エクセル 一定の範囲から条件一致を全て抜き出す

質問

よろしくお願いします。
エクセルでSheet1に以下のようにデータが並んでいます。

　　Ａ列　　　 Ｂ列　　　Ｃ列　　 Ｄ列　　　Ｅ列

１　番号　　　氏名　　項目　　 内容　　確認

上記のように行ごとに案件別に表記しているのですが、これを空白の別Sheetに個人別にまとめて抜き出して１行に１件ずつ表示したいと考えていますが、案件別表記なので同じ人間が複数の案件を持っていることがあります。（Aさんが10件、Bさんが1件、Cさんが6件という具合に不特定で複数案件が発生します）
VLOOKUPで氏名を条件に検索してみたのですが、複数の案件があった場合に一番上の行に表記されているものしか拾えませんでした。

このような場合に適切な関数を教えて下さい！
よろしくお願いします。

回答No.2

KURUMITO · ベストアンサー

例えばシート1で氏名も新たに追加されていくことなどを考えるならば、その氏名も含めて自動的に表示されることがよいでしょう。
シート1では作業列を設けいて対応してはどうでしょう。
Ｆ2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(COUNTIF(B$2:B2,B2)=1,MAX(F$1:F1)+1,"")
Ｇ2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(B2="","",IF(F2<>"",F2+0.0001*COUNTIF(B$2:B2,B2),INDIRECT("F"&MATCH(B2,B:B,0))+0.0001*COUNTIF(B$2:B2,B2)))
Ｈ2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(G2="","",RANK(G2,G:G,1))
そこでシート2に移ります。
例えば1行目にシート1での項目名と同じ項目名を入力します。
Ａ列を氏名にしたい場合にはＡ1セルに氏名と入力します。Ｂ列には例えば番号と入力します。以降列でもお望みのように項目を入力すればよいでしょう。
その上でＡ2セルには次の式を入力し横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。
=IF(OR(COUNTIF(Sheet1!$H:$H,ROW(A1))=0,A$1=""),"",INDEX(Sheet1!$A:$E,MATCH(ROW(A1),Sheet1!$H:$H,0),MATCH(A$1,Sheet1!$1:$1,0)))

お礼

ありがとうございました。
工程まで丁寧に解説を頂き、非常に分かりやすかったです。

助かりました！

回答No.1

mshr1962

一例です。
A2=IF(COUNTIF(Sheet1!$B:$B,"氏名")>ROW()-1,INDEX(Sheet1!A:A,SUMPRODUCT(SMALL(100^(Sheet1!$B$2:$B$99<>"氏名")*ROW(Sheet1!$B$2:$B$99),ROW()-1))),"")

※上記の式で元の表の99行までカバーできます。
それ以上に多くなる場合は100^の数字を大きくしてください。
"氏名"の場所はセルの絶対参照($F$2）でも可能です。

お礼

ありがとうございました。
応用性のある方法をご紹介頂き、感謝してます。