matratus34
Board Regular
- Joined
- Nov 21, 2013
- Messages
- 74
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>
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?