• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Trying to match rows in spreadsheet

A

Ashvin

New Member
Hi,

I have spreadsheet that is about 1000 rows and I am trying to matched the rows. Here is examples of what I am trying to get. The amounts are sorted in ascending order. If amount matches, than I would want formulae to insert "Matched" otherwise "Not Matched". I tried using IF(A1=A2,"Matched", "Not Matched"), it works in few cases but because there is no pattern and I am not getting correct results. I could have 2 matched rows and then maybe next 3 rows that will not match. I tried Vlookup without any luck. Below is my desired result

Amount Matched/Not Matched
10.00 Matched
10.00 Matched
20.00 Not Matched
30.00 Not Matched
40.00 Matched
40.00 Matched
50.00 Not matched
60.00 Not Matched
70.00 Not Matched
80.00 Matched
80.00 Matched
90.00 Matched
90.00 Matched
100.00 Not Matched
110.00 Not Matched
120.00 Not Matched
130.00 Not Matched
140.00 Matched
140.00 Matched
 

Attachments

  • MatchingRows.xls
    315 KB · Views: 5
Hi Ashwin,

Going through your IF formula, it seems you are comparing both the cell below and above it. But in the values you put manually you are just comparing row below it.

So what exactly you want to compare?

Regards,
 
Hi Ashwin,

Please can you use the below formula and let us know if this what you are looking for??
Code: 
=IF(COUNTIF($B$24:$B$42,B24)>1,"Matched","Not Matched")
Regards!!
 
Ashvin said:
Hi,

I have spreadsheet that is about 1000 rows and I am trying to matched the rows. Here is examples of what I am trying to get. The amounts are sorted in ascending order. If amount matches, than I would want formulae to insert "Matched" otherwise "Not Matched". I tried using IF(A1=A2,"Matched", "Not Matched"), it works in few cases but because there is no pattern and I am not getting correct results. I could have 2 matched rows and then maybe next 3 rows that will not match. I tried Vlookup without any luck. Below is my desired result

Amount Matched/Not Matched
10.00 Matched
10.00 Matched
20.00 Not Matched
30.00 Not Matched
40.00 Matched
40.00 Matched
50.00 Not matched
60.00 Not Matched
70.00 Not Matched
80.00 Matched
80.00 Matched
90.00 Matched
90.00 Matched
100.00 Not Matched
110.00 Not Matched
120.00 Not Matched
130.00 Not Matched
140.00 Matched
140.00 Matched
Click to expand...
Somendra Misra said:
Hi Ashwin,

Going through your IF formula, it seems you are comparing both the cell below and above it. But in the values you put manually you are just comparing row below it.

So what exactly you want to compare?

Regards,
Click to expand...

Somendra Misra said:
Try using below formula in C24 of sheet1 and copy down.

=IF(OR(B24=B23,B24=B25),"Matched","Not Matched")

Regards,
Click to expand...

Thank You Somendra, your formulae works perfect. Thank you very much!!!
 
AIM said:
Hi Ashwin,

Please can you use the below formula and let us know if this what you are looking for??
Code: 
=IF(COUNTIF($B$24:$B$42,B24)>1,"Matched","Not Matched")
Regards!!
Click to expand...

Thank You AIM, your formulae works perfect. Thanks for all your help.
 
You must log in or register to reply here.
Back
Top