Formula to find duplicates that match 2 different values in a row

[matratus34]

PRODUCT ID	PRICE
A123	1.66
A123	1.66
A134	1.22
A145	2.34
A134	1.24
A167	1.26
A129	1.67

<tbody>
</tbody>

Hi Guys, I got a list of data today, about 30000 rows with 20 different column headers.
To put my problem simply I had a product ID in column A and a price in column B.
There were 1000s of duplicates in the product ID but the price wasn't always duplicated in column B (sometimes it was different)
What I needed to do was find all ID duplicates that had prices that weren't duplicates.

For example - the only rows I would be left with from the table above would be Product ID A134 because it has a duplicate ID and non duplicate prices.



The way I did it is below but I'm sure there must be an easier way.

Firstly I used Conditional formatting on the Product IDs to show the duplicates and deleted the none duplicates rows.
Next I sorted alphabetically and inserted a column to use CONCATENATE to join together the Product ID and the price.
I then used Conditional formatting to show the duplicates in the CONCATENATE results and deleted the none duplicate rows again.

This left me with the rows I needed.

Must be a much easier way?

 

[matratus34]

The last bit is incorrect - after the CONCATENATE formula I deleted the duplicates not the non duplicates.

 

[J.Ty.]

I do not understand. You wrote:

What I needed to do was find all ID duplicates that had prices that weren't duplicates.

What do you do if you have
PRODUCT ID PRICE
A123 1.66
A123 1.66
A123 1.22

DO you want to leave A123 or not? Note that it does have prices which are not duplicates (1.22 and 1.66), and it does have prices which are duplicates (1.66 and 1.66), at the same time.

J.Ty.

 

[matratus34]

Hi, thanks for the reply. In your example above I would need to keep all 3 rows with A123 if there were different values in the price column. (in an of the rows)

To add a bit more detail there is another column with a unique identifier.
The end result required is to keep just 1 row for each product but before that each different price needs examining to see which is correct.

 

[J.Ty.]

You may use the following formulas:

Excel 2010

	A	B	C	D	E
1	PRODUCT ID	PRICE	Keep?	Unique ID	Nonunique ID Unique PRICE
2	A123	1,66	FALSE	FALSE	TRUE
3	A123	1,66	FALSE	FALSE	TRUE
4	A134	1,22	TRUE	FALSE	FALSE
5	A145	2,34	FALSE	TRUE	FALSE
6	A134	1,24	TRUE	FALSE	FALSE
7	A167	1,26	FALSE	TRUE	FALSE
8	A129	1,67	FALSE	TRUE	FALSE

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C2 =COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,"<>"&B2)>0 D2 =COUNTIF($A$2:$A$8,A2)=1 E2 =NOT(OR(C2,D2)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

After they are there, you can filter the data according to their values. Please let me know, if this satisfies your needs.

J.Ty.

 

[matratus34]

thanks for the reply. I'll give it a go tomorrow.

 

[Blakerid]

Is there any way to use just one formula to accomplish this?
Like an =INDEX(MATCH(INDEX()))?

 

[J.Ty.]

Is there any way to use just one formula to accomplish this?
Like an =INDEX(MATCH(INDEX()))?

Accomplish what?
As far as I understand, the originator wanted to be able to filter out rows with Id which have more than one price. Formula in C2 does it. The other two formulas are provided just in case.

J.Ty.