comparing rows in two worksheets

[greatnewtaste]

Hi all,

macro newbie here, any help is greatly appreciated
i will try to be as detailed as possible

i have two worksheets

worksheet1 columns
code, side, quantity

worksheet2 columns
code, side, quantity

*yes, both have same columns
the side column can have only three values: 1,2, or 5 (buy, sell, shortsell for those that are curious)

what i need:

for every code and side in ws1 find the corresponding code and side in ws2
compare the quantities in both rows
if the quantity in ws2 >= quantity in ws1 ...display ok in the cell next to the row in ws1
if the quantity in ws2 < quantity in ws1...display the difference in the quantities

the code can appear multiple times in ws2


example:

ws1:

3035 5 49,000
2606 2 2,000
2498 2 1,000
2912 2 3,000
2887 2 17,000
5522 2 22,000


ws2:

2887 2 34000
2324 2 68000
9933 2 258000
2204 2 14000
2204 5 14000


been trying to find a solution for ages now, need help <!-- / message --><!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --><!-- END TEMPLATE: ad_showthread_firstpost_sig -->

<!-- controls -->

 

[Andrew Poulsom]

Welcome to MrExcel.

You could use a formula like:

=IF(ISNUMBER(MATCH(1,INDEX((Sheet2!A$2:A$1000=A2)*(Sheet2!B$2:B$1000=B2),),FALSE)),IF(INDEX(Sheet2!C$2:C$1000,MATCH(1,INDEX((Sheet2!A$2:A$1000=A2)*(Sheet2!B$2:B$1000=B2),),FALSE))>=C2,"OK",INDEX(Sheet2!C$2:C$1000,MATCH(1,INDEX((Sheet2!A$2:A$1000=A2)*(Sheet2!B$2:B$1000=B2),),FALSE))-C2),"")

in D2 copied down.

 

[greatnewtaste]

thanks for the reply

but I was looking to do this via a macro

i'm having trouble with there being two or more entries for the same code number in worksheet2

 

[greatnewtaste]

help? anybody?

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim Sh1 As Worksheet
    Set Sh1 = Worksheets("Sheet1")
    With Sh1
        With .Range("D2:D" & .Range("A" & .Rows.Count).End(xlUp).Row)
            .Formula = "=IF(ISNUMBER(MATCH(1,INDEX((Sheet2!A$2:A$1000=A2)*(Sheet2!B$2:B$1000=B2),),FALSE)),IF(INDEX(Sheet2!C$2:C$1000,MATCH(1,INDEX((Sheet2!A$2:A$1000=A2)*(Sheet2!B$2:B$1000=B2),),FALSE))>=C2,""OK"",INDEX(Sheet2!C$2:C$1000,MATCH(1,INDEX((Sheet2!A$2:A$1000=A2)*(Sheet2!B$2:B$1000=B2),),FALSE))-C2),"""")"
            .Value = .Value
        End With
    End With
End Sub

 

[greatnewtaste]

hey thanks for the help, sorry for the late reply just saw your post
really useful...

it works fine, but the values in the first column in ws2 can appear multiple times
the formula only finds and compares the first time the value appears in the column

is there any way around that?

 

[Andrew Poulsom]

Can you give an example? The formula finds the first matching combination of code and side.

 

[greatnewtaste]

sure,

in ws1 the code values will always be unique

in ws2 however there could be rows like:

2502 2 400
2502 2 1700

so it would have to go past the first matching combination of code and side

hope this clarifies

 

[Andrew Poulsom]

Please post a representative sample of your data showing what results you expect and explaining the reason.

 

[greatnewtaste]

i have sent you a pm