エクセルの重複データ抽出、削除についての質問です。

質問

会社で事務をしている者です。２つの顧客リストから重複データを抽出し、それから重複データを削除しなくてはならなくなりました。具体的には以下の順序で行いたいのですが、素人なので方法がわかりません。皆様のお知恵をいただきたいとおもいます。よろしくお願いします。

顧客リストＡ
顧客番号 氏名 住所 金額Ａ
1 聖徳太子 奈良県奈良市 480
2 坂本竜馬 高知県高知市 150
3 織田信長 岐阜県岐阜市 300
4 徳川家康 愛知県名古屋市 320
5 豊臣秀吉 愛知県名古屋市 180
6 紫式部 京都府京都市 230
7 真田幸村 長野県上田市 140
8 土方歳三 東京都日野市 200
9 宮本武蔵 熊本県熊本市 350
10 福沢諭吉 大阪府大阪市 130
11 上杉謙信 新潟県上越市 540

2、顧客リストＢ
氏名 住所 金額Ｂ
土方歳三 東京都日野市 250
千利休 大阪府堺市 280
織田信長 岐阜県岐阜市 300
武田信玄 山梨県甲府市 220
伊達政宗 宮城県仙台市 140
坂本竜馬 高知県高知市 200
聖徳太子 奈良県明日香村 380
福沢諭吉 大阪府大阪市 130
上杉謙信 新潟県上越市 500
西郷隆盛 鹿児島県鹿児島市 660

３、顧客リストＡ・Ｂより氏名、住所の一致した先を検索し、金額ＡおよびＢとともに別シートに抽出。
顧客番号 氏名 住所 金額Ａ 金額Ｂ
2 坂本竜馬 高知県高知市 150 200
3 織田信長 岐阜県岐阜市 300 300
8 土方歳三 東京都日野市 200 250
10 福沢諭吉 大阪府大阪市 130 130
11 上杉謙信 新潟県上越市 540 500

４、抽出した顧客リストのうち金額Ａ・Ｂが同額である先を削除。
顧客番号 氏名 住所 金額Ａ 金額Ｂ
2 坂本竜馬 高知県高知市 150 200
8 土方歳三 東京都日野市 200 250
11 上杉謙信 新潟県上越市 540 500

以上です。使用ソフトはエクセル2003です。
多少手間はかかってもわかりやすい方法でお願いします。

回答No.8

kagakusuki

回答番号ANo.7です。

>うまくいきません。

　どの様に上手く行かなかったのでしょうか？
　試された際に、各シートのどのセルがどの様な結果となったのかを御説明頂けなければ、対策の取りようが御座いません。

>シート２に顧客リストは付されておらず、

　シート２とは、何をするためのシートの事なのでしょうか？
　私が回答したANo.7には、シート２というシートは登場しておりませんし、質問者様が投稿された御質問文にもシート２という言葉は出てきておりません。
　御質問文には、シート名に関する記述や、顧客リストＡや顧客リストBが、どのシートの何列にが何という項目名のデータの入力欄として使用されていて、「聖徳太子」から始まる実際のデータが、何行目以下に入力されているのか等の、重要な情報に関する記述が一切ないため、仕方なく、ANo.7では、あくまでも仮に

>今仮に、
>顧客リストAが存在しているシートのシート名が　　顧客リストA
>顧客リストBが存在しているシートのシート名が　　顧客リストB
>抽出した顧客リストが存在しているシートのシート名が　　抽出先
>であるものとし、各リストの1行目は、「顧客番号」、「氏名」、「住所」、「金額」等の各項目名がA1セルから順番に入力されていて、各データは、2行目以下に表示されているものとします。

とした上で話を進めたのです。
　勿論、実際のデータはあまり公にしたくは無い場合もあるとは思いますが、その場合は、私の回答や他の回答者様方の御回答を参考にして、質問者様御自身の独力で、実際のExcelファイルのレイアウトに合う様に、関数やVBA等の一部を修正しなければなりません。
　ですから、もし、質問者様が関数等を修正する事が出来ないと言われるのでしたら、どのシートの何列にが何という項目名のデータの入力欄として使用されていて、「聖徳太子」から始まる実際のデータが、何行目以下に入力されているのか等の、重要な情報を正確に御教えして頂く事が、問題を解決するのに必要不可欠です。
　ぎりぎり最低限の情報として、
顧客リストＡの中で、「顧客番号」と入力されているセルが、何という名称のシート（シート1なのかSheet1なのか、或いは顧客リストAなのか、シート２なのか、等々）の、何列の何行目のセルなのか、
顧客リストBの中で、「氏名」と入力されているセルが、何という名称のシートの、何列の何行目のセルなのか、
抽出した顧客リストの中で、「顧客番号」と入力されているセルが、何という名称のシートの、何列の何行目のセルなのか、
という情報を全て漏らさず、御教え願います。

>1行目は氏名からとなっています。

　はい、ANo.7の添付画像を作成する際に、顧客リストBシートの1～2行目が欠けてしまいましたが、顧客リストBシートも、1行目は項目名のが入力されていて、実際のデータは2行目からとなっています。
　ですから、上手く行かない原因は、実際のシート名に合わせて、関数の一部を修正されていない事が原因ではないかと思います。

回答No.7

kagakusuki

今仮に、
顧客リストAが存在しているシートのシート名が　　顧客リストA
顧客リストBが存在しているシートのシート名が　　顧客リストB
抽出した顧客リストが存在しているシートのシート名が　　抽出先
であるものとし、各リストの1行目は、「顧客番号」、「氏名」、「住所」、「金額」等の各項目名がA1セルから順番に入力されていて、各データは、2行目以下に表示されているものとします。
　又、上記のシートとは別個に、「補助」というシートを作成し、そのシートのA列とB列を作業列として使用するものとします。

　まず、補助シートのA2セルに次の数式を入力して下さい。

=IF(INDEX(顧客リストB!$A:$A,ROW())="","",INDEX(顧客リストB!$A:$A,ROW())&":"&INDEX(顧客リストB!$B:$B,ROW()))

　次に、補助シートのB2セルに次の数式を入力して下さい。

=IF(COUNTIF($A:$A,"="&INDEX(顧客リストA!$B:$B,ROW())&":"&INDEX(顧客リストA!$C:$C,ROW())),ROW(),"")

　次に、補助シートのA2～B2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　次に、抽出先シートのA2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNT(補助!$B:$B),"",IF(INDEX(顧客リストA!A:A,SMALL(補助!$B:$B,ROWS($2:2)))="","",INDEX(顧客リストA!A:A,SMALL(補助!$B:$B,ROWS($2:2)))))

　次に、抽出先シートのA2セルをコピーして、抽出先シートのB2～D2の範囲に貼り付けて下さい。
　次に、抽出先シートのE2セルに次の数式を入力して下さい。

=IF($B2="","",IF(OR(INDEX(顧客リストB!$C:$C,MATCH($B2&":"&$C2,補助!$A:$A,0))="",INDEX(顧客リストB!$C:$C,MATCH($B2&":"&$C2,補助!$A:$A,0))=$D2),"",INDEX(顧客リストB!$C:$C,MATCH($B2&":"&$C2,補助!$A:$A,0))))

　次に、抽出先シートのA2～E2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　以上です。

補足

回答ありがとうございます。
表までつけていただいてわかりやすくこの方法でためしてみたのですが、うまくいきません。
私の質問がいけなかったのかもしれませんが、シート２に顧客リストは付されておらず、シート２リストの1行目は氏名からとなっています。
これが影響していると思うのですが、どうすればよいか教えてください。<m(__)m>

回答No.6

tom04

No.2・4です！
たびたびごめんなさい。

前回の数式でSheet名を統一していなかったので、エラーになると思います。

もう一度数式のみ投稿します。

「顧客リストA」Sheetの作業列E2セル
=IF(COUNTIF(顧客リストB!D:D,B2&C2),IF(D2<>INDEX(顧客リストB!C:C,MATCH(B2&C2,顧客リストB!D:D,0)),ROW(),""),"")

「顧客リストB」Sheetの作業列D2セル
=A2&B2

Sheet3のA2セル
=IF(COUNT(顧客リストA!$E:$E)<ROW(A1),"",INDEX(顧客リストA!A:A,SMALL(顧客リストA!$E:$E,ROW(A1))))
をD2セルまでオートフィルでコピー！

Sheet3のE2セル
=IF(A2="","",INDEX(顧客リストB!C:C,MATCH(B2&C2,顧客リストB!D:D,0)))

です。
Sheet1　→　Sheet名を「顧客リストA」
Sheet2　→　Sheet名を「顧客リストB」
としています。

※　とりあえずはこの画面から数式を各Sheetにコピー＆ペーストしてやってみてください。

画面上で数式部分を範囲指定　→　右クリック　→　コピー　→　
各Sheetのセルを選択　→　右クリック　→　貼り付け　です。m(_ _)m

回答No.5

keithin

ん～～～～と？　判らないのはこういう事ですか？？？

＞シート１の『Ｂ１：Ｄ１』をコピーし，シート２の該当するＡ１：Ｃ１に貼り付ける

その説明の前段までに説明した通りに，エクセルを作ってください。
ご質問では一体どこになにを記入しているのかさっぱり情報提供が足りていませんので，イチイチ「ここに何を置く」と説明しています。回答の手順をご自分のエクセルに当てはめて操作できないときは，回答した通りにあなたのエクセルを作り直してから手順を追ってください。

『Ｂ１：Ｄ１』というのは「Ｂ１セルからＤ１セルまでのセル範囲」の意味です。
添付図上半分で，赤で囲ったセル範囲です。

Ｂ１セルからＤ１セルまでのセル範囲を選択しておいて，コピーしてくださいと言っています。

その後リストＢのシート２に移動し，Ａ１セルからＣ１セルまでのセルを選んで，貼り付けてください。
添付図下半分で矢印で指した先に貼り付けを行うということです。

お礼

素人の私に親切な回答ありがとうございます。
何から何までご面倒おかけしてすみません。
本当に感謝いたします。

回答No.4

tom04

No.2です！
前回は少しでも手を掛けないように！とVBAでやってしまいましたが、
ご希望の方法でなくてごめんなさい。

今回は数式でやってみました。
（これもご希望の方法でなかったらごめんなさいね。）

↓の画像（小さくて見えづらいと思いますので、画面を拡大してみて下さい）のように作業用の列を
「顧客リストA」・「顧客リストB」それぞれのSheetに設けています。

「顧客リストA」Sheetの作業列E2セルに
=IF(COUNTIF(Sheet2!D:D,B2&C2),IF(D2<>INDEX(Sheet2!C:C,MATCH(B2&C2,Sheet2!D:D,0)),ROW(),""),"")
という数式を入れオートフィルでずぃ～～～！っと下へコピー！

「顧客リストB」Sheetの作業列D2セルに
=A2&B2
という数式を入れこれもオートフィルでずぃ～～～！っと下へコピーしておきます。

Sheet3のA2セルに
=IF(COUNT(顧客リストA!$E:$E)<ROW(A1),"",INDEX(顧客リストA!A:A,SMALL(顧客リストA!$E:$E,ROW(A1))))
という数式を入れ列方向へD2セルまでオートフィルでコピー！
E2セルには
=IF(A2="","",INDEX(Sheet2!C:C,MATCH(B2&C2,Sheet2!D:D,0)))
という数式を入れます。

最後にA2～E2セルを範囲指定　→　E2セルのフィルハンドルで下へコピー！

これで画像のような感じになります。
これでそれぞれのSheetの内容が変更されるたびにSheet3に反映されるはずです。

以上、長々と書きましたが
参考になれば良いのですが・・・m(_ _)m

お礼

表までつけていただいて感謝いたします。
わかりやすかったので、これでやってみたのですがデータ件数が多く、メモリー不足となってしまいました。
ほかの方法試してみます。ありがとうございました。

回答No.3

noname#192382

回答のなかまにいれてください。シート1としーと2にデータを入れて次のマクロを実行すればシート3とシート4 に望みどおりの表がつくられました。
Sub Macro2()
'
' Macro2 Macro
' マクロ記録日 : 2011/10/29 ユーザー名 :
'
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim c1 As Range
Dim c2 As Range
Dim c3 As Range
Dim mygyo As Integer

With Sheets("Sheet1")
Set rng1 = .Range("B1", .Range("B" & Rows.Count).End(xlUp))
End With

For Each c1 In rng1
c1.Offset(, 3).Value = c1.Value & c1.Offset(, 1).Value
c1.Offset(, 4).Value = c1.Offset(, 2).Value
Next

With Sheets("Sheet2")
Set rng2 = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
End With

For Each c2 In rng2
c2.Offset(, 3).Value = c2.Value & c2.Offset(, 1).Value
c2.Offset(, 4).Value = c2.Offset(, 2).Value
Next

With Sheets("Sheet1")
Set rng1 = .Range("E1", .Range("E" & Rows.Count).End(xlUp))
End With
With Sheets("Sheet2")
Set rng2 = .Range("D1", .Range("D" & Rows.Count).End(xlUp))
End With

rng1.Offset(, 2).ClearContents
rng1.Offset(, 2).ClearFormats

For Each c1 In rng1
For Each c2 In rng2

If c1.Value = c2.Value Then
With c1.Offset(, 2)
.Value = c2.Offset(, 1).Value
End With
Exit For
End If

Next
Next

With Sheets("Sheet1")
Set rng1 = .Range("G1", .Range("G" & Rows.Count).End(xlUp))
End With
mygyo = 1
For Each c1 In rng1

If c1.Value <> "" Then

Sheets("Sheet3").Cells(mygyo, 1) = c1.Offset(, -6).Value

Sheets("Sheet3").Cells(mygyo, 2) = c1.Offset(, -5).Value
Sheets("Sheet3").Cells(mygyo, 3) = c1.Offset(, -4).Value

Sheets("Sheet3").Cells(mygyo, 4) = c1.Offset(, -1).Value
Sheets("Sheet3").Cells(mygyo, 5) = c1.Value
mygyo = mygyo + 1

End If

Next

With Sheets("Sheet3")
Set rng3 = .Range("D1", .Range("D" & Rows.Count).End(xlUp))
End With
mygyo = 1
For Each c3 In rng3

If c3.Value <> c3.Offset(, 1).Value Then

Sheets("Sheet4").Cells(mygyo, 1) = c3.Offset(, -3).Value

Sheets("Sheet4").Cells(mygyo, 2) = c3.Offset(, -2).Value
Sheets("Sheet4").Cells(mygyo, 3) = c3.Offset(, -1).Value

Sheets("Sheet4").Cells(mygyo, 4) = c3.Value
Sheets("Sheet4").Cells(mygyo, 5) = c3.Offset(, 1).Value
mygyo = mygyo + 1

End If

Next

End Sub
'

回答No.2

tom04

こんばんは！
VBAでの一例です。

Sheet名はどうなっていても良いのですが、画面左下にあるSheet見出しの順は
「顧客リストA」Sheetは一番左側（元のSheet1の場所）
「顧客リストB」SheetはSheet見出しの2番目にあるとします。
両Sheetともデータは2行目からあるとします。
両Sheetを比較　→　氏名・住所が同じ場合、金額A・金額Bが違うものをSheet3に表示！
としてみました。（削除は考えていません）

Alt+F11キー　→　画面左側にある「This Workbook」をダブルクリック　→　VBE画面が出ますので
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Dim i, j As Long
Dim ws1, ws2, ws3 As Worksheet
Set ws1 = Worksheets(1)
Set ws2 = Worksheets(2)
Set ws3 = Worksheets(3)
ws3.Cells.Clear
With ws3.Cells(1, 1)
.Value = ws1.Cells(1, 1)
.Offset(, 1) = ws1.Cells(1, 2)
.Offset(, 2) = ws1.Cells(1, 3)
.Offset(, 3) = ws1.Cells(1, 4)
.Offset(, 4) = ws2.Cells(1, 3)
End With
Application.ScreenUpdating = False
For i = 2 To ws1.Cells(Rows.Count, 1).End(xlUp).Row
For j = 2 To ws2.Cells(Rows.Count, 1).End(xlUp).Row
If ws1.Cells(i, 2) = ws2.Cells(j, 1) Then
If ws1.Cells(i, 3) = ws2.Cells(j, 2) And ws1.Cells(i, 4) <> ws2.Cells(j, 3) Then
With ws3.Cells(Rows.Count, 1).End(xlUp).Offset(1)
.Value = ws1.Cells(i, 1)
.Offset(, 1) = ws1.Cells(i, 2)
.Offset(, 2) = ws1.Cells(i, 3)
.Offset(, 3) = ws1.Cells(i, 4)
.Offset(, 4) = ws2.Cells(j, 3)
End With
End If
End If
Next j
Next i
Application.ScreenUpdating = True
ws3.Columns("A:E").AutoFit
End Sub 'この行まで

※　自動で表示！という訳にはいきません。Sheet1・Sheet2のデータが変わるたびにマクロを実行してみてください。

以上、参考になれば良いのですが・・・m(_ _)m

お礼

早速の回答ありがとうございました。
パソコンの知識に本当にお詳しい方なのですね。
残念ながら私の知識が追いついていきません。
素晴らしい回答なのに生かすことができず申し訳ない気持ちです。
ありがとうございました。

回答No.1

keithin

＞多少手間はかかってもわかりやすい方法

難しい数式を極力使わないお奨め手順をお話しします。

シート１にリストＡがあるとする
シート２にリストＢがあるとする

ヤリタイ事：
「名前・住所」リストＡＢに共にあり，「金額」が違う一覧を作成したい

準備
シート１のＡＢＣＤ列に番号・氏名・住所・金額が記入されている
　Ａ１に「番号」Ｂ１に「氏名」Ｃ１に「住所」Ｄ１に「金額」と記入する
シート２のＡＢＣ列に氏名・住所・金額が記入されている
　シート１の『Ｂ１：Ｄ１』をコピーし，シート２の該当するＡ１：Ｃ１に貼り付ける

手順：
シート３を開く
データメニューのフィルタから「フィルタオプションの設定」を開始する
　抽出先は指定した範囲にマーク
　リスト範囲にリストＡの番号から金額までのセル範囲（Sheet1!$A$1:$D$99など）を設定
　検索条件範囲にリストＢの「名前と住所」のセル範囲（Sheet2!$A$1:$B$15など）を設定
　抽出範囲はシート３のＡ１を設定してＯＫする

→リストＡＢで名前と住所が重複したリストが作成される

シート３で
データメニューのフィルタから「フィルタオプションの設定」を開始
　抽出先は選択範囲内にマーク
　リスト範囲にシート３の氏名から金額Ａまでのセル範囲を設定
　検索条件範囲にリストＢの「名前と住所と金額」のセル範囲(Sheet2!$A$1:$C$15など)を設定してＯＫする

→リストＡＢで名前と住所と金額が重複したリストが抽出される
→このデータは要らないので行選択し，行削除して上に詰めてしまう

データメニューのフィルタで「全て表示」する
→名前と住所が重複し，金額の違うリストが抽出され，Ｄ列には金額（金額Ａ）が表示されている
Ｅ列に金額Ｂと記入し
Ｅ２：
=SUMPRODUCT((Sheet2!$A$1:$A$99=B2)*(Sheet2!$B$2:$B$99=C2),Sheet2!$C$2:$C$99)
のように数式を入れて，金額Ｂを計算する。

補足

早速の回答感謝いたします。
たぶんお勧めの手順だと思うのですが、残念ながら私自身素人すぎてついていけません。
最初の段階からつまづいていて、準備の段階で、「シート１の『Ｂ１：Ｄ１』をコピーし，シート２の該当するＡ１：Ｃ１に貼り付ける」というのは具体的にどこをどのように貼り付けるのでしょうか？
ご面倒でも教えていただきたくお願いいたします。