Check for duplicates for 2 columns in other sheet

[BoxerLove]

Hello all,

I am using Excel 2010. Looking for some help with a template i am creating for work to find duplicates that match both the column A and B values exactly in another sheet. I work with purchase orders and when a we are trying to track what purchase order AND line items vendors are sending pricing back for. A PO May have multiple line items that each need their own price confirmed.Its setup like this

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Column A (PO Number)[/TD]
[TD="align: center"]Column B (Line item)[/TD]
[/TR]
[TR]
[TD="align: center"]123456789[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]123456789[/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]

​

A list of outstanding PO's that have not been priced is on Sheet1 Starting in cell A14, and the PO's they have sent back are on Sheet2 in the same format (Col A,B). The formula i am using currently works, but only references the PO Number column, and not the corresponding line item in Column B as well. I want the text "Updated" in column C just as it is doing now but only when the PO and Line item match exactly. My current formula is below. Can this be done without using a specific VBA Script for each individual vendor using a formula?

=IF(ISERROR(MATCH(A14,'Sheet2'!A:A,0)),"","Updated")

 

[elmer007]

Are you trying to flag the duplicates (so column C would return something like "Duplicate")? Or are you trying to return the actual price submitted and showing on the other sheet?

If you're simply trying to find duplicates, then in C2 (assumed to be the first line of data): =IF(COUNTIFS(Sheet2!$A:$A,Sheet1!$A2,Sheet2!$B:$B,Sheet1!$B2)>0,"Duplicate","Unique")

Let me know if this doesn't work or if you have any questions.

 

[BoxerLove]

Are you trying to flag the duplicates (so column C would return something like "Duplicate")? Or are you trying to return the actual price submitted and showing on the other sheet?

If you're simply trying to find duplicates, then in C2 (assumed to be the first line of data): =IF(COUNTIFS(Sheet2!$A:$A,Sheet1!$A2,Sheet2!$B:$B,Sheet1!$B2)>0,"Duplicate","Unique")

Let me know if this doesn't work or if you have any questions.

Yes, I want column C to show the text "Duplicate." The vendors dont always send back pricing for all the lines. They may send back line 1,3,4 and leave out line 2. So i want the PO # and Line 2 to not be marked as a duplicate because and removed from my sheet because we do not have pricing for that line yet. Thats why i needed it to reference an exact match between column A and B. I will try your formula now.

 

[BoxerLove]

Are you trying to flag the duplicates (so column C would return something like "Duplicate")? Or are you trying to return the actual price submitted and showing on the other sheet?

If you're simply trying to find duplicates, then in C2 (assumed to be the first line of data): =IF(COUNTIFS(Sheet2!$A:$A,Sheet1!$A2,Sheet2!$B:$B,Sheet1!$B2)>0,"Duplicate","Unique")

Let me know if this doesn't work or if you have any questions.

Hmmmmm, Isnt working quite right. It did not respond to me changing the Line item (Col B) number to a number that is not on my returned PO's sheet (Sheet2.)

Also note that the PO's will not be in the same exact order on Sheet2. The vendors respond with a list of PO's not in the same order as listed on Sheet1. So it has to search the whole column A and B for an exact match in the row anywhere on column A and B on Sheet2.

 

[BoxerLove]

Bump for help