Loop Column Compare

[pvans267]

Good afternoon,

I wonder if someone would be able to help me with an issue that I am having.

I have two worksheets in a workbook.

Each row in Column C contains values on either sheet that are sometimes different, and sometimes are the same:

Example:
Sheet1 Sheet2
C C
1 1
2 3
3 2
2 2
5 2


I am trying to create a macro to compare each row in column C on sheet1 and sheet2. And total the results be displayed in cells C2 - C4on Sheet 3. C2 represents "matching", C3 represents "Sheet1 Bigger", C4 represents "Sheet1 Smaller"<SHEET2"< p>
Therefore, the results for the simple example above would appear like so:
Sheet3
C
2
2
<SHEET2 p 1<>1

One final point (as if this wasn't tricky enough ), occasionally a row will have no data - in either sheet - for column C, and so I would like that row to be ignored and not be included in the "matching" result.

I am clueless as to where to start and would be very grateful if someone could help me.

Thank you,

Paul

 

[circledchicken]

Hi Paul,

Do you have to use a macro? A formula may be sufficient - try the following array formulae in cells C2 to C4 of Sheet3 (enter them using CTRL-SHIFT-ENTER rather than just ENTER):

C2:
=SUMPRODUCT((Sheet1!$C$1:$C$5=Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<>"")*(Sheet2!$C$1:$C$5<>""))

C3:
=SUMPRODUCT((Sheet1!$C$1:$C$5>Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<>"")*(Sheet2!$C$1:$C$5<>""))

C4:
<?XML:NAMESPACE PREFIX = Sheet2!$C$1 /><Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<><Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<><Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<><Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<><Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<>(for some reason this formula with less than doesn't show up properly here - its exactly as per the one in C3, just replace the first > with a less than)</Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<></Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<></Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<></Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<></Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<>
<Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<><Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<><Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<><Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<><Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<></Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<></Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<></Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<></Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<></Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<>
<Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<><Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<><Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<><Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<><Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<>
</Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<></Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<></Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<></Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<></Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<><Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<><Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<><Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<><Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<><Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<></Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<></Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<></Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<></Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<></Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<>
<Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<><Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<><Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<><Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<><Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<>Here, the first array checks for the condition (=,> or<), and the second and third ensure that blanks are skipped. You would change the ranges to whatever rows in column C you need to compare.
</Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<>
</Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<>
</Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<>
</Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<>
</Sheet2!$C$1:$C$5)*(Sheet1!$C$1:$C$5<>

 

[circledchicken]

Oops I didn't mean the CTRL-SHIFT-ENTER part, this should work with just ENTER.

 

[pvans267]

Thank you very much. Seems to work very nicely

Gonna play around with it a little more to complete testing but seems to be perfect.

Thank you so much

Paul

 

[circledchicken]

Your very welcome - I'm glad it works