Comparing values from columns in a worksheet with a set range of values in another workbook

[mountainscaler]

Hello All,

I am new to the forum and have been using VBA in a limited fashion for about half a year. However, it is my goal to get a lot better at it so here I am.

My first question involves comparing values between two different workbooks using a macro. Specifically I would like to:

1. Look in column C of Workbook-1 and associate column C cell values with cell values in column L. Column C values will be the key values to be searched in Workbook-2.
2. Then, look in Workbook-2, column A for the Workbook-1 keys and compare the values in Workbook-2 column J those in column L of Workbook-1.
3. If values in Workbook-1, column L exceed those in Workbook-2, column J for rows with matching keys, then highlight the exceeding cells in Workbook-1, column L in red.

Example of data format in Workbook 1:

A B C D E F G H I J K L Recip. Area analyte mthd grdnt Well date smpl fltrd owner prps value Lab A PO-1 alkalinity 310.1 ND E24 12/11/17 B78U Y WHH R 103 Lab A PO-1 iron 6010 ND E25 12/11/17 B89P N WHH R 0.03 Lab A PO-1 calcium 6010 ND E26 12/15/17 C45T Y WHH R 60.3 <tbody> </tbody>

<tbody>
</tbody>

Example of data format in Workbook 2:

A	B	C	D	E	F	G	H	I	J
Analyte	Filtered	units	Geo_mean	Ges_STDV	samp_nm	Min	Max	90_CL	95_CL
alkalinity	N	mg/L	116.85	1183	30	80.0	170.0	147.1	156.4
iron	Y	mg/L	0.055	6.17	22	0.006	7.2	0.57	1.10
calcium	N	mg/L	36.5	1.33	25	19.2	79.7	52.6	58.4

<tbody>
</tbody>

Any and all help would be greatly appreciated!

 

[Fluff]

Hi & welcome to the board.
Are the values in cols A & C unique, or can they occur multiple times within the same column?

 

[mountainscaler]

Thank you! There are duplicate values in both of those columns.

 

[Eric W]

Consider:

Sub CheckIt()
Dim MyDict As Object, MyVals As Worksheet, MyComp As Worksheet, MyData As Variant
Dim lr As Long, r As Long

    Set MyDict = CreateObject("Scripting.Dictionary")
    Set MyVals = Workbooks("Book2").Sheets("Sheet1")
    Set MyComp = Workbooks("Book1").Sheets("Sheet6")
    
    lr = MyVals.Cells(Rows.Count, "A").End(xlUp).Row
    MyData = MyVals.Range("A2").Resize(lr - 1, 10).Value
    For r = 1 To lr - 1
        MyDict(MyData(r, 1)) = MyData(r, 10)
    Next r
    
    For r = 2 To MyComp.Cells(Rows.Count, "A").End(xlUp).Row
        If MyDict.exists(CStr(MyComp.Cells(r, "C"))) Then
            If MyComp.Cells(r, "L") > MyDict(CStr(MyComp.Cells(r, "C"))) Then MyComp.Cells(r, "L").Interior.Color = vbRed
        End If
    Next r

End Sub

The parts in red you may need to adjust for your sheets. But if your columns are what you previously said, you should only have to worry about the workbook and worksheet names.

Duplicates in column C on workbook1 will cause no issues. Duplicates in Column A on workbook2 will cause the furthest down duplicate value to be considered.

 

[Fluff]

So if you had data like this.

Excel 2013 32 bit
	A	B	C	D	E	F	G	H	I	J	K	L
1	Analyte	Filtered	units	Geo_mean	Ges_STDV	samp_nm	Min	Max	90_CL	95_CL
2	alkalinity	N	mg/L	116.85	1183	30	80	170	147.1	156.4
3	iron	Y	mg/L	0.055	6.17	22	0.006	7.2	0.57	1.1
4	calcium	N	mg/L	36.5	1.33	25	19.2	79.7	52.6	58.4
5	alkalinity	N	mg/L	116.85	1183	30	80	170	147.1	100
6	iron	Y	mg/L	0.055	6.17	22	0.006	7.2	0.57	1.5
7	calcium	N	mg/L	36.5	1.33	25	19.2	79.7	52.6	63
8
9
10	Recip.	Area	analyte	mthd	grdnt	Well	date	smpl	fltrd	owner	prps	value
11	Lab A	PO-1	alkalinity	310.1	ND	E24	12/11/2017	B78U	Y	WHH	R	103
12	Lab A	PO-1	iron	6010	ND	E25	12/11/2017	B89P	N	WHH	R	0.03
13	Lab A	PO-1	calcium	6010	ND	E26	12/15/17	C45T	Y	WHH	R	60.3
Sheet1

Would any of the book1 values need to be highlighted, as 2 of them are both higher & lower than values in book2

 

[Fluff]

If you simply want to compare against the highest values, try

Sub CompareData()

   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   Dim Cl As Range
   
   Set Ws1 = Workbooks("[COLOR=#ff0000]Book1.xlsm[/COLOR]").Sheets("[COLOR=#ff0000]sheet1[/COLOR]")
   Set Ws2 = Workbooks("[COLOR=#ff0000]Book2.xlsm[/COLOR]").Sheets("[COLOR=#ff0000]sheet1[/COLOR]")
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws2.Range("A2", Ws2.Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            .Add Cl.Value, Cl.Offset(, 9).Value
         Else
            If Cl.Offset(, 9).Value > .Item(Cl.Value) Then .Item(Cl.Value) = Cl.Offset(, 9).Value
         End If
      Next Cl
      For Each Cl In Ws1.Range("C2", Ws1.Range("C" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then
            If Cl.Offset(, 9).Value > .Item(Cl.Value) Then Cl.Offset(, 9).Interior.Color = vbRed
         End If
      Next Cl
   End With
            
End Sub

Changing workbook & sheet names in red to suit

 

[mountainscaler]

There are 7000 lines of data in the actual workbook-1 that need to be compared to the set number of 80 rows of data in actual workbook-2. I receive these data-dumps every 2 weeks. So, I am trying to accurately and efficiently examine the received data to observe for background level exceedances.

 

[mountainscaler]

Both or your macros worked beautifully! Much appreciated!

 

[Fluff]

Glad we could help & thanks for the feedback