【エクセル】2列間で、同じ組み合わせのものを抽出したい。

質問

A列には地名、B列にはお店が書いてあるような表で
A列とB列の組み合わせが他の行でもあった場合それを表示しないよう
にさせたいんです。
たとえば以下な表を、（A,B列は元から記入されています）

│A　　 │B 　　　 │C│D│││││
1│東京 　│ラーメン屋│ │││││
2│東京　 │花屋 　　 │ │││││
3│神奈川│花屋 　　 │ │││││
4│東京　 │ラーメン屋│ │││││
5│神奈川│自転車屋 │ │││││
6│埼玉　 │ラーメン屋│ │││││
7│神奈川│花屋　　　　　│ │││││

を・・

│A　　 │B 　 　　 │C　 │D │E│
1│東京　 │ラーメン屋│東京　│ラーメン屋│2│
2│東京　 │花屋 　　 │東京　│花屋 │2│
3│神奈川│花屋 　　 │神奈川│花屋 │1│
4│東京　 │ラーメン屋│神奈川│自転車屋 │2│
5│神奈川│自転車屋 │埼玉　│ラーメン屋│1│
6│埼玉　 │ラーメン屋│ 　 　│ │ │
7│神奈川│自転車屋　│ 　 　 │ │ │

というように、C列、D列、E列に、A,B列の元データで共に重複している
部分を表示させないようにするには、どうしたらよいでしょうか？

ちなみに、マクロはなるべく使わず、関数で教えてもらえると有難いです。
よろしくお願いします。

回答No.3

nihonjinn · ベストアンサー

訂正です
(3)F2に「=IF(D2="","",COUNTIF($C2:&C&100,C2))」と入力
を
(3)F2に「=IF(D2="","",COUNTIF($C2:$C$100,C2))」と入力
としてください。

このままだとばらばらにデータが入ったままなので
(1)D～F列のデータを選択→コピー
　→右クリック「形式を選択して貼り付け」で「値」で貼り付けておく
(2)D～F列のデータ範囲を選択して「データ」→「並び替え」で並び替える

もしくはデータの順番をデータの登場順のままで整理したいなら
(1)D～F列のデータ範囲を選択して「データ」→「フィルタ」→「オートフィルタ」
(2)D列の△印→「空白せる以外」をクリック
(3)データをいったん別シートに「値」でコピペしておき、オートフィルタを解除しなおしてからもとのシートに貼り付けてなおしてください。

回答No.2

zap35

作業列を使わない方法ですが、少し難しくなりますよ。
以下の表があるとき
　　　　　Ａ列　　　Ｂ列　　　　　Ｃ列　　　Ｄ列　　　Ｅ列
１行目　東京　　ラーメン屋
２行目　東京　　花屋
３行目　神奈川　花屋
４行目　東京　　ラーメン屋
５行目　東京　　自転車屋
６行目　埼玉　　ラーメン屋
７行目　神奈川　花屋
８行目　東京　　花屋
９行目　東京　　金物屋

Ｃ１セルに
　=INDEX(A:A,SMALL(INDEX(SUBSTITUTE((MATCH($A$1:$A$100&$B$1:$B$100,$A$1:$A$100&$B$1:$B$100,0)=ROW($1:$100))*1,0,10^5)*ROW($1:$100),),ROW(Y1)))
を貼り付けてＤ１セルおよび下方向にコピーしてください。すると以下のような結果になります（実際に試した結果を載せました）

　　　　　Ａ列　　Ｂ列　　　　　　Ｃ列　　　Ｄ列　　　　　Ｅ列
１行目　東京　　ラーメン屋　　東京 　ラーメン屋
２行目　東京　　花屋　　　　　東京　　花屋
３行目　神奈川　花屋　　　　　神奈川　花屋
４行目　東京　　ラーメン屋　　東京　　自転車屋
５行目　東京　　自転車屋　　　埼玉　　ラーメン屋
６行目　埼玉　　ラーメン屋　　東京　　金物屋
７行目　神奈川　花屋　　　　　#REF!　　#REF!
８行目　東京　　花屋
９行目　東京　　金物屋

表示するものがないセルが#REFエラーにならないようにするには、Ｃ１セルの式は条件判定を追加して以下にします。右、および下方向にコピーしてください
　=IF(SUM(INDEX((MATCH($A$1:$A$100&$B$1:$B$100,$A$1:$A$100&$B$1:$B$100,0)=ROW($1:$100))*1,))>=ROW(Y1),INDEX(A:A,SMALL(INDEX(SUBSTITUTE((MATCH($A$1:$A$100&$B$1:$B$100,$A$1:$A$100&$B$1:$B$100,0)=ROW($1:$100))*1,0,10^5)*ROW($1:$100),),ROW(Y1))),"")

質問にはＥ列の説明がありませんが、出現回数でしょうか？
Ｅ１セルの式は以下になります
　=SUMPRODUCT(($A$1:$A$9&$B$1:$B$9=C1&D1)*1)

お礼

回答ありがとうございます。
作業列を使わないでも出来るんですね。すごいです。ただちょっとこの
長い関数を理解できずにいます^^;

説明不足のE列（すいません。）についても表からばっちり理解していただき感謝です。

回答No.1

nihonjinn

(1)C2に「=A2&B2」と入力し、下にフィルコピー
(2)D2に「=IF(COUNTIF(C$2:C2,C2)=1,A2,"")」
E2に「=IF(COUNTIF(C$2:C2,C2)=1,B2,"")」と入力し、下にフィルコピー
(3)F2に「=IF(D2="","",COUNTIF($C2:&C&100,C2))」と入力し、下にフィルコピー
これで重複しないリストと重複数のリストができます。

お礼

ありがとうございます。とても参考になりました。
「＆」で合わせて一つの文字列にするといいんですね。