別ブックとのデータ照合

A_BooK.xlsmのデータ　「C、E、F列」のデータと
B_Book.xlsxのデータ（台帳）　「B、F、H列」のデータが一致し、
かつ、A_BooKの「G列」の数量がB_Book.xlsxの「L列」の数量以内の場合、K列の行に"OK"又は"NG"
A_BooｋのB、F列のデータは一致するがH列のデータがない場合は不一致の行に赤に色付けするマクロは組めるのでしょうか？

ご教授のほどよろしくお願いいたします。

質問者からの補足コメント

• ありがとうございます。
  以下、補足です。
  
  >> K列の行に"OK"又は"NG"
  >どういうときにOKでどういうときにNGですか？
  AのデータとBのデータが一致した時（C,E,Fの3つの揃ったデータがB_bookに有るか無いか）
  
  >> H列のデータがない場合
  >どのブックのH列ですか？
  間違えました。
  A_bookの「F」のデータです。
  
  >不一致の行とはどのブックのどの行ですか？
  >色づけするのは行全体で良いですか？
  不一致の警告という意味＝Aに
  
  言葉足らずで申し訳ございません。
  いつもはBの台帳から「F」でコードを入力しソート　→　「B」で更に絞り込み「F」が有るか無いかをAブックのデータをBブックから　手作業で探してます。
  マクロで出来れば１つ1つを手動でチェックするのでは無く、一括で照合出来ればと
  思った次第です。（AのデータがBにあるか？、その３つ揃ったデータの数はある？）

    補足日時：2021/03/03 16:29

• 質問に補足いたしました。
  よろしくお願いいたします。

  No.1の回答に寄せられた補足コメントです。 補足日時：2021/03/04 11:00

• ありがとうございます。以下にご回答させて頂きます。
  
  >１．比較対象項目については以下とする。
  その通りです。
  B_Book.xlsx「F,B,H」列のデータがA_Book.xlsm「C,E,F」列のデータに有無をチェック。
  
  >２．上記①②③が一致した場合
  
  もう一度検討、整理した結果、以下の通りです。
  
  ①、②、③が一致し
  （１）A_Book.xlsmのG列≦B_Book.xlsxのL列の場合「OK」
  →「OK」表示セルはG列以降に。
  
  （１）の場合以外・・・
  
  ②③が一致しない場合（B_BooK.xlsxに無い場合）
  ①②③は一致したのにL列の数が足りない場合（L列は残数値です）
  →A_Book.xlsmの当該行の背景色を赤にを設定
  
  上記の通りです。
  ご理解頂けますでしょうか？
  
  お手数おかけいたします。よろしくお願いいたします。

  No.2の回答に寄せられた補足コメントです。 補足日時：2021/03/04 19:04

• 早々のご回答ありがとうございます。
  
  >G列以降の意味が不明です
  G列以外、H,I･･･列を追加するかもしれないので「K」列でお願いします。
  
  >（１）以外の場合、Case1又はCase2なら
  >A_Book.xlsmの当該行の背景色を赤にを設定であってますか。
  あっております。
  
  >列を指定してください。
  「K」列でお願いします。
  
  >Case1,Case2の何れにも該当しない場合は、なにもしなくて良いですか。
  はい。
  A_Bookのデータ（行）に無いものはそのままです。
  
  例えば投稿画像の中でA_Bookの項番25のC列「****」C列「****」F列「2」台がB_Bookの中にあるか？
  ※B_Bookのフィルタ順位としてはF→B→L→Hとなります。
  F列の「２」台がなければNG（背景色赤）
  一致していればOK、というイメージでございます。
  
  よろしくお願いいたします。

  No.3の回答に寄せられた補足コメントです。 補足日時：2021/03/04 21:03

• ご丁寧に何度もありがとうございます。
  
  ①は一致しても②③が同時に一致しなければNG。（①の一致は前提です）
  A_Book.xlsmのG列（数）がB_Book.xlsxのL列（数）より多い場合もNG（背景色赤）
  CaseBという事になると思います。
  
  >A_Book.xlsmのE列の値が、B_Book.xlsxのB列にない・・・判定①
  >A_Book.xlsmのF列の値が、B_Book.xlsxのH列にない・・・判定②
  >CaseBに従えば、判定①、判定②のどちらかが成立すれば、赤色背景
  >ということになりますが、よろしいでしょうか。
  その通りです。

  No.4の回答に寄せられた補足コメントです。 補足日時：2021/03/04 23:09

• >A_Book.xlsmのシート名はなんでしょうか。（Sheet1で良いですか）
  >B_Book.xlsxのシート名はなんでしょうか。（Sheet1で良いですか）
  sheet1で良いです。
  
  >B_Book.xlsxは既にオープンされている前提で良いですか。
  良いです。
  
  遅くまでありがとうございました。

  No.5の回答に寄せられた補足コメントです。 補足日時：2021/03/04 23:25

No.6 ベストアンサー 回答者： tatsumaru77 回答日時： 2021/03/05 08:46

以下のマクロを標準モジュールに登録してください。

Option Explicit
Public Sub データ照合()
Dim dicT1 As Object '３列一致判定用
Dim dicT2 As Object 'C列とF列の一致判定用
Dim shA As Worksheet
Dim shB As Worksheet
Dim maxrowA As Long
Dim maxrowB As Long
Dim wrow As Long
Dim row1 As Long
Dim key1 As Variant
Dim key2 As Variant
Set dicT1 = CreateObject("Scripting.Dictionary") ' 連想配列の定義
Set dicT2 = CreateObject("Scripting.Dictionary") ' 連想配列の定義
Set shA = Workbooks("A_book.xlsm").Worksheets("Sheet1")
Set shB = Workbooks("B_book.xlsx").Worksheets("Sheet1")
maxrowA = shA.Cells(Rows.Count, 1).End(xlUp).Row 'A列最終行を求める
maxrowB = shB.Cells(Rows.Count, 1).End(xlUp).Row 'A列最終行を求める
'B_bookの項目を登録
For wrow = 2 To maxrowB
key1 = shB.Cells(wrow, "F").Value & "|" & shB.Cells(wrow, "B").Value & "|" & shB.Cells(wrow, "H").Value
key2 = shB.Cells(wrow, "F").Value
dicT1(key1) = wrow
dicT2(key2) = wrow
Next
'A_BookのK列クリア及び背景色クリア
shA.Range("K2:K" & maxrowA).Value = ""
shA.Range("A2:K" & maxrowA).Interior.Pattern = xlNone
'A_Bookを処理する
For wrow = 2 To maxrowA
key1 = shA.Cells(wrow, "C").Value & "|" & shA.Cells(wrow, "E").Value & "|" & shA.Cells(wrow, "F").Value
key2 = shA.Cells(wrow, "C").Value
If dicT1.exists(key1) = True Then
row1 = dicT1(key1) '３列一致
If shA.Cells(wrow, "G").Value <= shB.Cells(row1, "L").Value Then 'Gの値＜＝Lの値
shA.Cells(wrow, "K").Value = "OK" 'OK設定
Else
shA.Range("A" & wrow & ":K" & wrow).Interior.Color = 255 '背景赤色
End If
Else
If dicT2.exists(key2) = True Then 'C列とF列が一致
shA.Range("A" & wrow & ":K" & wrow).Interior.Color = 255 '背景赤色
End If
End If
Next
MsgBox ("完了")
End Sub

この回答へのお礼

ご丁寧に何度もありがとうございました。
なんでここまでしてくれるのかと・・・・m(__)m

で、実際に作動検証はその前のマクロ作動の関係でA_Bookの照合セルとかが変わってしまい調整が必要となりました。
他の業務の関係で今週は時間がないのですがこれが実行できればかなりの稼働削減となり、大変助かります。
本当にありがとうございました。

お礼日時：2021/03/05 14:35

No.5 回答者： tatsumaru77 回答日時： 2021/03/04 23:20

確認漏れがありました。

A_Book.xlsmのシート名はなんでしょうか。（Sheet1で良いですか）
B_Book.xlsxのシート名はなんでしょうか。（Sheet1で良いですか）
B_Book.xlsxは既にオープンされている前提で良いですか。
（A_Book.xlsmにマクロを格納するので、当然A_Book.xlsmはオープン済み）

No.4 回答者： tatsumaru77 回答日時： 2021/03/04 21:37

１．②③が一致しない場合（B_BooK.xlsxに無い場合）の確認ですが

②が一致しない　かつ　③が一致しない・・・CaseA
②が一致しない　又は　③が一致しない・・・CaseB
CaseA,CaseBのどちらでしょうか。
又、CaseA,CaseBのどちらであっても①は一致しても、一致しなくてもかまわないことになりますが、
それであってますか。


２．
>例えば投稿画像の中でA_Bookの項番25のC列「****」C列「****」F列「2」台がB_Bookの中にあるか？
>※B_Bookのフィルタ順位としてはF→B→L→Hとなります。
>F列の「２」台がなければNG（背景色赤）
>一致していればOK、というイメージでございます。

（フィルタの意味が分かりませんが・・・）フィルターには関係なく
「②③が一致しない場合（B_BooK.xlsxに無い場合）」に従えば、
A_Book.xlsmのE列の値が、B_Book.xlsxのB列にない・・・判定①
A_Book.xlsmのF列の値が、B_Book.xlsxのH列にない・・・判定②
CaseAに従えば、判定①、判定②が共に成立すれば、赤色背景
CaseBに従えば、判定①、判定②のどちらかが成立すれば、赤色背景
ということになりますが、よろしいでしょうか。

No.3 回答者： tatsumaru77 回答日時： 2021/03/04 20:22

>①、②、③が一致し

>（１）A_Book.xlsmのG列≦B_Book.xlsxのL列の場合「OK」
>→「OK」表示セルはG列以降に。

OKの表示セルはA_Book.xlsmですね。
G列以降の意味が不明です。
G列にOKを設定すると、元の値を壊しませんか？
H,I,J,K等のなかから、具体的に１列を指定していただけませんでしょうか。

>（１）の場合以外・・・
>②③が一致しない場合（B_BooK.xlsxに無い場合）・・・・・・・・・・Case1
>①②③は一致したのにL列の数が足りない場合（L列は残数値です）・・・Case2
>→A_Book.xlsmの当該行の背景色を赤にを設定

．（１）以外の場合、Case1又はCase2なら
A_Book.xlsmの当該行の背景色を赤にを設定であってますか。
　　その場合、A列～？列まで全て背景色を赤にします。？列を指定してください。
　　又、Case1,Case2の何れにも該当しない場合は、なにもしなくて良いですか。
　　（例として、①が不一致で②③が一致の場合）

No.2 回答者： tatsumaru77 回答日時： 2021/03/04 14:42

以下、確認事項と不明点です。

１．比較対象項目については以下とする。
①．A_Book.xlsmのC列とB_Book.xlsxのF列
②．A_Book.xlsmのE列とB_Book.xlsxのB列
③．A_Book.xlsmのF列とB_Book.xlsxのH列
これであってますか。

２．上記①②③が一致した場合
（１）A_Book.xlsmのG列≦B_Book.xlsxのL列の場合
　　　A_Book.xlsmの当該行のK列へOKを設定
（２）上記以外の場合、A_Book.xlsmの当該行のK列へNGを設定
これであってますか。


３．上記２以外で、①②が一致した場合
　　　A_Book.xlsmの当該行のA列の背景色を赤にを設定
これであってますか。

４．上記２、上記３以外の場合、（例として①のみ一致の場合、もしくは1項目も一致しない場合等）
　　どのようにするか不明です。なにもしなくて良いのでしょうか。
　　上記２よりは重度のエラーのように思えますが、このケースは考慮しなくてよいのでしょうか。

No.1 回答者： chiune 回答日時： 2021/03/03 14:57

補足をお願いいたします。

> K列の行に"OK"又は"NG"

どういうときにOKでどういうときにNGですか？

> H列のデータがない場合

どのブックのH列ですか？

> 不一致の行に赤に色付けする

不一致の行とはどのブックのどの行ですか？
色づけするのは行全体で良いですか？