指定文字列の番地or行数を取得（重複あり）

質問

お世話になります。

以下のようなブックがあります。
・Sheet1に大量のデータ (A1:M5000)
・Sheet2にSheet1のＣ列の文字列に関する重複なしのリスト(A1:Ａ2000)(1行目は見出し)

ここで、Sheet2の文字列の横のセル（Ｂ列）に、Sheet1の該当番地または行数を返したいと考えています。
ただ、重複があるため、重複があった場合は、Ｃ列、Ｄ列・・・・を使って、個別に返せれば、ありがたいのですが、
このような関数は作れますでしょうか？

方法は、Sheet2のリストの文字列の該当行数が、重複を含め分かれば、どんなやり方でもかまいません。
ご教授お願いいたします。

回答No.3

keithin · ベストアンサー

シート２のＡ２以下に「文字列」が記入してある前提で。
シート２のＢ２に
=IF(A2="","",IFERROR(SUM(A2,MATCH($A2,OFFSET(Sheet1!$C$1:$C$5000,SUM(A2),0),0)),""))
と記入，右に下にコピーします。

お礼

ありがとうございます！！
見事に返すことができました。感謝いたします。

非常に悩みましたが、もっともシンプルな数式のご提案をくださったkeithinさんをベストアンサーとさせていただきます。回答者みなさんに感謝いたします。

回答No.5

bunjii

>ただ、重複があるため、重複があった場合は、Ｃ列、Ｄ列・・・・を使って、個別に返せれば、ありがたいのですが、このような関数は作れますでしょうか？
行番号を求める数式は次のようになります。
B2=IF(AND(COUNTA($A2),COUNTIF(Sheet1!$C$2:$C$5001,$A2)>=COLUMNS($B2:B2)),LARGE(INDEX((Sheet1!$C$2:$C$5001=$A2)*ROW(Sheet1!$C$2:$C$5001),0),COUNTIF(Sheet1!$C$2:$C$5001,$A2)-COLUMNS($B2:B2)+1),"")
B2セルを右と下へコピーすれば良いでしょう。
INDEX関数はSheet2のA列と同じ文字列がSheet1のC列にある行番号の配列値をLARGE関数へ返すための処理に使っています。
Excelのすべてのバージョンで使えると思います。

お礼

ありがとうございます！！
見事に返すことができました。感謝いたします。

回答No.4

kagakusuki

今仮にSheet2のA2以下にリストの文字列が入力されているものとします。
　まず、Sheet2のB2セルに次の関数を入力して下さい。

=IF(A2="","",IF(COUNTIF(Sheet1!$C:$C,$A2)<COLUMNS($B:B),"",MATCH($A2,INDEX(Sheet1!$C:$C,SUM(A2)*(COLUMNS($B:B)>1)+1):Sheet1!$C$1048576,0)+SUM(A2)*(COLUMNS($B:B)>1)))

　そして、Sheet2のB2セルをコピーし、そのセルを起点にして、右方向には「文字列の中で最も重複しているものが多い物の重複回数」として想定している回数を上回るのに十分な列数、下方向には「Sheet2のA列のリストをカバーするのに十分な行数」となり得るセル範囲に貼り付けて下さい。

　以上です。

お礼

ありがとうございます！！
見事に返すことができました。感謝いたします。

回答No.2

chie65535

B列（一致する最初の１つ目）は

=MATCH(A2,Sheet1!C1:C5000,0)

の式で、行番号が求まります。

C列（一致する２つ目）は

=IFERROR(MATCH(A2,OFFSET(Sheet1!C1:C5000,B2,0,5000-B2,1),0),"")

D列（一致する３つ目）は

=IF(ISNUMBER(C2),IFERROR(MATCH(A2,OFFSET(Sheet1!C1:C5000,C2,0,5000-C2,1),0),""),"")

E列（一致する４つ目）は

=IF(ISNUMBER(D2),IFERROR(MATCH(A2,OFFSET(Sheet1!C1:C5000,D2,0,5000-D2,1),0),""),"")

で、行番号が求まります。

「該当番地」で求めずに「行番号」で求めているのは「次を探す時」に「見付かった次の行から探す必要があるから」です。

なお、検証してないので、間違っている（うまく動かない）かも知れません。

お礼

ありがとうございます！！
見事に返すことができました。感謝いたします。

回答No.1

msMike

》 Sheet2にSheet1のＣ列の文字列に関する重複なしのリスト…
この「文字列に関する重複なしのリスト」は思わせぶりな表現だけど、「文字列の重複なしのリスト」あるいは「重複なしの文字列のリスト」とどう違いますか？

補足

Ｃ列の文字列を重複なしにしたリストです。
ですので、前者です。

ご指摘ありがとうございます。