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

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

No.1 ベストアンサー 回答者： imogasi 回答日時： 2007/08/30 22:28

これはかなりパズル的に難しい。

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

この回答へのお礼

お礼が遅くなりました。

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

お礼日時：2007/08/31 18:47

No.3 回答者： maron--5 回答日時： 2007/08/31 05:51

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

◆こんな方法もありますよ
B1=SUMIF($C$1:$C$10,C1,$A$1:$A$10)-A1
★下にコピー

この回答へのお礼

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

お礼日時：2007/08/31 18:50

No.2 回答者： deus_ex_machina 回答日時： 2007/08/30 23:21

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

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で動作確認済。

この回答へのお礼

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

お礼日時：2007/08/31 18:49