いない人を抽出したい

エクセルで会員リストを作っています。

会員リストは、半期(６ヶ月)に一度作成し、５月と１１月に作成します。
現会員は、最新の会員リストから分かりますが、退会された会員は消えていくばかりで分かりません。

そこで、退会した会員のリストを過去５年分遡って作成したいのですが、
どのようにしたら、いいでしょうか。
半年、１年で退会した人も含みます。過去５年間で現会員でない人のリスト化をしたいのです。

リストは、Ａ２：会員番号、Ｂ２：氏名、Ｃ２：リスト作成日が１００件続く簡単な物です。

半期に１度会員リストを作成するので、１００名×２回／年×５年で１０００名程度の会員名簿があります。

No.4 回答者： str1234 回答日時： 2023/06/07 15:00

こんにちは。

最新のリストで前回のリストから新規に増えた方は分るものとします。

１．最新版を除く、５年分のデータを１つのデータにする。
２．データ　→　重複の削除で重複の無いデータにする。
３．最新版のデータから新規に増えた方のデータを削除して、一番下の行に貼り付ける（コピーする）。
４．ホーム　→　条件付き書式　→　セルの強調表示ルール→　重複する値→　OK
５.重複した値が色付きで表示されるので、色がついている行を纏めて削除すれば、現会員で無い方のリストになります。

No.3 回答者： tatsumaru77 回答日時： 2023/06/06 09:07

補足要求です。

１．会員リストはどのようにブックに格納されていますか。
下記のケースのどちらでしょうか。
①１つのブック内に格納されている。
　１ブック内に5年分のシートがある。
②複数のブック内に格納されている。
　１ブック内に１年分のシートがある。（もしくは半年分、２年分等）

２．ブック内の会員リストのシート名は、具体的にどのようになっていますか。

３．ブック内に会員リスト以外のシートもありますか。
もし、ある場合、会員リストと会員リストでないシートをどのように区別すれば良いですか。

４．最新の会員リストと過去の会員リストをシート名で区別する方法はありますか。

No.2 回答者： goomania 回答日時： 2023/06/06 08:44

この会の入会時期に制限はあるのでしょうか?

(1)入会時期に制限はなく随時入会できる
(2)入会時期は5月と11月と定めており、その際に会員名簿を作成している

(2)であれば、

>半年、１年で退会した人も含みます。過去５年間で現会員でない人のリスト
>化をしたいのです。

というご質問者のご希望はNo.1さんがお示しになった方法で実現できると思います。

しかし、(1)の場合、そもそも名簿に掲載される前に退会した人が存在する可能性があるので、全ての退会者を把握することは困難です。

つまり(1)の場合名簿作成直後に入会し、次回名簿作成直前に退会した人は「半年で退会した人」になりますが、どの名簿にも掲載されないことになり把握できないということです。

退会者名簿作成の目的にもよりますが、「一度でも在籍した人を特定したい」というような目的で(1)の場合は上記のような「漏れ」がでるので注意が必要です。

No.1 回答者： fujillin 回答日時： 2023/06/05 22:15

こんばんは

いずれにしろ、まず
・100名×5年分のリストをひとつにまとめておく
のが便利です。
（コピペ10回で済みますので、手作業の方が速いと思います）

会員番号の重複発行はされていないと考えてよいのでしょうから・・

office 365環境等であれば、
=UNIQUE(FILTER(5年分のリスト範囲,COUNTIF(現リストの会員番号列,5年分のリストの会員番号列)=0,""))
を空きセルに入力すれば、ご希望のリストがスピルされます。

旧いバージョンのエくjセルの場合は
まず、5年分のリストで「重複を削除」を行います。
（「データ」―「重複の削除」）
後は、上記同様にCOUNTIFで現リストに無い会員番号のデータを抽出すればよいですが、ローテクなので少々長い式になります。
（一発で求めずに、手順を分けて抽出してゆけば、さほどでもないでしょう）