comparing rows in two worksheets

greatnewtaste

New Member
Joined
Mar 22, 2011
Messages
7
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 -->
progress.gif
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.
 
Upvote 0
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
 
Upvote 0
Try:

Code:
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top