Excelの順不同の難しい重複チェックについて

質問

列A　　　　　列B　　　　　列C　　　　列D 　　　　列E 　　　　列F 　　　　列G 　　　　列H
行1　　愛　　　　　　青　　　　　　パンチ　　　　　　　　　　　　　　　　　　　　　　　　　　　
行2　　愛　　　　　　勇気　　　　　友達　　　ＢＢ　　　　ＡＡ　　　　ＣＣ　　　　　　　　　　　
行3　　勇気　　　　　愛　　　　　　友達　　　ＡＡ　　　　ＣＣ　　　　ＢＢ　　　　　　　　　　　
行4　　勇気　　　　　弁護士　　　　正義　　　　　　　　　　　　　　　　　　　　　　　　　　　　
行5　　勇気　　　　　愛　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　
行6　　コイ　　　　　ダイス　　　　ダチ　　　　　　　　　　　　　　　　　　　　　　　　　　　　
行7　　ダイス　　　　ダチ　　　　　コイ　　　　　　　　　　　　　　　　　　　　　　　　　　　　
行8　　友達　　　　　愛　　　　　　勇気　　　ＣＣ　　　　ＡＡ　　　　ＢＢ

上記のような６列のデータ（データによってはB列で終わっていることもあります。）
の順不同で行での重複をチェックする関数を知りたいです。
行２、３、８と行６、７が順番は違いますが同じデータなので重複と出したいです。

こちらのサイトで色々聞いたので、３列の場合には
E1に↓を入れて下にコピー
=IF(COUNTA(A1:C1)=0,"",A1&"_"&B1&"_"&C1&"_"&COUNTA(A1:C1))

D1に↓を入れて下にコピー
=IF(SUMPRODUCT(1*(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E$1:E1,A1,""),B1,""),C1,"")="___"&COUNTA(A1:C1)))>1,"重複","")

上記のように教わり、６列のデータが出てきてしまったので
それに対応できるように、

列Ｇ１行目に
=IF(COUNTA(A1:F1)=0,"",A1&"_"&B1&"_"&C1&"_"&D1&"_"&E1&"_"&F1&"_"&COUNTA(A1:F1))
こちらの関数を入れ下にドラッグし、

列Ｈ１行目には
=IF(SUMPRODUCT(1*(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(H$1:H1,A1,""),B1,""),C1,""),D1,""),E1,""),F1,"")="___"&COUNTA(A1:F1)))>1,"重複","")

上記のように関数を追加したのですが、下記のような欲しいデータがどうしても出てきません。

　　　　列A　　　　　列B　　　　　列C　　　　列D 　　　　列E 　　　　列F 　　　　列G 　　　　列H
行1　　愛　　　　　　青　　　　　　パンチ　　　　　　　　　　　　　　　　　　　　　　　　　　　
行2　　愛　　　　　　勇気　　　　　友達　　　ＢＢ　　　　ＡＡ　　　　ＣＣ　　　　　　　　　　　
行3　　勇気　　　　　愛　　　　　　友達　　　ＡＡ　　　　ＣＣ　　　　ＢＢ　　　　重複　　　　　
行4　　勇気　　　　　弁護士　　　　正義　　　　　　　　　　　　　　　　　　　　　　　　　　　　
行5　　勇気　　　　　愛　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　
行6　　コイ　　　　　ダイス　　　　ダチ　　　　　　　　　　　　　　　　　　　　　重複　　　　　
行7　　ダイス　　　　ダチ　　　　　コイ　　　　　　　　　　　　　　　　　　　　　重複　　　　　
行8　　友達　　　　　愛　　　　　　勇気　　　ＣＣ　　　　ＡＡ　　　　ＢＢ　　　　重複　　　　　

３列の時にはできた関数なので６列も可能だと思うのですが何がいけないのか調べていたら
こんな時間になってしまい、助けをお願いしたく再度書き込みしております。

何卒よろしくお願いいたします。

回答No.5

mt2008

ANo.3です

> 教えて頂いたものですと正しく重複チェックできないものがありました。

それは失礼。
どの様なパターンの時でしょう？
提示例の「行6」の重複は単なる記入ミスかと思いましたが、「行6」にも重複が表示されることがお望みなのでしょうか。

回答No.4

bunjii

回答No.3で誤動作の指摘がありましたので訂正させて頂きます。

G1=A1&" "&B1&" "&C1&" "&D1&" "&E1&" "&F1
データの区切りにスペースを入れました。
H1=IF(SUMPRODUCT((TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G$1:G1,A1,""),B1,""),C1,""),D1,""),E1,""),F1,""))="")*1)>1,"重複","")
H1セルでは区切りの空白が残るので全てがマッチしたときはTRIM関数でスペースを除去すると文字列数が0（""）になります。
チェックするデータの個数に関係なく比較対象の文字数が0になったとき重複と見做すようにすれば良いでしょう。

回答No.3

mt2008

画面だと見づらい「_（アンダースコア）」を使ったせいで紛らわしかったですね。
すでにNo.1の方が指摘されている内容ではありますが……
G1=IF(COUNTA(A1:F1)=0,"",A1&"_"&B1&"_"&C1&"_"&D1&"_"&E1&"_"&F1&"_"&COUNTA(A1:F1))
H1=IF(SUMPRODUCT(1*(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G$1:G1,A1,""),B1,""),C1,""),D1,""),E1,""),F1,"")="______"&COUNTA(A1:F1)))>1,"重複","")

ただ、No.1の方の回答の方がシンプルですが、
・「勇気、愛」と言う行と「勇気愛」と言う行が有った場合、重複と見なされる
・「勇気、愛」と言う行と「勇気、愛、恋」と言う行が有った場合、重複と見なされると言う問題が有ります。

補足

教えて頂いたものですと正しく重複チェックできないものがありました。

回答No.2

tom04

こんにちは！
VBAになってしまいますが、一例です。
何列あっても対応できるようにしてみました。
元データはSheet1のA1セルからあるとし、A列だけは最終行まで必ずデータが入っているとします。

尚Sheet2を作業用のSheetとして使用していますので、
Sheet2は使っていない状態にしておいてください。

Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻りマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, j As Long, k As Long, lastRow As Long, lastCol As Long
Dim c As Range, wS As Worksheet
Set wS = Worksheets("Sheet2")
With Worksheets("Sheet1")
lastCol = 1
Do While WorksheetFunction.CountA(.Columns(lastCol)) > 0
lastCol = lastCol + 1
Loop
Set c = .Columns(lastCol - 1).Find(what:="重複", LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
lastCol = lastCol - 1
Else
.Columns(lastCol - 1).ClearContents
lastCol = lastCol - 2
End If
lastRow = .UsedRange.Rows.Count
Range(.Cells(1, "A"), .Cells(lastRow, lastCol)).Copy wS.Range("B1")
For i = 1 To wS.Cells(Rows.Count, "B").End(xlUp).Row
j = wS.Cells(i, Columns.Count).End(xlToLeft).Column
Range(wS.Cells(i, "B"), wS.Cells(i, j)).Sort key1:=wS.Cells(i, "B"), order1:=xlAscending, _
Header:=xlNo, Orientation:=xlLeftToRight
For k = 2 To j
If wS.Cells(i, k) <> "" Then
wS.Cells(i, "A") = wS.Cells(i, "A") & wS.Cells(i, k) & "_"
End If
Next k
Next i
For i = 1 To lastRow
If WorksheetFunction.CountIf(wS.Range("A:A"), wS.Cells(i, "A")) > 1 Then
.Cells(i, lastCol + 1) = "重複"
End If
Next i
wS.Cells.Clear
End With
End Sub 'この行まで

こんな感じではどうでしょうか？m（_ _）m

回答No.1

bunjii

>上記のように関数を追加したのですが、下記のような欲しいデータがどうしても出てきません。
タイプミスが有ることと単純な見落としがあります。

タイプミス部分
H1=[省略]SUBSTITUTE(H$1:H1,A1,"")[省略]　自身のセルを参照のため循環になります。
H1=[省略]SUBSTITUTE(G$1:G1,A1,"")[省略]

見落とし部分
H1=[省略]="___"&COUNTA(A1:F1)[省略]　アンダースコア（_）の数が不足しています。
元データが3列増えているのでG列のデータにはアンダースコアが3文字増えます。
従って、比較対象が全て不一致となり重複なしの判定になります。

単純ミスを防止するには不要な情報を介在させないことを推奨します。
G1=A1&B1&C1&D1&E1&F1
H1=IF(SUMPRODUCT((SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G$1:G1,A1,""),B1,""),C1,""),D1,""),E1,""),F1,"")="")*1)>1,"重複","")