65000以上のデータの重複検索

質問

■Ａ列・Ｂ列に６桁の数字が入力されています。
※データ数が65000以上なのでＡ列では収まりきれません。

Ａ列・Ｂ列の中で重複しているものを見つけたいのですが、
どのようにするのがベストでしょうか？
通常COUNTIFを使用するのですが、２列にまたがっている為うまく出来ません。

お教えいただけましたら助かります。
よろしくお願い致します。

回答No.5

kagakusuki · ベストアンサー

【関数と作業列を使う方法】
　今仮に、６桁の数字がＡ列・Ｂ列に入力されているシートがSheet1であるものとし、
Sheet3のA列とB列を作業列として使用し、
Sheet2のA列に重複している数値を、全て表示し、その右方にあるセルに、重複している箇所のセル番号を表示させるものとします。

　まず、Sheet3のA1セルに次の数式を入力して下さい。

=IF(AND(COUNTIF(Sheet1!$A:$B,INDEX(Sheet1!A:A,ROW()))>1,COUNTIF(INDEX(Sheet1!$A:$A,1):INDEX(Sheet1!$B:$B,ROW()),INDEX(Sheet1!A:A,ROW()))-(INDEX(Sheet1!A:A,ROW())=INDEX(Sheet1!B:B,ROW()))=1),INDEX(Sheet1!A:A,ROW()),"")

　次に、Sheet3のA1セルをコピーして、Sheet3のB1セルに貼り付けて下さい。
　次に、Sheet3のA1～B1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。

　次に、Sheet2のA2セルに次の数式を入力して下さい。

=IF(ROWS($A$2:A2)>COUNT(Sheet3!$A:$B),"",SMALL(Sheet3!$A:$B,ROWS($A$2:A2)))

　次に、Sheet2のB2セルに次の数式を入力して下さい。

=IF(COLUMNS($B:B)>COUNTIF(Sheet1!$A:$B,$A2),"",IF(COLUMNS($B:B)>COUNTIF(Sheet1!$A:$A,$A2),"B","A")&(MATCH($A2,INDEX(Sheet1!$A:$B,IF(AND(COLUMNS($B:B)>1,LEFT(A2,1)=IF(COLUMNS($B:B)>COUNTIF(Sheet1!$A:$A,$A2),"B","A")),ROW(INDIRECT(A2)),0)+1,(COLUMNS($B:B)>COUNTIF(Sheet1!$A:$A,$A2))+1):INDEX(Sheet1!$A:$B,MAX(MATCH(9^99,Sheet1!$A:$A),MATCH(9^99,Sheet1!$B:$B)),(COLUMNS($B:B)>COUNTIF(Sheet1!$A:$A,$A2))+1),0)+IF(AND(COLUMNS($B:B)>1,LEFT(A2,1)=IF(COLUMNS($B:B)>COUNTIF(Sheet1!$A:$A,$A2),"B","A")),ROW(INDIRECT(A2)),0)))

　次に、Sheet2のB2セルをコピーして、Sheet2のB2の右方向に位置するセルに貼り付けて下さい。
　次に、Sheet2の2行目全体をコピーして、3行目以下に貼り付けて下さい。

　すると、Sheet2に重複している数値と、その数値が入力されている位置が表示されます。

【条件付き書式を利用する方法（Excel2007よりも前のバージョンの場合）】
A1セルををクリック
　　↓
メニューの[書式]をクリック
　　↓
現れた選択肢の中にある[条件付き書式]をクリック
　　↓
現れた「条件付き書式の設定」ウィンドウの左端の欄をクリック
　　↓
現れた選択肢の中にある「数式が」をクリック
　　↓
左から3番目にある欄の欄に次の数式を入力

=COUNTIF($A:$B,A1)>1

　　↓
「条件付き書式の設定」ウィンドウの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ウィンドウの[パターン]タグをクリック
　　↓
好きな色の四角形をクリック
　　↓
「セルの書式設定」ウィンドウの[OK]ボタンをクリック
　　↓
「条件付き書式の設定」ウィンドウの[OK]ボタンをクリック
　　↓
A1セルにカーソルを合わせて、マウスを右クリック
　　↓
現れた選択肢の中にある[コピー]をクリック
　　↓
A・B列の条件付き書式を設定したいセル範囲を、範囲選択
　　↓
選択範囲を示す黒い太枠の内側にカーソルを合わせて、マウスを右クリック
　　↓
現れた選択肢の中にある[形式を選択して貼り付け]をクリック
　　↓
現れた「形式を選択して貼り付け」ウィンドウの中にある「書式」と記されている箇所をクリックして、チェックを入れる
　　↓
「形式を選択して貼り付け」ウィンドウのの[OK]ボタンをクリック

【条件付き書式を利用する方法（Excel2007以降のバージョンの場合）】
A1セルををクリック
　　↓
[ホーム]タグをクリック
　　↓
「スタイル」タグの中にある[条件付き書式] ボタンをクリック
　　↓
現れた選択肢の中にある[新しいルール] をクリック
　　↓
現れた「新しい書式ルール」ウィンドウの[数式を使用して、書式設定するセルを決定] をクリック
　　↓
左から3番目にある欄の欄に次の数式を入力

=COUNTIF($A:$B,A1)>1

　　↓
「新しい書式ルール」ウィンドウの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ウィンドウの[塗りつぶし] タグをクリック
　　↓
好きな色の四角形をクリック
　　↓
「セルの書式設定」ウィンドウの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ウィンドウの[OK]ボタンをクリック
　　↓
選択しているセルを変えずに、再度[条件付き書式] をクリック
　　↓
現れた選択肢の中にある[ルールの管理] をクリック
　　↓
現れた「条件付き書式ルールの管理」ウィンドウの「書式ルールの表示」欄が[現在の選択範囲]となっていることを確認
　　↓
「ルール(表示順で適用)」欄が「数式: =COUNTIF($A:$B,A1)>1」となっている行の「適用先」欄の内容を

=$A$1

から

=$A$1:$B$65535

に変更する（数値が入力されている可能性のあるセル範囲の全て）
　　↓
「条件付き書式ルールの管理」ウィンドウの[OK]ボタンをクリック

　以上です。

お礼

ご丁寧にありがとうございました。

回答No.7

mitarashi

別件で連想配列づいているもので．．．
重複している値のセルに色を付けます。
重複数が少なければ、数秒で終了します。（PentiumM 1.33GHz）
dictionaryは最初とっつき難いのを経験しておりますが、情報はWeb上に結構ありますので、必要ならお調べ下さい。
他のプログラミング言語では当たり前に使っていたりします。
Sub test()
Dim myDic As Object
Dim buf As Variant
Dim i As Long, j As Long
Dim myKey As String

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'試験データ作成
' With Range("A1:B65000")
' .Clear
' .Formula = "=int(rand()*10000000)+1"
' .Value = .Value
' End With
Set myDic = CreateObject("Scripting.Dictionary")
buf = Range("A1:B65000")
For i = 1 To UBound(buf, 1)
For j = 1 To UBound(buf, 2)
myKey = CStr(buf(i, j))
If myDic.exists(myKey) Then
Set myDic.Item(myKey) = Union(myDic.Item(myKey), Cells(i, j))
myDic.Item(myKey).Interior.ColorIndex = 4
Else
myDic.Add myKey, Cells(i, j)
End If
Next j
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

お礼

ご丁寧にありがとうございました。

回答No.6

imogasi

COUNTIF は複数列対象で数えられる
例
=COUNTIF(A2:C7,A2)
ーー
だからあわせて、１３万行ぐらいなら（質問に合計行数ぐらい書くこと。それと第２列以後のデータのあり場所＝列など）
データを隣接２列とかにくっつけられるなら（隣列に列挿入し、そこへコピー貼り付け）、COUNTIFで重複判定できるのでは。
結果が２列（各列対応）になってしまうが。
そういうことでは、都合が悪い・出来ない理由など質問に丁寧に書くべきではと思う。
思うよう（内容不明だが）にやりたいなら、VBAを使わないと出来ないのでは。
＞重複しているものを見つけたいのですが
これも抽象的。見つけてどうしたいのか？件数カウントだけなら、まだ簡単だが。

お礼

説明不足だったようですみません。
ありがとうございました。

回答No.4

tom04

こんばんは！
VBAでの一例です。
Sheet1のA・B列の１行目からデータがあるとします。
重複するデータをSheet2のA列に表示するようにしてみました。

画面左下のSheet1のSheet見出し上で右クリック　→　コードの表示　→　VBE画面が出ますので
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Dim i, j As Long
Dim ws1, ws2 As Worksheet
Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
For j = 1 To 2
If WorksheetFunction.CountIf(Range(ws1.Columns(1), ws1.Columns(2)), ws1.Cells(i, j)) > 1 Then
ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1) = ws1.Cells(i, 1)
End If
Next j
Next i
For i = ws2.Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If WorksheetFunction.CountIf(ws2.Columns(1), ws2.Cells(i, 1)) > 1 Then
ws2.Cells(i, 1).Delete (xlUp)
End If
Next i
End Sub 'この行まで

※　データが65000行以上あるというコトなので、少々時間がかかるかもしれません。

他に良い方法があればごめんなさいね。m(_ _)m

お礼

ご丁寧にありがとうございました。

回答No.3

oct1290

エクセルで重複検索はいくつも方法があります
検索後削除するなら
A.B列が別のデーターで切り分け出来るなら別シートに転写し先の関数で出来ますよね
同一行のデーターなら一旦CSVにしアクセスに取り込み処理をされるのが簡単だと思います（アクセスがあれば）
検索結果のみを知りたい状況であるなら条件付書式でも検索（では無いかも）できるけど？

しかしまあよく６５０００も入れましたね、元データーがあるのならそれを先に処理されてはいかがですか。

お礼

ありがとうございました。

回答No.2

mshr1962

条件付き書式で塗りつぶすなら
A:B列全体を選択して「書式」「条件付き書式」で
「数式が」「=COUNTIF($A:$B,A1)」で「書式」でパターンの色を設定

お礼

ありがとうございました。

回答No.1

nerimaok

excelの話かな?
2007とか2010にすれば行数もっと増えますけど。