重複データのチェック方法がわかりません。

質問

はじめまして。エクセル・アクセス関係を最近さわりはじめました。
下記について重複しているデータをチェックしたいのですが、いろんなサイトをみても「これ！」というのが見当たらず困っています。どのような式を作成すればよいのか、ご教授ください。
ちなみに今はMATCH関数を使いながら、データを並び替え・・とすごく面倒なことをしています。

顧客ID　　　　　請求番号　　　　　　　登録日

1111　　　　　　　0001　　　　　　　　　2010/01/01 　
1112　　　　　　　0001　　　　　　　　　2011/11/11　　
1113　　　　　　　　0002　　　　　　　　　2010/02/01　　
1113 　　　　　 0003 　　　 2011/01/01　　
1114 　　　　　 0004 　　　 2011/01/01　　
1114 　　　　　 0005 　　　 null　　　　　　　　

上記の顧客データあるとします。
顧客IDや請求IDが重複しているものがあるため、これらを最終的には下記（＜最終形態＞）のようにしたいのですが、その前に横の列にチェック（＜チェック＞）をいれたいです。

＜最終形態＞

顧客ID　　　　　請求番号　　　　　　　登録日 チェック

1112　　　　　　　0001　　　　　　　　　2011/11/11　　　　残し
1113 　 　　　0003 　　　 2011/01/01　　　　　　　残し
1114 　 　　　0004 　　　 2011/01/01　　　　　　　確認
1114 　 　　　0005 　　　 null　　　　　　　　　　　　確認

＜チェック＞

顧客ID　　　　　請求番号　　　　　　　登録日　　　　　　　チェック

1111　　　　　　　0001　　　　　　　　　2010/01/01　　　　　削除 　
1112　　　　　　　0001　　　　　　　　　2011/11/11　　　　　残し
1113　　　　　　　0002　　　　　　　　　2010/02/01　　　　　削除
1113 　 　　　0003 　　　 2011/01/01　　　　　　　　残し
1114 　　　　 0004 　　　 2011/01/01　　　　　　　　確認
1114 　　　　 0005 　　　　 null　　　　　　　　　　　　　　確認

(1)請求番号が同じで顧客IDが異なり、登録日も違う場合は、登録日が新しいほうに「残し」、古いほうに「削除」を入れる

(2)顧客IDが同じで請求番号が異なり、登録日も違う場合は、登録日が新しいほうに「残し」、古いほうに「削除」を入れる

(3)顧客IDが同じで請求番号が異なる、あるいは請求番号が同じで顧客IDが異なり、登録日がnull(どちらか一方でも)の場合は、「確認」を入れる→登録日がともに同じ日付でも「確認」を入れる

※ちなみに、実際の重複するデータは上記のように連続して入っていません。
順序はばらばらです※

エクセルでもアクセスでもかまいませんが、やりやすいほうでご教授いただけると幸いです。
よろしくお願いします。
SQLはよくわかっていません。。
最初から教えていただけると幸いです。

回答No.5

Siegrune

ANo4の続きです。
>この場合、顧客IDもしくは請求番号が重複していて、登録日が同じであった場合「確認」となるのでしょうか？
の対応ですが、遅くなってしまい、すいません。
（ちょっと悩ましかった点があったので。）

ANo.3の
>D2セルは、=if(isna(vlookup(A2,A$1:A1,1,false))=true,"残し","削除")
を
=if(isna(vlookup(A2,A$1:A1,1,false))=true,"残し",if(vlookup(A2,A$1:C1,3,false)=C2,"確認","削除"))
とすれば、"削除"となるもの(2件目以降)を"確認"にできます。
※
チェックしている行より上の行（すなわち登録日が同じか後の行）に同じ顧客IDがあったときは、
登録日の列の値を比較して同じなら"確認にする処理"。

あわせて、ANo.4の
>H2 =if(OR(D2="削除",E2="削除"),"削除",if(OR(F2="確認",G2="確認"),"確認","残し"))
は
H2 =if(OR(D2="削除",E2="削除"),"削除",if(OR(D2="確認",E2="確認",F2="確認",G2="確認"),"確認","残し"))
とします。

★この状態では、最初に出現した行が"残し"になっています。
I2 = if(isna(vlookup(A2,$A3:$H$65535,1,false))=true,"",if(vlookup(A2,$A3:$H$65535,8,false)="確認","確認",""))
J2 = if(isna(vlookup(B2,$B3:$H$65535,1,false))=true,"",if(vlookup(B2,$B3:$H$65535,7,false)="確認","確認",""))
を全行にコピー＆ペーストします。
※$65535は一番下の行(Excel2003およびその互換モードでの最大行)。確認してその行の値でもＯＫ。
対象行と同一の顧客IDもしくは請求番号の行が確認となっているかどうかを判定しています。
（上記の判定で、同一日のデータのみ"削除"ではなく"確認"になっているはず。）

K2 = if(OR(I2 ="確認",J2="確認"),"確認",H2)
としてコピー＆ペーストすれば、K列は、
顧客IDもしくは請求番号が重複していて、登録日が同じであった場合「確認」となるはず。
（試してはいませんが。）

注：同一日の顧客IDがあるのだが、より新しい日付の請求番号があるときはどうなるのかな？
D2="確認",E2="削除"となるため、H2="削除"となっており、K2も"削除"とでてくるはず。

ま、試してみていただいて問題あるようでしたら、お礼なり補足なりに記載願います。

お礼

ありがとうございます。
一度試してみます！
今度対象データを扱うのが下旬ころになりそうですが、試してまた連絡いたします。

回答No.4

Siegrune

ANO.3です。

すいません。
H2 =if(OR(E2="削除",F2="削除"),"削除",if(OR(G2="確認",H2="確認"),"確認","残し"))
の式は、間違えていました。
H2 =if(OR(D2="削除",E2="削除"),"削除",if(OR(F2="確認",G2="確認"),"確認","残し"))

D2:顧客IDが同じで後の登録日がある場合"削除"　さもなければ、　さもなければ、"残し"
E2:請求番号が同じで後の登録日がある場合"削除"　さもなければ、"残し"
F2:顧客IDが同じで他にNullの登録日がある場合"確認"　さもなければ、""
G2:請求番号が同じで他にNullの登録日がある場合"確認"　さもなければ、""
と表示させようとしています。

H2は、
顧客IDが同じで後の登録日がある場合か請求番号が同じで後の登録日がある場合に"削除"
顧客IDが同じで他にNullの登録日がある場合か請求番号が同じで他にNullの登録日がある場合に"確認"
他は、"残し"と出そうとしています。

>この場合、顧客IDもしくは請求番号が重複していて、登録日が同じであった場合「確認」となるのでしょうか？

としたければ、
・・・ちょっと時間がないので後で。

お礼

ありがとうございます。
Nullだと最後「削除」となってしまったので、あれ？！と思っていたんです(^-^;

登録日同じ場合…またご連絡お待ちしております。

回答No.3

Siegrune

## SQL使えりゃ簡単ですが使えないのでEXCELでやってみる前提で。

まず、ロジックに問題があります。
(1)と(2)を以下の例に同時に適用しようと思うとどうなりますか？
顧客ID　　　　　請求番号　　　　　　　登録日　　　　
1111　　　　　　　0001　　　　　　　　　2010/01/01
1112　　　　　　　0001　　　　　　　　　2011/11/11
1112　　　　　　　0002　　　　　　　　　2011/12/11

顧客ID　　　　　請求番号　　　　　　　登録日　　　　　　　(1)のチェック　(2)のチェック
1111　　　　　　　0001　　　　　　　　　2010/01/01　　　　　削除 　
1112　　　　　　　0001　　　　　　　　　2011/11/11　　　　　残し　　　　削除
1112　　　　　　　0002　　　　　　　　　2011/12/11　　　　　　　　　　　残し

真ん中の行は削除か残しかどっち？
・・・この例では、削除だと目でみたら分かるのでしょうけど。

★登録日を基準に重複する顧客IDや請求番号があれば古い登録日の行に"削除"をいれる
と考えるべきでは？

もしそうならば、の作業方法例。
(1)登録日の逆順に並び替える

A 　　　　　B　　　　　　　 C　　　　
1 顧客ID　　　　　請求番号　　　　　　　登録日　　　　
2 1112　　　　　　　0002　　　　　　　　　2011/12/11
3 1112　　　　　　　0001　　　　　　　　　2011/11/11
4 1111　　　　　　　0001　　　　　　　　　2010/01/01

(2)登録日がNullのデータだけ別シートにコピーします。

(3)登録日のとなりのセルに、vlookup（matchでもいい）を使った式を入れる。
式は、D2セルは、=if(isna(vlookup(A2,A$1:A1,1,false))=true,"残し","削除")
D3セル以降はコピペすると
D3 =if(isna(vlookup(A3,A$1:A2,1,false))=true,"残し","削除")
・・・となります。
（直前までに同一顧客IDがあれば"削除"なければ"残し"）
E列にも同様に請求番号について処理する式をいれます。
E2 =if(isna(vlookup(B2,B$1:B1,1,false))=true,"残し","削除")

次はNullチェックのための数式を入れていきます。
F列には、
F2 =if(isna(vlookup(A2,別シート!A:A,1,false))=true,"","確認")
G列には、
G2 =if(isna(vlookup(B2,別シート!B:B,1,false))=true,"","確認")

で、答えは、H列に
H2 =if(OR(E2="削除",F2="削除"),"削除",if(OR(G2="確認",H2="確認"),"確認","残し"))

A 　　　　　B　　　　　　　 C　　　　 D
1 顧客ID　　　　　請求番号　　　　　　　登録日　　　　
2 1112　　　　　　　0002　　　　　　　　　2011/12/11
3 1112　　　　　　　0001　　　　　　　　　2011/11/11
4 1111　　　　　　　0001　　　　　　　　　2010/01/01

こんなところでどうでしょう。

お礼

ありがとうございます。確かにおっしゃるとおり！です。
登録日を第一優先で残していきます。

この場合、顧客IDもしくは請求番号が重複していて、登録日が同じであった場合「確認」となるのでしょうか？

回答No.2

KURUMITO

かなり難しいご質問で下の方法で良いのかどうかわかりませんが作業列を作って対応するのがよいでしょう。
お示しの表がＡ列からＣ列の２行目から下方にあるとしてＤ２セルには次の式を入力して下方にドラッグコピーします。

=IF(A2="","",IF(COUNTIF(A$2:A2,A2)=1,MAX(D$1:D1)+1,INDEX(D$1:D1,MATCH(A2,A:A,0))))

Ｅ２セルには次の式を入力して下方にドラッグコピーします。

=IF(B2="","",IF(COUNTIF(B$2:B2,B2)=1,MAX(E$1:E1)+1,INDEX(E$1:E1,MATCH(B2,B:B,0))))

Ｆ２セルには次の式を入力して下方にドラッグコピーします。

=IF(AND(A2="",(ROW(A1)-COUNT(E:E))*100000<MAX(F$1:F1)+100000),(ROW(A1)-COUNT(E:E))*100000,IF(COUNTIF(E:E,E2)>1,E2*100000+IF(C2="null",0,C2),IF(COUNTIF(D:D,D2)>1,D2*100000+IF(C2="null",40000,C2),"")))

チェックの結果をＧ列に表示させるとしてＧ２セルには次の式を入力して下方にドラッグコピーします。

=IF(A2="","",IF(OR(MOD(F2,10000)=0,COUNTIF(F:F,ROUNDDOWN(F2,-4))>0),"確認",IF(RANK(F2,F:F,1)=RANK(ROUNDDOWN(F2,-5)+100000,F:F,1)-1,"残し","削除")))

最後に残しと確認の行だけを「フィルタ」機能を使って抽出してコピーし、別のシートに貼り付けをすればよいでしょう。

お礼

ありがとうございます。
一度この方法でやってみます。

来週以降になるかと思いますがまたご連絡いたします。

補足

G列の式がエラーになります(>_<)

回答No.1

localica

エクセルのバージョンによってタブの位置が変わりますが、「オートフィルタ」を選択しフィルタオプションの「重複するレコードは無視する」にチェックを入れるとお望みの動作になります。

お礼

ありがとうございます。

しかし、これでどうやって登録日が新しいものに「残し」、古いものに「削除」、確認すべきデータに「確認」といれればよろしいのですか？