エクセルで名寄せとマッチング

質問

Excel 2003です。
関数やメニューにある機能でできると、とても助かります。

2つのシートの情報を統合して、1つのシートにしたいのですが、方法が分からず途方にくれています。どうぞお知恵をお貸しください！！！
---
【GOAL】２つのシートを、「アイテムID」をキーに、マッチング。

1.　＜シート２＞を名寄せして、１アイテム＝１行のデータにする
2.　＜シート１＞と、名寄せした＜シート２＞を、「アイテムID」をキーにマッチングし、＜シート１＞と＜シート２＞の情報を、全て１つのシートに統合。

---
▼具体的には、以下のようなデータです。

＜シート１＞ 23,000件ほどのアイテム名リスト

・アイテムはユニークで、重複なしです

【例】
アイテム名,アイテムID
いろは,1111
にほへと,2222
ちるぬ,3333
るを,4444

＜シート２＞
30,000件ほどのアイテムカテゴリー分け表

・アイテムによって、割り当てられているカテゴリ数が異なる。
・「１行＝１アイテム１カテゴリ」で、１アイテムにつき、割り当てられたカテゴリ数分だけ行が発生している。

【例】
アイテムID,カテゴリ名,カテゴリID
1111,エンタメ,aaaa
2222,ビジネス,bbbb
3333,エンタメ,aaaa
3333,趣味,cccc
4444,エンタメ,aaaa

---
▼したいこと。

1.　まず、＜シート２＞を名寄せ。１アイテム＝１行のデータにする。

【結果】
1111,エンタメ,aaaa
2222,ビジネス,bbbb
3333,エンタメ,aaaa,趣味,cccc
4444,エンタメ,aaaa

2. アイテムIDで、＜シート１＞と＜シート２＞をマッチング

【結果】
いろは,1111,エンタメ,aaaa
にほへと,2222,ビジネス,bbbb
ちるぬ,3333,エンタメ,aaaa,趣味,cccc
るを,4444,エンタメ,aaaa

以上です！
---

エクセルしかない環境で、困っています。どうぞよろしくお願いします！！

回答No.2

zap35 · ベストアンサー

関数式を組み合わせてやる方法もありますが以下の方法が簡単だと思います。

シート２を別シートにコピーして、Ａ列（アイテム名）を挿入します。コピーしたシートのA2セルの式は以下になります。

=IF(ISNA(MATCH(B2,Sheet1!B1:B24000,0)),"",INDEX(Sheet1!A1:A24000,MATCH(B2,Sheet1!B1:B24000,0)))

A2セルをデータ行数分だけコピーします。これでアイテム名がつきました。

重複行を取り除くにはフィルタオプションを使用します。Ｂ列（アイテムＩＤ）を選択して
「データ」→「フィルタ」→「フィルタオプションの設定」を選択します。「リストまたは選択範囲の…」というメッセージが出たら「ＯＫ」でかまいません

「抽出先」は「選択範囲内」を選択し、「重複するレコードは無視する」にチェックして「ＯＫ」で重複がないリストになります。

回答No.3

zap35

＃０２です。訂正です。回答中の式は以下に差し替えてください

=IF(ISNA(MATCH(B2,Sheet1!B$1:B$24000,0)),"",INDEX(Sheet1!A$1:A$24000,MATCH(B2,Sheet1!B$1:B$24000,0)))

お礼

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

＜シート２＞の名寄せ（行の再構成）は、IF関数でアイテムID(Bの列)をキーに、とにかく同じ行の横の列へチマチマともってくることで対応しました。

=IF(B2=B3,C3,""),=IF(B2=B4,C4,""),=IF(B2=B5,C5,"")

↑コレを延々と、振り分けられている最大カテゴリ数分だけ横に展開。

＜シート１＞と＜シート２＞のマッチングは、これもチマチマとVLOOKUPで、値を移しました。

みなさま、本当にありがとうございました。

補足

ありがとうございます！

シート２の重複データは、除いてしまわず
名寄せして、情報は引き継ぎたいのです。。

アイテムID,カテゴリ名,カテゴリID
1111,エンタメ,aaaa
2222,ビジネス,bbbb
3333,エンタメ,aaaa
3333,趣味,cccc
4444,エンタメ,aaaa

↓
×
アイテムID,カテゴリ名,カテゴリID
1111,エンタメ,aaaa
2222,ビジネス,bbbb
3333,エンタメ,aaaa
4444,エンタメ,aaaa

　↓
　○
アイテムID,カテゴリ名,カテゴリID
1111,エンタメ,aaaa
2222,ビジネス,bbbb
3333,エンタメ,aaaa,趣味,cccc
4444,エンタメ,aaaa

この名寄せがやっかいで・・困り果てています。
どうぞ宜しくお願いします！

回答No.1

cafe_au_lait

以下のような方法はいかがでしょうか？

＜シート２＞
例のデータがA1:C5に入っているものとします。
D1に以下の式を入力してD5までフィルコピー

=COUNTIF(A$1:A1,A1)+10*A1

＜シート１＞
例のデータがA1:B4に入っているものとします。
C1に以下の式を入力してC4までフィルコピー

=IF(COUNTIF(シート2!$A$1:$A$5,$B1)<(COLUMN()-1)/2,"",INDEX(シート2!$B$1:$B$5,MATCH($B1*10+(COLUMN()-1)/2,シート2!$D$1:$D$5,0)))

D1に以下の式を入力してD4までフィルコピー

=IF(C1="","",VLOOKUP(C1,シート2!$B$1:$C$5,2,0))

C1:D4を選択して必要なだけ右にフィルコピー

あとは全体をコピー－形式を選択して貼り付け－値とするなどして取り出してください。

カテゴリ数が10を超える場合は修正が必要になります（式中の*10を*100などにする）。

補足

ありがとうございます！！

試してみているのですが、うまくいきません・・。

COLUMN()の範囲指定はしないでこのままでよいでしょうか？