エクセルマクロで重複数値と以外の数値を抽出する

質問

数値の表を作成しておりますが、
重複数値で困っております。マクロで教えてください。

A列11～20まで1.2.2.4.5.6.6.8.9.10とあるデータに
2と6が重複しています
重複数値2.6をＢ列2行目以降に、Ｃ列２行目以降にはそれ以外の1.4.5.8.9.10
を抽出したいのですが、マクロで教えてください。
（offsetを使ってできますか？）

また、重複数値を抽出する自作関数ってできるでしょうか？
よろしくお願いいたします。

回答No.2

tom04 · ベストアンサー

こんばんは！
既存のワークシート関数でやってみました。

↓の画像のように作業用の列を設けています。
作業列E2セルに
=IF(AND(COUNTIF(A$11:A$20,A11)>1,COUNTIF(A$11:A11,A11)=1),ROW(A1),"")
という数式を入れ、元データの行数分（10行）下へコピー！

そしてB2セルに
=IFERROR(INDEX(A$11:A$20,SMALL(E$2:E$11,ROW(A1))),"")
C2セルに
=IFERROR(INDEX(A$11:A$20,SMALL(IF(COUNTIF(A$11:A$20,A$11:A$20)=1,ROW(A$1:A$10)),ROW(A1))),"")
※　C2セルは配列数式になりますので、Ctrl＋Shift＋Enterで確定！
この画面からコピー＆ペーストする場合、
上記数式をドラッグ＆コピー　→　C2セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl＋Shiftキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。

最後にB2・C2セルを選択　→　C2セルのフィルハンドルで下へコピー！
これで画像のような感じになります。

※　作業列の「2」と「6」はデータそのものではなく、
INDEX関数で範囲指定した行番号です。

尚、質問のタイトルが「マクロで！」というコトですので、ついでにVBAでの一例です。
シートモジュールにしてみてください。（当然のことながら作業列は不要です）

Sub Sample1()
Dim i As Long, c As Range
Range("B2:C11").ClearContents
For i = 11 To 20 '←A列11行目～20行目まで
If WorksheetFunction.CountIf(Range("A11:A20"), Cells(i, "A")) > 1 Then
Set c = Range("B:B").Find(what:=Cells(i, "A"), LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
Cells(Rows.Count, "B").End(xlUp).Offset(1) = Cells(i, "A")
End If
Else
Cells(Rows.Count, "C").End(xlUp).Offset(1) = Cells(i, "A")
End If
Next i
End Sub

こんな感じではどうでしょうか？m(_ _)m

お礼

tom04様
ご丁寧にご説明いただきましてありがとうございました。
感激。目からウロコでした。助かりました。
丁寧な解説で、だんだん解読できるようになりました。
まだまだ学習中ですので、またご質問させていただきますが、
よろしくお願いします。

回答No.1

bin-chan

Microsoft Queryを追加インストールしてSQL投げる。
「外部データの取込」とあるが、EXCELへも接続できる

SelectでCOUNTとって、HAVINGで識別
重複アリは＞1だし、重複ナシは=１を捨えばＯＫ
これをVBAで記述

参考URL：

http://office.microsoft.com/ja-jp/excel-help/HA010099664.aspx

お礼

ご回答ありがとうございました。このような機能があることを初めて知りました勉強になります。
と同時にExcelの万能さに感動しております。まだまだ学習不足なので、またご質問させていただきますが、
よろしくお願いいたします。