列A 列B 列C 列D 列E 列F 列G 列H
行1 愛 青 パンチ
行2 愛 勇気 友達 BB AA CC
行3 勇気 愛 友達 AA CC BB
行4 勇気 弁護士 正義
行5 勇気 愛
行6 コイ ダイス ダチ
行7 ダイス ダチ コイ
行8 友達 愛 勇気 CC AA BB
上記のような6列のデータ(データによってはB列で終わっていることもあります。)
の順不同で行での重複をチェックする関数を知りたいです。
行2、3、8と行6、7が順番は違いますが同じデータなので重複と出したいです。
こちらのサイトで色々聞いたので、3列の場合には
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,"重複","")
上記のように教わり、6列のデータが出てきてしまったので
それに対応できるように、
列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(H$1:H1,A1,""),B1,""),C1,""),D1,""),E1,""),F1,"")="___"&COUNTA(A1:F1)))>1,"重複","")
上記のように関数を追加したのですが、下記のような欲しいデータがどうしても出てきません。
列A 列B 列C 列D 列E 列F 列G 列H
行1 愛 青 パンチ
行2 愛 勇気 友達 BB AA CC
行3 勇気 愛 友達 AA CC BB 重複
行4 勇気 弁護士 正義
行5 勇気 愛
行6 コイ ダイス ダチ 重複
行7 ダイス ダチ コイ 重複
行8 友達 愛 勇気 CC AA BB 重複
3列の時にはできた関数なので6列も可能だと思うのですが何がいけないのか調べていたら
こんな時間になってしまい、助けをお願いしたく再度書き込みしております。
何卒よろしくお願いいたします。
A 回答 (5件)
- 最新から表示
- 回答順に表示
No.5
- 回答日時:
ANo.3です
> 教えて頂いたものですと正しく重複チェックできないものがありました。
それは失礼。
どの様なパターンの時でしょう?
提示例の「行6」の重複は単なる記入ミスかと思いましたが、「行6」にも重複が表示されることがお望みなのでしょうか。
No.4
- 回答日時:
回答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
- 回答日時:
画面だと見づらい「_(アンダースコア)」を使ったせいで紛らわしかったですね。
すでに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
- 回答日時:
こんにちは!
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
- 回答日時:
>上記のように関数を追加したのですが、下記のような欲しいデータがどうしても出てきません。
タイプミスが有ることと単純な見落としがあります。
タイプミス部分
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,"重複","")
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルで、 A1セルに「A」という値、 B1セルに「B」という値が入っています。 どちらも表示形式 5 2023/02/22 23:05
- Excel(エクセル) EXCEL 行内のデータを2行に分けて、表を作り直したいのです。教えてください。 5 2023/06/25 14:00
- Excel(エクセル) 表示形式、文字列セル(列)に数式を入力するには マクロ 1 2022/09/18 10:53
- Excel(エクセル) Excelの空文字判定について 7 2023/01/06 13:25
- Visual Basic(VBA) vbaエクセルマクロ RemoveDuplicatesについて RemoveDuplicatesを使 3 2023/02/28 01:13
- Excel(エクセル) エクセル シート内のボックスを縦並びから横並びに 7 2023/04/05 04:28
- Visual Basic(VBA) Excel VBA 最終行を取得しVlookup関数をコピーする方法をコーディングで教えてください。 3 2023/05/11 13:14
- その他(Microsoft Office) IF関数について教えてください 2 2022/05/10 13:31
- Visual Basic(VBA) vbaエクセルマクロ RemoveDuplicatesについて RemoveDuplicatesを使 1 2023/02/27 22:21
- Excel(エクセル) 【画像あり】A1が●+B1と同じ文字がB列にある+C1と同じ文字がC列にある場合D1に〇を付ける 3 2023/03/09 18:18
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルでの作業計算方法について
-
Microsoft1Officeの互換ソフト...
-
【マクロ】その時、その時で変...
-
はがきについて。
-
【マクロ】読取専用のファイル...
-
エクセル初心者です 関数の入れ...
-
【関数】適切な文字数の数字を...
-
LOOKUP関数を使えばいいのでし...
-
【関数】先頭だけにある、半角...
-
Excel ピボットテーブルで日付...
-
Excelのpivotについて質問です
-
時間によってファイル名が変わ...
-
エクセル 白黒印刷で白線を印刷...
-
Aというブックの1というシート...
-
エクセル関数を教えてください
-
WPS OFFICEでの縦書きについて
-
Excelのチェックボックスの使い...
-
エクセルの条件付き書式につい...
-
エクセルのセルに同じ大きさの...
-
エクセルの関数について教えて...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報