Excel 重複判断しつつ並べ替えする関数

Question

お世話になっております。

添付の画像を見て頂きたいのですが、

１．添付した画像のような感じで重複、非重複が分かるようにA-Z順に並べる関数と、
２．その非重複の全リストの一覧にする関数

はどのように関数を書けばいいでしょうか。
お知恵お貸し下さいm(_ _)m

tom04 · Accepted Answer

No.4・5です。
補足の件について・・・

＞これをリストが5つ（E列まで）、10個（J列まで）などにする場合、
＞コードのどこをどう書き換えれば良いのでしょうか。

に関しては、「全項目」が画面から見えなくなるといけないので余計なお世話かもしれませんが、
↓の画像のように「全項目」列をA列とし、「商品リスト」はB列以降にあり
1行目項目が入っている最終列まで対応するようにしてみました。
（1行目項目データがある最終列までです）

次に
＞また、今回は1つの商品リストに十数個程度のデータしか入っていませんでした、
＞これがそれぞれの商品リストに数千入っていても、
＞このまま動作すると思って大丈夫でしょうか。

の件につきまして、おそらく大丈夫のはずですが結構時間がかかるかもしれません。

今一度コードを載せてみますので、マクロを試してみてください。

Sub test2() 'この行から
    Dim i, j, k, M As Long
    Application.ScreenUpdating = False
    M = Cells(1, Columns.Count).End(xlToLeft).Column    '最終列の取得
    For j = 2 To M  'B列～最終列まで
    For k = 2 To Cells(Rows.Count, j).End(xlUp).Row '2行目からｊ列最終行まで
    If WorksheetFunction.CountIf(Columns(1), Cells(k, j)) = 0 Then
        Cells(Rows.Count, 1).End(xlUp).Offset(1) = Cells(k, j)
    End If
    Next k
    Next j
    For j = 1 To M  'A列～最終列まで
    k = Cells(Rows.Count, j).End(xlUp).Row 'J列の最終行取得
    Range(Cells(2, j), Cells(k, j)).Sort key1:=Cells(1, j), order1:=xlAscending
    Next j
    For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
    For j = 2 To M  'B列から最終列まで
    For k = Cells(Rows.Count, j).End(xlUp).Row To 2 Step -1
    If Cells(k, j) = Cells(i, 1) Then
        Cells(k, j).Cut Destination:=Cells(i, j)
    End If
    Next k
    Next j
    Next i
    Application.ScreenUpdating = True
End Sub 'この行まで

※　ご希望通りに動きになれば良いのですが・・・m(_ _)m

aokii · Answer

まずはじめに、各列のデータを一つの列にコピペし、ピボットテーブルを使って全リストの一覧を作り、ＶＬＯＯＫＵＰ関数で存在する場合のみデータを入れるようにしてはいかがでしょう。

don9don9 · Answer

ExcelのバージョンがわからないのでExcel2003での方法を書きます。

元のシート名をSheet1とします。

別シートを追加して（Sheet2とする）、商品リスト1～4の内容を全てA列に貼り付ける。
A1に項目名を入れ、実際のデータは2行目以降に入れる。

Sheet2のA列をデータ　＞　並べ替えで昇順に並べ替える。

Sheet1のK1セルを選択状態にする。

データ　＞　フィルタ　＞　フィルタオプションの設定で「指定した範囲」を選択
リスト範囲を　Sheet2!$A:$A
抽出範囲を　$K$1
とし、「重複するレコードは無視する」にチェックを入れてOKを押す。

これで重複を除いた昇順のリストがK列にできます。

その後はG2セルに

=IF(COUNTIF(A:A,$K2),$K2,"")

という式を入れて、これをJ16セルまで複写すればいいです。

KURUMITO · Answer

お示しのデータがシート1に有るとしてシート2には作業用のシートとして次のようにします。
Ｂ1セルからＥ1セルには商品リスト1から商品リスト4まで項目名を入力します。
Ｂ2セルには次の式を入力して右横方向にドラッグコピーします。

=IF(B1="","",COUNTA(Sheet1!A:A)-1)

Ａ3セルには0を入力した後にＢ3セルには次の式を入力して右横方向にドラッグコピーします。

=IF(B2="","",SUM($B$2:B2))

Ａ4セルには商品リストと文字を入力してからＡ5セルには次の式を入力して下方にドラッグコピーします。

=IFERROR(TRIM(INDEX(Sheet1!$A:$D,ROW(A1)-INDEX($3:$3,MATCH(ROW(A1)-0.5,$3:$3,1))+1,MATCH(ROW(A1)-0.5,$3:$3,1))),"")

その後にＡ5セルから下方のデータが表示されているセルまでを選択して「コピー」したのちにＧ1セルを選択してから「形式を選択して貼り付け」で「値」にチェックをして貼り付けます。
Ｇ1セルから下方のデータが表示されているセルまでの範囲を選択してから「ホーム」タブの「並べ替えとフィルター」から「昇順」を選択して「現在選択されている範囲を並べ替える」にチェックをして「並べ替え」をクリックします。
その後にＨ1セルには1と入力したのちに、Ｈ2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNTIF(G$1:G2,G2)=1,MAX(H$1:H1)+1,"")

シート1に戻ってＫ1セルには全項目と入力したのちにＫ2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNTIF(Sheet2!H:H,ROW(A1))=0,"",INDEX(Sheet2!G:G,MATCH(ROW(A1),Sheet2!H:H,0)))

Ｇ1セルからＪ1セルには商品リスト1から4を並べます。

Ｇ2セルには次の式を入力したのちにＪ2セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(COUNTIF(A:A,$K2)>0,$K2,"")

tom04 · Answer

こんにちは！
VBAになってしまいますが・・・
一例です。

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

Sub test()  'この行から
    Dim i, j, k As Long
        Application.ScreenUpdating = False
        k = Cells(Rows.Count, 5).End(xlUp).Row
        If k > 1 Then
            Range(Cells(2, 5), Cells(k, 5)).ClearContents
        End If
        For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        For j = 1 To 4
        If WorksheetFunction.CountIf(Columns(5), Cells(i, j)) = 0 Then
            Cells(Rows.Count, 5).End(xlUp).Offset(1) = Cells(i, j)
        End If
        Next j
        Next i
        k = Cells(Rows.Count, 5).End(xlUp).Row
        Range(Cells(2, 5), Cells(k, 5)).Sort key1:=Cells(1, 5), order1:=xlAscending
        For j = 1 To 4
        For i = Cells(Rows.Count, 5).End(xlUp).Row To 2 Step -1
        For k = 2 To Cells(Rows.Count, j).End(xlUp).Row
        If Cells(k, j) = Cells(i, 5) Then
            Cells(k, j).Cut Destination:=Cells(i, j)
        End If
        Next k
        Next i
        Next j
        Application.ScreenUpdating = True
End Sub 'この行まで

※　一旦マクロを実行すると、元に戻せませんので別Sheetでマクロを試してみてください。

関数でないので、ご希望の方法でなかったらごめんなさいね。m(__)m

tom04 · Answer

No.4です！
たびたびごめんなさい。
前回のコードではA～D列データが昇順に並んでいないとちゃんと表示されません。
今度はA～D列データがどんな並びでも対応できるようにしてみました。

前回のコードは削除して↓のコードに変更してください。

Sub test2() 'この行から
    Dim i, j, k As Long
    Application.ScreenUpdating = False
    For j = 1 To 4
    k = Cells(Rows.Count, j).End(xlUp).Row
    Range(Cells(2, j), Cells(k, j)).Sort key1:=Cells(1, j), order1:=xlAscending
    Next j
    For j = 1 To 4
    For i = 2 To Cells(Rows.Count, j).End(xlUp).Row
    If WorksheetFunction.CountIf(Columns(5), Cells(i, j)) = 0 Then
        Cells(Rows.Count, 5).End(xlUp).Offset(1) = Cells(i, j)
    End If
    Next i
    Next j
    k = Cells(Rows.Count, 5).End(xlUp).Row
    Range(Cells(2, 5), Cells(k, 5)).Sort key1:=Cells(1, 5), order1:=xlAscending
    For k = Cells(Rows.Count, 5).End(xlUp).Row To 2 Step -1
    For j = 1 To 4
    For i = Cells(Rows.Count, j).End(xlUp).Row To 2 Step -1
    If Cells(i, j) = Cells(k, 5) Then
        Cells(i, j).Cut Destination:=Cells(k, j)
    End If
    Next i
    Next j
    Next k
    Application.ScreenUpdating = True
End Sub 'この行まで

※　操作方法は前回同様です。
何度も失礼しました。m(_ _)m

don9don9 · Answer

No.2です。
横から失礼します。

No.5さんのコードは、リストが4列で5列目に全アイテムの出力を行うように作られていますので、実際のリストの数がもっと多いのであれば

>For j = 1 To 4
→　For j = 1 to 10

のように、固定値で4と記述している箇所をリストの数

>Cells(Rows.Count, 5)
→　Cells(Rows.Count, 11)

など、固定値で5と記述している箇所をリストの数+1にするだけだと思います。

余談です。
実際のリストが4つではなく、かつ1つのリストに含まれる商品の数が数千あるというなら、そういう情報はできれば質問文に書いておいてほしかったです。
私がNo.2に書いたような方法は、Excel2003では65536行までしかないため、例えばリストが20個でリスト1つ当たり5000の商品があるような場合だと使えないんですよね。

Excel 重複判断しつつ並べ替えする関数

まずはじめに、各列のデータを一つの列にコピペし、ピボットテーブルを使って全リストの一覧を作り、ＶＬＯＯＫＵＰ関数で存在する場合のみデータを入れるようにしてはいかがでしょう。

この回答への補足

ExcelのバージョンがわからないのでExcel2003での方法を書きます。

お示しのデータがシート1に有るとしてシート2には作業用のシートとして次のようにします。

こんにちは！

No.4です！

この回答への補足

No.2です。

No.4・5です。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング