エクセルのＡ列とＢ列で重複するレコードのみを抽出して別の列に表示させた

質問

エクセルのＡ列とＢ列で重複するレコードのみを抽出して別の列に表示させたい。

エクセルのＡ列に約１，０００行くらいのデータが有ります。
このＡ列内で重複レコードがあります。
Ｂ列に約１，０００行くらいのデータが有ります。
Ｂ列内でも重複レコードが有ります。
この条件の中で

（１）Ａ列にもＢ列にあるデータすべて
（２）Ａ列にしかないデータすべて
（３）Ｂ列にしかないデータすべて
を抽出したいのですが、どんな方法がありますか？
それで
（１）をＣ列
（２）をＤ列
（３）をＥ列
に並べて表示させたいです。
さらに抽出されたデータで重複レコードの場合は１件のみで表示したいです。
複数の操作を繰り返すのではなく
Ｃ，Ｄ，Ｅ列に式を入れるだけで済ませる事は可能でしょうか？
よろしくお願いします。

例
　 　Ａ　　 Ｂ 　 　Ｃ　　　Ｄ　　　Ｅ
1-001-002--001--004--007
2-002-002--002--006--008
3-001-001--005--010
4-005-007
5-001-005
6-004-005
7-004-008
8-010-007
9-006-007

A列には001が3個、002が1個、005が1個有ります。
B列には001が1個、002が2個、005が2個有ります。
A列にもB列にも有るのは、001と002と005ですから
これがC列に表示されます。
でそれぞれA列にもB列にも複数有りますが、1個として判定なので

よって、C列には
001
001
001
001
002
002
005
005
と表示ではなく
001
002
005
と表示したい。

次にＤ列はA列に有る物だけですから
004
006
010
となります。B列には004は2個有りますが同じ値なので
004
004
006
010
では無く004は１個の表示です。

E列はB列のみのデータなので
007
008
です。
同じくB列には007が3個有りますが1個ともなします。
007
007
007
008
では無く
007
008
です。

回答No.3

tom04 · ベストアンサー

No.2です！
たびたびすみません。
投稿した後で質問文を読み返してみるとデータが約10000行あるということなので、
前回の数式の「1000」の部分を全て「10000」に変更してみてください。

そして余計なお世話かもしれませんが、10000行までオートフィルでコピーするのは大変でしょうから、
当方使用のExcel2003の場合ですが
もう1列A列に列を挿入します。
A2セルに「1」を入力後、A2セルをアクティブにします　
→　メニュー　→　編集　→　フィル　→　連続データの作成　→　「列」を選択　→　「停止値」に「10000」としてOK
そして、B2～D2セルに作業列が移動しているはずですので、B2～D2セルを範囲指定し、
D2セルのフィルハンドルの（＋）マークでダブルクリック
10000行目までコピーできますが、少し時間がかかると思います。
最後にA列全てを削除します。

以上、何度もごめんなさいね。m(__)m

お礼

丁寧にありがとうございました。
今回１回だけの編集なんですが、おかげで助かりました。

回答No.2

tom04

こんばんは！
一例です。
No.1さんとほとんど同様の方法になります。
↓の画像のように作業用の列を3列使っています。
とりあえず、1000行目まで対応できるようにしています。
3列挿入していますので、元の列は右にずれてしまいます。

作業列A2セルに
=IF(COUNTBLANK(D2:E2)=2,"",IF(AND(COUNTIF($E$2:$E$1000,D2),COUNTIF($D$2:D2,D2)=1),D2*1000+ROW(A1),""))

B2セルに
=IF(COUNTBLANK(D2:E2)=2,"",IF(AND(COUNTIF($E$2:$E$1000,D2)=0,COUNTIF($D$2:D2,D2)=1),D2*1000+ROW(A1),""))

C2セルに
=IF(COUNTBLANK(D2:E2)=2,"",IF(AND(COUNTIF($D$2:$D$1000,E2)=0,COUNTIF($E$2:E2,E2)=1),E2*1000+ROW(A1),""))

という数式を入れ、A2～C2セルを範囲指定し、C2セルのフィルハンドルで下へずぃ～～～！っとコピーします。

そしてF2セルは
=IF(COUNT(A$2:A$1000)<ROW(A1),"",INDEX($D$2:$D$1000,MOD(SMALL(A$2:A$1000,ROW(A1)),1000)))
として隣のG2セルまでコピー。

H2セルは
=IF(COUNT($C$2:$C$1000)<ROW(A1),"",INDEX($E$2:$E$1000,MOD(SMALL($C$2:$C$1000,ROW(A1)),1000)))
という数式を入れています。

最後にF2～H2セルを範囲指定し、H2セルのフィルハンドルで下へコピーすると
画像のような感じになります。

以上、長々と書きましたが
参考になれば幸いです。m(__)m

回答No.1

mt2008

A列、B列に入っているのが文字列ではなく数値で、書式設定で「001」の様に表示しているだけの場合、以下の様にすることで可能です。

作業列を３つ使います。
G1に　=IF(COUNTIF(A$1:A1,A1)=1,IF(COUNTIF(B:B,A1)>0,A1,""),"")
H1に　=IF(COUNTIF(A$1:A1,A1)=1,IF(COUNTIF(B:B,A1)=0,A1,""),"")
I1に　=IF(COUNTIF(B$1:B1,B1)=1,IF(COUNTIF(A:A,B1)=0,B1,""),"")

と、入れてG1:I1を下方向にコピーします。
これで途中に空白が入ったデータが出来ます。
これをC～E列に空白セルを詰めて表示します。

C1に　=IF(ROW()>COUNT(G:G),"",SMALL(G:G,ROW()))
と入れてコピーし、C1:E1000（データが1000行以上ある時はその行まで）に貼り付けます。