エクセル 重複データの抽出について

エクセル初心者勉強中です。楽しんでいます。
説明不足等あれば申し訳ありません。

現在、エクセルの勉強をしています。テーマは関数です。

下記画像のとおり、一枚のワークシートに、2つの表を作成しました。
あまり質のいい作り方ではありませんが、会員名と発送先を入力したダミーデータです。

ここで行いたい処理なのですが、会員数（6名）すべてを検索し、それぞれ重複している会員名の右右隣に「重複」の記載をいれ、さらにそのデータをJ2セル・K2セルに抽出したいのです。

COOUNTIF関数の引数に複数の範囲を設定しようとしましたが、うまくいきませんでした。SUMPRODUCT関数とかをつかうのでしょうか。

よろしくお願いいたします。

No.2 ベストアンサー 回答者： d-q-t-p 回答日時： 2018/03/21 01:02

表の作りを見直した方がいいと思いますけど……

後で何かに使う（表計算が必要）なら 印刷物としての表の作り方
は手間を増やすことにしかなりません。 マクロを使う前提なら好
きに作ればいいんでしょうけど 関数で何とかしようと思うなら最
初から表をそれに見合った作りにすべきです。

というかその表の作り方なら別に Excelでなくてもいいでしょう。
Excelは機能が充実しているので 関数だけで何かをするようには
できていません。

Googleスプレッドシートに移行したらどうですか？
それなら

=QUERY({B4:C,ARRAYFORMULA(IFERROR(VLOOKUP(B4:B,E4:F,2,FALSE),""))},"where Col3 Is Not Null")

で済みます。お勧めする訳じゃありませんが 印刷前提の表を作っ
てしまったのに 関数に拘る人にはそっちの方が向いてます。

No.3 回答者： 銀鱗 回答日時： 2018/03/21 04:37

作りがどうの、ルールがどうのは置いといて、

この場合の考え方をアドバイス。

紙の上に書いた表から重複するデータを見つける時、どんなことをする？
一つずつ省略せず手順を文章に書いてみましょう。
そして、その手順通りに関数で処理を行えばいいのです。

一度に処理しようと考えちゃダメってこと。
それは関数を十分理解したのちに考えることです。

具体的には作業用の数式を記入する列をたくさん挿入してそこで処理を一つずつ行う。
見栄えが悪いなら、作業用の列を「非表示」にしてしまえばいい。

・・・
あと質問の例で、実はBさんも重複していた…なんて場合も想定した処理をするようにしましょう。

No.1 回答者： amerikaumare 回答日時： 2018/03/21 00:13

「2つの表を作成しました」なのですが　２つの違いはなんでしょうか　同じ「会員発送リスト」なら一つの表でよいように思いますが。

「Aさん」を「重複」としていますが　「送付先」が「東京都」「福岡県」と違うので”同姓同名の他人”の可能性があります。これを”重複”とする根拠は何でしょうか。

一般にこのような重複を探す場合には　一つの表にしたうえで　会員名、送付先でソート（並べ替え）を行い、”重複チェック”列に　＝IF（｛会員名セル｝＝｛１つ上の会員名セル｝、”重複”、””）の式を入れます　（なお　重複行すべてに”重複”といれたいなら｛会員名セル｝＝｛１つ下の会員名セル｝を OR で追加します）　（送付先もチェックするならそれも同様にします　会員名とは AND で追加します）

データベースというものは　今あるデータをそのまま加工しようとすると苦労することがおおいです　データそのものを加工しやすい形にすることも大切です。