複数ファイルから重複するIDの明細をすべて取り出す
重複するIDを集計で見つけ、その一覧を元の明細に結合します。IDだけでなく、金額や元のファイルを示す情報も残せます。
重複するIDが分かっても、集計結果だけでは個々の金額や元のファイルを確認できません。まず重複IDの一覧を保存して読み込み直し、元の明細に結合しましょう。そのIDを持つすべての明細を取り出せます。
重複IDの一覧だけでは足りない場合
以下の架空の3ファイルには、金額の異なるD1が合計3回出てきます。Batchは、どのファイルから来た明細かを区別するために元データに用意した列です。Repeatedは、最初の集計で作って保存する表なので、手入力する必要はありません。
FileA
| ID | Amount | Batch |
|---|---|---|
| D1 | 4 | A |
| D2 | 8 | A |
FileB
| ID | Amount | Batch |
|---|---|---|
| D1 | 9 | B |
| D3 | 6 | B |
FileC
| ID | Amount | Batch |
|---|---|---|
| D1 | 5 | C |
| D4 | 2 | C |
Repeated
| ID | Count |
|---|---|
| D1 | 3 |
重複IDを保存し元の明細へ結合
- FileA・FileB・FileCを+接続点でつなぎ、「すべての行を保持」を選びます。重複した行も出現回数に含めます。
- 「結果を整理」で「集計」を追加し、「まとめる項目」にIDを選びます。「集計方法」は「件数」のまま、「結果の項目名」にCountと入力します。続けて「絞り込み」を追加し、Countが1「より大きい」行を残します。
- 「適用」を押し、D1・3の1行を確認します。重複IDの一覧を元の明細と照合できるよう、「結果を保存」でExcelに保存し、Repeatedというシートとして読み込み直します。
- 縦結合の先頭にあるFileAをクリックして、結果の基準を元の明細に戻します。「結果を整理」を開き直し、集計と絞り込みを削除して「適用」を押します。FileAのIDとRepeatedのIDを結び、「一致する行のみ」を選びます。
- D1の3つの金額とBatch A・B・Cがすべてあることを確認して保存します。
D1の異なる3明細を確認
第1段階はD1・3の1行です。最終結果にはD1の3明細があり、各行に同じCount 3が付きます。Countを再合計した9を元データの件数と解釈しないでください。
| ID | Amount | Batch | Repeated_ID | Count |
|---|---|---|---|---|
| D1 | 4 | A | D1 | 3 |
| D1 | 9 | B | D1 | 3 |
| D1 | 5 | C | D1 | 3 |
出現回数とファイル数を区別
この手順では、合計で2回以上現れるIDを取り出します。同じファイル内だけで重複するIDも対象です。「必ず2つ以上のファイルにあるID」という条件ではない点に注意してください。元ファイルの重複行は削除されず、元データが変わったときも自動では更新されません。
自分のファイルで始めましょう
JOIN SHEETを使ってみる ↗Excel・CSVの作業はPCでご利用ください。
新しいタブで開くため、作業中のデータは置き換わりません。
サンプルの検証日:
出典
Is there a formula to cross-check and extract out dupe datas between multiple files? (Reddit r/excel)
原文は複数ファイルで重複するコードの明細行をすべて別ファイルに抽出する方法を質問しています。 上の表と手順は、この質問を参考に別途作成した架空の例です。質問者の実データや製品の利用体験ではありません。
