Ｅｘｃｅｌ 2つの重複データに重複先セル番地を表示させたい！

質問

お世話になります。
エクセルで２つの重複しているデータがランダムにがあります。
下記のように表示いたいのですが、良いほうを教えて下さい。
たとえばＡ１のみかんがＡ７と重複している時【７】と表示したい
※ＶＢＡはわかりません。関数は得意です。　　よろしくお願いします。
A　 B C
1　　7 みかん
2　　5 りんご
3　　　 もも
4　　6 なし
5　　2　 りんご
6　　4　 なし
7　　1　 みかん
8　　9　 ぶどう
9　　8　 ぶどう

回答No.1

imogasi · ベストアンサー

これはかなりパズル的に難しい。こういうのはＶＢＡ向きの課題ですよ。
例データ　Ｂ列に、Ｂ２から（Ｃ列は下記（１）の結果を前出しした）
Ｂ列　Ｃ列
みかん 1
りんご 2
もも 3
なし 4
りんご 5
なし 6
みかん 7
ぶどう 8
ぶどう 9
（１）Ｃ列Ｃ２に１といれＣＴＲＬを押しながら下へ引っ張り
連番を振る。関数でも、オートフィルでも可能。
上記の通り。
（２）Ｂ列でソート
ソートも関数でできるが、煩雑になるので、操作を使う。
結果
B C
なし 4
なし 6
ぶどう 8
ぶどう 9
みかん 1
みかん 7
もも 3
りんご 2
りんご 5
（３）Ｄ列Ｄ２に
=IF(COUNTIF($B$1:$B4100,B2)=2,COUNTIF($B$1:B2,B2),"")
と入れて下方向に式を複写。
結果
B C D
なし 4 1
なし 6 2
ぶどう 8 1
ぶどう 9 2
みかん 1 1
みかん 7 2
もも 3
りんご 2 1
りんご 5 2
（４）Ａ列Ａ２に
=INDEX($C$1:$C$100,IF(D2=1,ROW()+1,IF(D2=2,ROW()-1,"")),1)
と入れて、下方向に式を複写。
結果
A B C D
6 なし 4 1
4 なし 6 2
9 ぶどう 8 1
8 ぶどう 9 2
7 みかん 1 1
1 みかん 7 2
#VALUE! もも 3
5 りんご 2 1
2 りんご 5 2
#VALUE! は出ないようにできるが、式が長くなるので略。
（５）Ａ２：Ｃ１０をコピーして、別シートなどに、形式を選択して張り付けー値
結果　式は消える
6 なし 4
4 なし 6
9 ぶどう 8
8 ぶどう 9
7 みかん 1
1 みかん 7
#VALUE! もも 3
5 りんご 2
2 りんご 5
（６）Ｃ列でソート
結果
A B C D
7 みかん 1 1
5 りんご 2 1
#VALUE! もも 3
6 なし 4 1
2 りんご 5 2
4 なし 6 2
1 みかん 7 2
9 ぶどう 8 1
8 ぶどう 9 2
あと不要な列は消す。

お礼

お礼が遅くなりました。

回答ありがとうございます。関数を貼り付けたら出来ました！！V（＾＾）V
本当に助かりました。感動しました。
関数は得意でネストしてみたのですがどうしても出来なくて・・・・・・
簡単に出来るかと思っていましたが難しいこととは知りませんでした。
ゆっくり、数式の内容を理解してみようと思います。

回答No.3

maron--5

＞重複が２つずつに限られているのであれば
◆こんな方法もありますよ
B1=SUMIF($C$1:$C$10,C1,$A$1:$A$10)-A1
★下にコピー

お礼

ありがとうございました。

回答No.2

noname#52504

重複が２つずつに限られているのであれば、

B1セル：
　=SUMPRODUCT(($C$1:$C$9=C1)*ROW($C$1:$C$9))-ROW(C1)
としてフィルすればOKです。

略解
　隣接セルと値が等しいセルの行番号の和、つまり、隣接セルとその"連れ"の２セルの行番号の和を求めて、
　それから隣接セルの行番号を引く　⇒　"連れ"のセルの行番号

「もも」のように"連れ"がない場合に、空白を返す必要があるのなら、
　=IF(COUNTIF($C$1:$C$9,C1)=1,"",SUMPRODUCT(($C$1:$C$9=C1)*ROW($C$1:$C$9))-ROW(C1))

Excel2003で動作確認済。

お礼

ありがとうございました。