[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『重複データの検索方法を教えていただきたいです』（ゆき夫）
下記の図で重複データのセルの結果を出したいのです。 A列:商品コード(数字のみ) B列:本支店区分(本店=100) C,E,G,I,K列:商品名(Null)あり D,F,H,J列：他のデータ(今回の処理には使いません)
・重複を見る範囲は各商品CD内(A列が同じ)でB列=100以外の行です ・各行内における商品の重複は対象外です
[A] [B] [C] [D] [E] [F] [G] [H] [I] [1] 商品CD 本支店 商品1 他項目 商品2 他項目 商品3 他項目 結果 [2] 123 100 ○ [3] 123 10 △ × ○ [4] 123 35 × 0 [5] 223 100 △ ○ [6] 223 22 ○ △ [7] 223 43 × [8] 223 6 △ × 0 [9] 323 100 ○ × △ [10] 323 32 △ ○ [11] 323 2 △ 0 [12] 423 100 × ○ [13] 423 23 △ × [14] 423 56 × 0 [15] 423 8 × 0
言葉にするとこのような処理がしたいです。 A列が同じコード内でB列が100以外の行同士の商品1と商品2と商品3をみます。 2行目はB列が100なので対象外です。 3行目の商品名を3つdictionaryに代入。 4行目は商品1が既に3行目にあるので結果に0を入力。 5行目はB列が100なのでdictionaryをクリア 6行目の商品名を2つdictionaryに代入。 7行目は6行目には無い商品名なのでdictionaryに代入。 8行目は既にdictionaryに入っている商品名なので結果に0を入力
というような処理をしたいのです。 同じ行内で重複データがあってもそれは対象外で、異なる行同士での重複チェックです。
お力を貸してください。どうぞよろしくお願いいたします。
>C,E,G,I,K列:商品名(Null)あり
と、表とはちゃうみたいですけど（結果表示のI列は商品名が入っているはず）。
まぁ、とりあえず表のほうが正しいとして。
（弥太郎）
'-----------------------------
Sub Yukio()
Dim dic As Object, i As Long, n As Integer, tbl, x, y
Set dic = CreateObject("scripting.dictionary")
tbl = Range("a2").Resize(Range("a" & Rows.Count).End(xlUp).Row - 1, 7)
ReDim x(1 To UBound(tbl, 1), 1 To 1)
For i = 2 To UBound(tbl, 1)
If tbl(i, 2) <> 100 Then
If Not dic.exists(tbl(i, 1)) Then
dic(tbl(i, 1)) = Array(tbl(i, 3), tbl(i, 5), tbl(i, 7))
Else
y = dic(tbl(i, 1))
For n = 3 To 7 Step 2
If tbl(i, n) <> "" Then
If Not IsError(Application.Match(tbl(i, n), y, 0)) And IsEmpty(x(i, 1)) Then
x(i, 1) = 0
Else
ReDim Preserve y(UBound(y) + 1)
y(UBound(y)) = tbl(i, n)
dic(tbl(i, 1)) = y
End If
End If
Next n
End If
End If
Next i
Range("i2").Resize(UBound(tbl, 1)) = x
End Sub
数式を使うなら、J列を作業列にして J2に =IF(B2=100,"",J1&C2&E2&G2) I2に =IF(B2=100,"",IF(ISERROR(FIND(C2,J1))+ISERROR(FIND(E2,J1))+ISERROR(FIND(G2,J1)),"",0))
J列は100が出てきたらこれまでの商品名をリセットして、& で結合しながら表示。 I列は、一つ上の行までに 商品名が有ったかどうかを確認しています。
同じ様な（？）考え方でマクロを作ると。。。
'------
Sub 商品CD_商品を登録()
Dim dic As Object, i As Long, ii As Long
Dim tbl, x
Set dic = CreateObject("scripting.dictionary")
tbl = Range("A1").Resize(Range("A" & Rows.Count).End(xlUp).Row, 9).Value
ReDim x(1 To UBound(tbl, 1), 1 To 1)
x(1, 1) = tbl(1, 9)
For i = 2 To UBound(tbl, 1)
If tbl(i, 2) <> 100 Then
For ii = 3 To UBound(tbl, 2) - 1 Step 2
If tbl(i, ii) <> "" Then
If dic.exists(tbl(i, 1) & "_" & tbl(i, ii)) Then
x(i, 1) = 0
Exit For
End If
End If
Next
For ii = 3 To UBound(tbl, 2) - 1 Step 2
If tbl(i, ii) <> "" Then
dic(tbl(i, 1) & "_" & tbl(i, ii)) = Empty
End If
Next
End If
Next
Range("I1").Resize(UBound(tbl, 1)).Value = x
Set dic = Nothing
End Sub
'------
＞For ii = 3 To UBound(tbl, 2) - 1 Step 2 が二回出ていますが、 上側がI列に入れた式の様な役割・・・・過去に出てきているか確認 下側がJ列に入れた式の様な役割・・・・次の行の為に過去に出てきた物を登録 しています。
(HANA)
＞弥太郎さん どうもありがとうございました！ 関数の入れ子（VBAでも『入れ子』というのでしょうか）がまだ習得できていないので とても助かりました。辞書を見ながら解読してみます。
＞HANAさん 以前にもお世話になりましたが、違う方向からの見方がある事を気づかされます。 数式もバッチリ結果が出ました。本当に助かります。ありがとうございました！
教えてください。以前、教えていただいた関数を使用してみました。
J2に =IF(B2=100,"",J1&C2&E2&G2) I2に =IF(B2=100,"",IF(ISERROR(FIND(C2,J1))+ISERROR(FIND(E2,J1))+ISERROR(FIND(G2,J1)),"",0))
この方法で試したみたところ、一番上の表でセルC15は重複データなのですが、E15に○が入った場合には 重複データと認識しなくなってしまうのです。 自分の予想は『整数+#VALUE!』という式になり、答えがエラー値になってしまうため 重複データではないという結果になってしまうからなのかなと思うのです。 これを回避する方法は無いかと思い質問いたしました。
VBAで試しましたが、変数や配列の関数(?)が完全には理解できず、 会社で使用している表に合わせることができませんでした。
どうぞよろしくお願いいたします。 （ゆき夫）
＞自分の予想は『整数+#VALUE!』という式になり、答えがエラー値になってしまうため ＞重複データではないという結果になってしまうからなのかなと思うのです。
確かにそうですね。 実際は、ISERROR関数に入っているので、FALSE + TRUE + FALSE = 1 になってしまうため、重複データではないという結果になります。
現在の方法では、FALSE + FALSE + FALSE = 0 になった時だけ ・・・３つのセルの値をそれぞれ J列の中で探した時に すべてのセルで値が見つかった場合・・・ 重複データと言う事に成っています。
少し変更して、 J2に =IF(B2=100,"★",J1&C2&E2&G2) I2に =IF(B2=100,"",IF(ISERROR(1/(FIND(C2,J1)-1))*ISERROR(1/(FIND(E2,J1)-1))*ISERROR(1/(FIND(G2,J1)-1)),"",0)) としてみるとどうでしょう？
J2用の式の「★」は、C,E,G列に使われない文字にしてもらえれば何でも良いです。
最初の式では、検索するセルに入力が無かった場合も、検索値が最初にあった場合も FIND関数で調べるとどちらも１に成っていたのを 未入力なのか、検索値があったのか、検索値がなかったのか ３つに分けることにしてみました。
一つだけ取り出すと、数式内の
__________1
ISERROR(1/(FIND(C2,J1)-1)) の部分です。
~~~~~~~~~~~~~2
^^^^^^^^^^^^^^^^3
FIND(C2,J1) ・・・・・・・・・・・・・・・・・・・・★１★ C2セルに入力が無い場合 １ C2セルの値がJ1にある場合 ２以上 C2セルの値がJ1にない場合 #VALUE!
★１★−１・・・・・・・・・・・・・・・・・・・・・★２★ C2セルに入力が無い場合 １−１ ＝0 C2セルの値がJ1にある場合 ２以上−１ ＝1以上 C2セルの値がJ1にない場合 #VALUE!−１＝#VALUE
１／★２★・・・・・・・・・・・・・・・・・・・・・★３★ C2セルに入力が無い場合 １／０ ＝#DIV/0! C2セルの値がJ1にある場合 １／１以上 ＝数値 C2セルの値がJ1にない場合 １／#VALUE!＝#VALUE
ISERROR(★３★） C2セルに入力が無い場合 ISERROR(#DIV/0!)＝TRUE C2セルの値がJ1にある場合 ISERROR(数値) ＝FALSE C2セルの値がJ1にない場合 ISERROR(#VALUE!)＝TRUE
になるので、３つのセルのうちどこかに一つでもFALSEがあったら 「重複がある」と判断出来る様になりそうです。
(HANA)
HANAさんどうもありがとうございました！ おかげさまで希望どおりの結果が出ました。
わかりやすい説明付きで本当に助かりました。 遅い時間にもかかわらずありがとうございました。 （ゆき夫）
コードは、↑で載せた物から考えると データがA1セルから入っている事にかわりが無い場合
x(1, 1) = tbl(1, 9) 結果を書き出す列がI列 ＝ 9列目
If tbl(i, 2) <> 100 Then 本支店コードが入っているのがB列 = 2列目
For ii = 3 To UBound(tbl, 2) - 1 Step 2
商品が入っているのが C列 ＝ 3列目から
Step 2 ＝ 2列おき
UBound(tbl, 2) - 1 の部分は 直接値を入れると
今回の場合 H列 ＝ 8列目（或いは G列 ＝ 7列目）
C列から2列セットで3つあるので、H列までと考える。
ただ、実際にはH列は確認しないので、G列にしても良い。
Range("I1").Resize(UBound(tbl, 1)).Value = x
結果を書き出す先頭セルが I1 セル
ループ処理は２箇所に有るので、列の移動が有っただけなら ５行の変更になると思います。
どの位置に有った物が何処へ行ったのか 置き換えてもらうと動くかもしれません。
また、こちらのコードの改造なので [[20100327135924]] 『同じ商品番号同士の金額を比較するVBA』（ゆき夫） ↑で載せた物も、基本的には同じです。 両方のコードを見比べて、どの列がどの列に移動した時に 何処がどう変わっているか確認してもらって 同じように変更してみてもらうと良いかもしれません。
(HANA)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.