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

質問

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

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

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

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

回答No.7

tom04 · ベストアンサー

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

お礼

すごい・・・ほんと完璧です！

コードを書き換えなくても自動でリスト数を取得するようにまでなっていて大感激です！
なんて御礼を申し上げたら良いのか・・・。
おかげさまで仕事の効率がだいぶ改善されました。ほんと嬉しいです！！
世の中にはこんなことができる方がいらっしゃるのですね。

重ね重ねありがとうございましたm(_ _)m

回答No.6

don9don9

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に行の限界があるとは夢にも思っていなくて思い至りませんでした。

固定値に＋1するというのは勉強になりました。
このたびはお知恵をお貸し頂きありがとうございましたm(_ _)m

回答No.5

tom04

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

補足

うゎ、動いた！すごいっ！ありがとうございます！

コードでお伺いしたい点があるのですが、
今回【商品リスト】が4つ（D列）までだったわけですが、
これをリストが5つ（E列まで）、10個（J列まで）などにする場合、
コードのどこをどう書き換えれば良いのでしょうか。

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

以上2つお聞かせ頂ければ幸いです。
なんにせよ重ね重ねありがとうございました。＼(*T▽T*)／

回答No.4

tom04

こんにちは！
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

回答No.3

KURUMITO

お示しのデータがシート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,"")

お礼

Excelの関数を学ぼうと、本を買ってきました。
少しでも関数を使えるよう勉強していく所存です。
このたびはアドバイス頂きありがとうございましたm(_ _)m

回答No.2

don9don9

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セルまで複写すればいいです。

回答No.1

aokii

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

補足

アドバイス頂きありがとうございます。

ピボットテーブル、ＶＬＯＯＫＵＰ関数、ネットで調べてみました。
便利そうだなということは分かったのですが、
具体的にどうこの事例に当てはめれば
画像の通りの結果が得られるのか分かりませんでした。
すみません・・・