エクセル重複データのある列同士の2列を比較したい

質問

エクセルに詳しい方、教えて頂きたいです。

文で説明すると、難しいので例と一緒に説明させてください。

例えば、下のようなデータがあるとします。
Ａ列 Ｂ列
山田さん 栃木県
山田さん 栃木県
山田さん 大阪府
山田さん 栃木県
鈴木さん 三重県
鈴木さん 三重県
大野さん 千葉県
大野さん 東京都
大野さん 東京都

Ａ列はＡ列で重複したデータがあり、Ｂ列はＢ列で重複したデーターがある場合。

Ａ列 Ｂ列 Ｃ列
山田さん 栃木県 大阪府
鈴木さん 三重県
大野さん 千葉県 東京都

上ような結果にできる方法はないでしょうか。

例えば住所録をデータで何年分かためており、整理したいが、現在の住所は一つのはずなのに間違えて住所入力したか引越ししたかで複数住所がある。現在はどちらが正しいのか確認するために、その確認資料を作成したい。（同じ名前の人で別人の人はいないとする。）

相違があるかどうかを調べるだけならと、
C列にif関数をいれて見ましたが、ベストな結果ではないと思います。
ちなみに入れた関数は、元のデータのＣ列目に
IF(A1=A2,IF(B1=B2," ","相違あり")," ")
上のIF関数をいれて、下にデータの分だけコピペしました。

エクセルを触るのは久々で、思いつく方法をしてみました。

ですので関数でできるのか、マクロでできるのか、またはエクセルの機能を使用してできるのか、またその方法を教えて頂きたいです。

そんな表にしなくても、こういうのでいいんじゃないの？等の提案でも構いません。

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

回答No.1

nishi6 · ベストアンサー

質問にある通りのレイアウトを算式やマクロで出力するのは大変なので並べ替えとフィルターグループの詳細設定ボタンを使ってみます。算式は一切使いません。質問のように氏名で並べ替えている方がきれいに出力されます。

A列とB列の最初に「氏名」と「住所」の表題を付けます。添付図のように列を空けて表題を２つコピーしてください。検索条件範囲と抽出範囲の指定用の列になります。

A列のデータをクリック
データタブ＞並べ替えとフィルター＞詳細設定をクリック
フィルターオプションの設定ダイアログボックスで
　・抽出先を「指定した範囲」
　・リスト範囲は「$A$1:$B$10」　（選択されているはず）
　・検索条件範囲はクリアして何も入力しない
　・抽出範囲は「$D$1」
　・「重複するレコードは無視する」にチェックして「OK」

　これで、D2=山田さん、D３=鈴木さん、D４=大野さんになったはず。

次に、
A列のデータをクリック
データタブ＞並べ替えとフィルター＞詳細設定をクリック
フィルターオプションの設定ダイアログボックスで
　・抽出先を「指定した範囲」
　・リスト範囲は「$A$1:$B$10」　（選択されているはず）
　・検索条件範囲は「$D$1:$E$1」にする。
　・抽出範囲は「$G$1:$H$1」
　・「重複するレコードは無視する」にチェックして「OK」

　これで、G、H列に抽出されたはずです。

レイアウトは少し違います。実際の項目はもっと多いかもしれませんが、G、H列を変化させればできるでしょう。

お礼

早速の回答ありがとうございます！
こんな機能があったんですね。

参考にして、使って行けそうです！ありがとうございました(_ _)

回答No.3

answer119

名前が同じで住所が異なるとき、住所を横に展開するのではなく、縦のままとし、重複する名前は最初に現れる行以外は見えなくするというやり方もあると思います。
次のような感じです。

名前....... 住所
山田さん 栃木県
............... 大阪府
鈴木さん 三重県
大野さん 千葉県
............... 東京都

重複する名前を表示しないようにするには、「書式」→「条件付き書式」で、値が一つ上のセルと同じときには、文字の色を白（あるいは背景と同じ色）に設定すれば、見えなくなります。
名前列のもっとも上のセルでこの書式を設定して、名前列全体にその書式をコピーします。

あとは、先の回答者さんが示しているように、名前順に並べて、「フィルタ オプションの設定」で重複する行を隠せばいいと思います。

横に展開すると住所が長く、重複する件数が多かったらやたらと横長になってしまいますし、縦の展開で維持していた方がその後の利用もしやすいと思うのですが。

当方、古いバージョンのExcelしか持っていないもので、最新のバージョンならもっといい方法があるのかもしれませんが。

お礼

本当に色々な提案して頂き参考になります！

いろいろ試してみて、自分のしたいことができるように調整してみたいと思います。

本当にありがとうございます(_ _)

回答No.2

keithin

ご利用のエクセルのバージョンがご相談に書かれていないので、エクセル２００７以降を使う方法を２つ。

方法１）
Ａ：Ｂ列を選択する
データタブの「重複の削除」を行う
山田さん　栃木県
山田さん　大阪府
鈴木さん　三重県
大野さん　千葉県
大野さん　東京都
という５つのデータが全てである事が確認できる。

方法２）
Ｃ１セルに
=IF(COUNTIFS($A$1:A1,A1,$B$1:B1,B1)>1,"","初出")
と記入、以下コピーする
５種のデータが初出である（それ以外は重複である）ことが確認できる。

バリエーション：
Ｄ１セルに
=IF(COUNTIFS($A$1:A1,A1,$B$1:B1,B1)>1,"重複あり",IF(COUNTIFS(A:A,A1,B:B,"<>"&B1)>0,"別バージョン","残す"))
と記入、以下コピーする

残す：そのまま残しておいてよいデータ
重複あり：名前、住所がダブってるデータが自分より上にあるので、問答無用で削除して良い
別バージョン：名前が同じで住所の違うデータが存在してる

【参考】
エクセル２００３以前をご利用の場合；
=IF(SUMPRODUCT(($A$1:A1=A1)*($B$1:B1=B1))>1,"重複あり",IF(SUMPRODUCT(($A$1:$A$999=A1)*($B$1:$B$999<>B1))>0,"別バージョン","残す"))
といった関数にする。

＃ご相談投稿では、普段あなたがご利用のソフトのバージョンまで、キチンと明記する事を憶えて下さい。次回以降のご参考に。

お礼

記述忘れてすみません。エクセル2007でした。

こんな簡単な作業でできるのを初めて知りました！
今後も活用できそうです。

ありがとうございました(_ _)