How do I solve this ?

[navinrb]

my question is clearly show in the image please help me if you can solve it.

 

[Norie]

As per the question in the image.

=IF(AND(A2=A3, B2=B3), "MATCHED", "NOT MATCHED")

 

[navinrb]

i want both c2 and c3 to show same remarks as matched or not matched if both conditions are met. your above formula will work only for 1st orginal cell not the duplicate cell
i want both original and duplicate cells to show same remarks

 

[Joe4]

This looks like a homework/assignment.
You should not expect us to do your homework for you, but we can give you hints.

Here is your hint. Use the basis of Norie's formula, but incorporate an OR function into it.

 

[navinrb]

ikn

This looks like a homework/assignment.
You should not expect us to do your homework for you, but we can give you hints.

Here is your hint. Use the basis of Norie's formula, but incorporate an OR function into it

no this is not homework its just an issue that i am not able to solve while doing my office work....i m not an excel pro....i already knew the nories logical formula which don't meet my desired result... OR is irrelvant here.....
i am not expecting you to do my work...i just want my queries to be solved ...its a little issue that can help me do my work more efficiently

 

[Joe4]

no this is not homework its just an issue that i am not able to solve while doing my office work....i m not an excel pro....i already knew the nories logical formula which don't meet my desired result...

Really? The wording of what you posted regarding what to use in formulas seems pretty specific. That is the kind of wording usually seen in assignments, homework, tests.

OR is irrelvant here.....

Why do you say that? If you don't know how to do it, then you probably shouldn't be dismissing ideas that might solve your issue.
The reason why Norie's formula only shows those lines on the first duplicate is because it is always comparing the current row to the row just below it.
But if you want BOTH rows to show the duplicate, then you need to check to see if the row below it is the same OR the row above it is the same.
So you need to check both things, and have it return true if either of those things is true. You use the OR function for "either" conditions.

 

[jasonb75]

The wording of what you posted regarding what to use in formulas seems pretty specific.

Looks more like a suggestion of what the OP thinks they might need than a specific requirement, Joe.

If rows 7 and 11 should also be considered as matched (which is not clear from the description in the image) then try this one

=LOOKUP(COUNTIFS(A:A,A2,B:B,B2),{1,2,3},{"Not Matched","Matched","More than 2 entries"})

 

[navinrb]

i don't understand why its an issue ? its just an extract of my data which m sharing..i m just looking for solution...you theoritical hints is insufficent to my practical issue...i dont understand how you want me to use OR here?

Really? The wording of what you posted regarding what to use in formulas seems pretty specific. That is the kind of wording usually seen in assignments, homework, tests.


Why do you say that? If you don't know how to do it, then you probably shouldn't be dismissing ideas that might solve your issue.
The reason why Norie's formula only shows those lines on the first duplicate is because it is always comparing the current row to the row just below it.
But if you want BOTH rows to show the duplicate, then you need to check to see if the row below it is the same OR the row above it is the same.
So you need to check both things, and have it return true if either of those things is true. You use the OR function for "either" conditions.

 

[Joe4]

i don't understand why its an issue ? its just an extract of my data which m sharing..i m just looking for solution...you theoritical hints is insufficent to my practical issue...i dont understand how you want me to use OR here?

At first glance, I thought that your data was sorted, so each set of duplicates would appear consecutively. If that was the case, you would just need to compare the values in the current row to the values above and below it. But on further inspection, it appears that the data is not sorted.

However, this should be able to be done pretty easily with COUNTIFS, just counting the number of rows that match the values in the current row, and if that count is more than one, identify as "MATCHED".

If you do not know how to use COUNTIFS, see here: MS Excel: How to use the COUNTIFS Function (WS)