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

mountainscaler · Jan 22, 2018

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:

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.
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.
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:
[TABLE="width: 1149"]
<tbody>[TR]
[TD="class: xl65, width: 114, bgcolor: transparent"]
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[/TR]
[TR]
[TD]Recip.
[/TD]
[TD]Area
[/TD]
[TD]analyte
[/TD]
[TD]mthd
[/TD]
[TD]grdnt
[/TD]
[TD]Well
[/TD]
[TD]date
[/TD]
[TD]smpl
[/TD]
[TD]fltrd
[/TD]
[TD]owner
[/TD]
[TD]prps
[/TD]
[TD]value
[/TD]
[/TR]
[TR]
[TD]Lab A
[/TD]
[TD]PO-1
[/TD]
[TD]alkalinity
[/TD]
[TD]310.1
[/TD]
[TD]ND
[/TD]
[TD]E24
[/TD]
[TD]12/11/17
[/TD]
[TD]B78U
[/TD]
[TD]Y
[/TD]
[TD]WHH
[/TD]
[TD]R
[/TD]
[TD]103
[/TD]
[/TR]
[TR]
[TD]Lab A
[/TD]
[TD]PO-1
[/TD]
[TD]iron
[/TD]
[TD]6010
[/TD]
[TD]ND
[/TD]
[TD]E25
[/TD]
[TD]12/11/17
[/TD]
[TD]B89P
[/TD]
[TD]N
[/TD]
[TD]WHH
[/TD]
[TD]R
[/TD]
[TD]0.03
[/TD]
[/TR]
[TR]
[TD]Lab A
[/TD]
[TD]PO-1
[/TD]
[TD]calcium
[/TD]
[TD]6010
[/TD]
[TD]ND
[/TD]
[TD]E26
[/TD]
[TD]12/15/17
[/TD]
[TD]C45T
[/TD]
[TD]Y
[/TD]
[TD]WHH
[/TD]
[TD]R
[/TD]
[TD]60.3
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Example of data format in Workbook 2:
[TABLE="width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[/TR]
[TR]
[TD]Analyte
[/TD]
[TD]Filtered
[/TD]
[TD]units
[/TD]
[TD]Geo_mean
[/TD]
[TD]Ges_STDV
[/TD]
[TD]samp_nm
[/TD]
[TD]Min
[/TD]
[TD]Max
[/TD]
[TD]90_CL
[/TD]
[TD]95_CL
[/TD]
[/TR]
[TR]
[TD]alkalinity
[/TD]
[TD]N
[/TD]
[TD]mg/L
[/TD]
[TD]116.85
[/TD]
[TD]1183
[/TD]
[TD]30
[/TD]
[TD]80.0
[/TD]
[TD]170.0
[/TD]
[TD]147.1
[/TD]
[TD]156.4
[/TD]
[/TR]
[TR]
[TD]iron
[/TD]
[TD]Y
[/TD]
[TD]mg/L
[/TD]
[TD]0.055
[/TD]
[TD]6.17
[/TD]
[TD]22
[/TD]
[TD]0.006
[/TD]
[TD]7.2
[/TD]
[TD]0.57
[/TD]
[TD]1.10
[/TD]
[/TR]
[TR]
[TD]calcium
[/TD]
[TD]N
[/TD]
[TD]mg/L
[/TD]
[TD]36.5
[/TD]
[TD]1.33
[/TD]
[TD]25
[/TD]
[TD]19.2
[/TD]
[TD]79.7
[/TD]
[TD]52.6
[/TD]
[TD]58.4
[/TD]
[/TR]
</tbody>[/TABLE]

Any and all help would be greatly appreciated!

Fluff · Jan 22, 2018

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

mountainscaler · Jan 22, 2018

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

Eric W · Jan 22, 2018

Consider:

Rich (BB code):

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 · Jan 22, 2018

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 · Jan 22, 2018

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

Code:

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 · Jan 22, 2018

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 · Jan 22, 2018

Both or your macros worked beautifully! Much appreciated!

Fluff · Jan 22, 2018

Glad we could help & thanks for the feedback

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

mountainscaler

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

mountainscaler

New Member

Eric W

MrExcel MVP

Fluff

MrExcel MVP, Moderator

Fluff

MrExcel MVP, Moderator

mountainscaler

New Member

mountainscaler

New Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

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

New Member

Excel Facts

MrExcel MVP, Moderator

New Member

MrExcel MVP

MrExcel MVP, Moderator

MrExcel MVP, Moderator

New Member

New Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock