重複している項目の番号を表示したいです

画像のような表を作っているのですがBセルの内容とCセルの内容が両方とも一致した場合に
Dセルに「重複」と表示するようにはできたのですが
今度はE列にどれと重複しているかわかる表示をしたいです。
できれば１セル内にNoの「1,4,…」みたいな感じで番号を表示したいのですが可能でしょうか？
他の方法でも重複してる項目がどれかわかればいいのですが…

D列にはこんな関数をいれてます
=IF(A2="","",IF(SUMPRODUCT(($B$2:$B100=B2)*($C$2:$C100=C2))>1,"重複",""))

No.1 ベストアンサー 回答者： tom04 回答日時： 2017/05/25 20:43

こんばんは！

＞できれば１セル内にNoの「1,4,…」みたいな感じで・・・
別セル表示なら関数で可能です。

画像の配置だとE2セルに
=IFERROR(IF($D2="重複",INDEX($A$2:$A$100,SMALL(IF(($B$2:$B$100=$B2)*($C$2:$C$100=$C2),ROW($A$2:$A$100)-1),COLUMN(A1))),""),"")

配列数式なのでCtrl＋Shift＋Enterで確定！
E2セルのフィルハンドルで右へ、下へコピーしてみてください。

※　どうしても1セル内に！というコトであればVBAで可能です。
今回はVBAは割愛します。m(_ _)m

この回答へのお礼

できました～！
感謝です。大変参考になりました！

お礼日時：2017/05/26 12:52

No.3 回答者： mike_g 回答日時： 2017/05/26 10:11

私自身の Excel の使い方をツラツラと考えてみるに、

一枚のワークシートの広さは横は１万列以上、縦は百万行以上の広大なものなのに、何と左上隅だけで作業していたのだろうか！
考え方や計算を楽にするために、もっと余白を利用しよう。
というわけで、右側を作業用として惜し気も無く使用しています。(*^_^*)

１．次式を入力したセル F2 を右に列Ｏまでオートフィル
￣￣ =IFERROR(IF($A2=SMALL(IF(($B$2:$B$11=$B2)*($C$2:$C$11=$C2),$A$2:$A$11,""),COLUMN(A1)),"",SMALL(IF(($B$2:$B$11=$B2)*($C$2:$C$11=$C2),$A$2:$A$11,""),COLUMN(A1))),"")
￣￣【お断り】上式は必ず配列数式として入力のこと
２．列Ｐを空白列として、式 =P2&" "&F2 を入力したセル Q2 を
￣￣右に列Ｚまでオートフィル
３．セル D2 に式 =TRIM(Z2) を入力
４．範囲 D2:Z2 を下方に 11行目までオートフィル

この回答へのお礼

なるほど～！こんな方法もありなのですね
勉強になりました。感謝です！

お礼日時：2017/05/26 12:59

No.2 回答者： tom04 回答日時： 2017/05/25 22:16

No.1です。

ついでに1セル内に表示するVBAを作ってみました。

Sub Sample1()
Dim i As Long, k As Long, lastRow As Long
Dim myStr As String
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
Range("F:F").Insert
Range(Cells(2, "F"), Cells(lastRow, "F")).Formula = "=IF(D2=""重複"",B2&C2,"""")"
For i = 2 To lastRow
If Cells(i, "D") = "重複" Then
For k = 2 To lastRow
If Cells(k, "D") = "重複" And Cells(k, "F") = Cells(i, "F") Then
myStr = myStr & Cells(k, "A") & ","
End If
Next k
Cells(i, "E") = Left(myStr, Len(myStr) - 1)
myStr = ""
Else
Cells(i, "E").ClearContents
End If
Next i
Range("F:F").Delete
Application.ScreenUpdating = True
End Sub

おそらくE列に表示されるはずです。

※　関数でないのでデータ変更があるたびに
マクロを実行する必要があります。m(_ _)m

この回答へのお礼

なるほど～！
今回はブックを開く人間が素人なのでVBAは避けたほうがいいかなと思いましたが
社内資料で使えるので参考にさせてもらいます！
ありがとうございます！

お礼日時：2017/05/26 12:55