エクセルで２つの表から「合致する品目」「合致しない品目」を、抜き出す方法は？

質問

とある披露イベントを開催しました。当日来てくれた来場者の名簿があります（あるのは、名前だけです）。

また、それとは別にとある学校のOBリストがあります。

来場者名簿の中から、
学校のOBの人
OBではない人

を抜き出したいのですが、どうしたらいいでしょうか？

--

つまり、
●イベント来場者リスト（名前だけ）リストＡ
●OBリスト（名前だけ）リストＢ
の、２つのリストがあり、

リストＡの中で、Ｂと合致する項目を抽出
リストＡの中で、Ｂと合致しない項目を抽出

したいということです。
スッキリとしたマクロや関数で、スマートに処理したいと思っていますので、適した方法がありましたらご教示ください。

回答No.1

KURUMITO · ベストアンサー

リストＡをシート1、リストＢをシート2としてどちらのシートにもＡ列に名前が載っているとします。
シート1のＢ1セルには次の式を入力して下方にオートフィルドラッグします。
=IF(A1="","",IF(COUNTIF(Sheet2!A:A,A1),"合致名前","不合致名前"))

お礼

ありがとうございます。バッチリです！

回答No.5

tom04

こんばんは！
すでに回答は出ていますので
参考程度で目を通してみてください。

一例です。
↓の画像の左がBOOK1で右がBOOK2になります。

BOOK1のD列を作業用の列とさせていただいています。

D2セルに
=IF(COUNTIF([Book2]Sheet1!A$2:A$1000,A2),1,"")

そして、B2・C2セルは配列数式になってしまいますので
この画面からコピー＆ペーストしただけではエラーになると思います。
貼り付け後、F2キーを押すか、数式バー内で一度クリックします。
編集可能になりますので
Shift＋Ctrl＋Enterキーで確定してください。
数式の前後に｛　｝マークが入り配列数式になります。

B2セルは
=IF(COUNT($D$2:$D$1000)<ROW(A1),"",INDEX($A$2:$A$1000,SMALL(IF($D$2:$D$1000=1,ROW($1:$999)),ROW(A1))))

C2セルは
=IF(COUNTA($A$2:$A$1000)-COUNT($D$2:$D$1000)<ROW(A1),"",INDEX($A$2:$A$1000,SMALL(IF($D$2:$D$1000<>1,ROW($1:$999)),ROW(A1))))

として、Shift＋Ctrl＋Enterキーで確定です。

最後にB2～D2セルを範囲指定し、D2セルのフィルハンドルで下へコピーすると
画像のような感じになります。

尚、数式は1000行目まで対応できるようにしています。

以上、長々と書きましたが、参考になれば幸いです。
他に良い方法があれば読み流してくださいね。m(__)m

回答No.4

ka_na_de

こんにちは。

VBAによる一例です。

前提として、
A列の２行目以降：　イベント来場者リスト（名前だけ）
B列の２行目以降：　OBリスト（名前だけ）
C列の２行目以降：　リストＡの中で、Ｂと合致する項目を抽出
D列の２行目以降：　リストＡの中で、Ｂと合致しない項目を抽出

Sub test()
　　Dim myRng1 As Range, myRng2 As Range, c As Range
　　Dim i As Long, j As Long
　　Dim myAns As Variant

　　Set myRng1 = Range("A2", Cells(Rows.Count, "A").End(xlUp))
　　Set myRng2 = Range("B2", Cells(Rows.Count, "B").End(xlUp))

　　i = 2:　　j = 2
　　For Each c In myRng1
　　　　myAns = Application.Match(c.Value, myRng2, 0)
　　　　If IsError(myAns) = False Then
　　　　　　Cells(i, "C").Value = c.Value
　　　　　　i = i + 1
　　　　Else
　　　　　　Cells(j, "D").Value = c.Value
　　　　　　j = j + 1
　　　　End If
　　Next c

　　Set myRng1 = Nothing
　　Set myRng2 = Nothing
End Sub

回答No.3

MackyNo1

イベント来場者リストがA2セルからA100セルの範囲内に入力されているなら、以下の式でOBリストに含まれているイベント来場者を抽出することができます。

=INDEX(A:A,SMALL(INDEX(ISNA(MATCH($A$2:$A$100,OBリスト範囲,0))*1000+ROW($A$2:$A$100),),ROW(A1)))&""

同様にOBリストに含まれていない人は上記の数式の「ISNA」の部分を「ISNUMBER」に置換した数式になります

回答No.2

mhassy

ご質問の「表題」は、合致する品目についてとご記入されていますが、ご質問の説明には品目に関する記述が見当たりません・・・

仮に、品目での一致チェック方法であれば、重複した品の「名称」を考慮する必要が少ないと考えられるため、質問に詳細を記入してさえいれば、スマートな方法の紹介が出来そうだとも思えます。

実際のご質問の文章は「品目」とは無関係な「名前」の一致について記述されています。

名前の場合、姓名なのか？　姓だけなのか？　姓名の間にスペースがあるのか？
など、「比較するデータそれぞれ」が正規化されているのかどうかによって、ご希望の「スマートな比較」手段に違いが出ることをご存知ないご様子です。

「山田太郎」と「山田　太郎」は、そのままの比較では「同じ」と認識できません。
正規化を行い、同じ条件で項目を比較できるように編集することが求められます。

また、特にOBリストであれば「同姓同名」が存在することも考慮すべき点です。
比較する片割れの状態についての説明が見当たりません。
OBリスト内の重複の扱いについて、どこにも記述が見当たりません。

表題にある品目の比較ではなく、名前（＝曖昧）の比較手段についてのご質問ですが、比較すべきA・Bのリストの「状態説明」が欠如しているように感じます。

「スッキリ」とか「「スマート」などの注文をするわりに、必須な「前提条件・状態」のご説明も無く、しかも表題と異なるご質問内容です。
ご質問をきちんと整理することをお勧めしたいと考えます。