Highlighting Duplicates within a unique identifier

[lukequest]

Hi,

Need help with a brain teaser....

I'm currently sorting a price list with different price breaks and each product has its own unique ID. We have multiple rows of the same ID to differentiate the price break across 3 rows. However, there are some unwanted duplicates within some of the ID's.

Now I have thousands of UID's and need a way to be able to highlight all those unwanted duplicates within each UID, is there a way I can do this?

[lukequest]

See this doc to get an idea of the issue (couldn't put a link in the post)

docs.google.com/spreadsheets/d/1F7tgBOHmzg758iz01QlFZUWByjGbUJ6rE3vc6mI86p0/edit?usp=sharing

[Pete_UK]

You can't post a link until you have made 10 posts, but you can attach a sample Excel workbook (some contributors can't use external links anyway, do to company policy or firewalls etc.).

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

[lukequest]

Hopefully this has attached

[Pete_UK]

You could put this formula in E2:

=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2,D$2:D2,D2)=1,"ok","delete")

then copy down - duplicate records will be tagged with "delete", so you could apply autofilter to column E, select "delete" from the filter drop-down, then highlight all the visible rows (using the row identifiers) and delete all those duplicates in one operation. Then remove the autofilter and delete column E.

Hope this helps.

Pete

[torachan]

See if the attached works for you.
There are two helper columns F & H
Into F1 there is a formula and copied down to last row that has your data in.

Please Login or Register  to view this content.

On sheet1 there is a button 'SORT'
On the 'Sheet1' there is code behind the button and a 'function'
Leave column H clear as it assists data manipulation.
After you have seen the process you can set helper columns to 0 width to aid cosmetics.

torachan.

[lukequest]

That has worked perfectly thank you very much Pete!

[Pete_UK]

You're welcome.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of any post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete