Highlighting Duplicates and Merging them at the same time

Hi Klaudia,

Thank you for your forum question.

Highlighting and removing duplicates need to be done in 2 stages.

With your data relating to invoice payments, I would expect it to include headers for Invoice number, customer details, amount due, date due etc. If you have an entire record that is repeated then you can use Conditional Formatting to highlight duplicates by the Invoice number:

1. Select all the Invoice number cells and go to HOME > CONDITIONAL FORMATTING > HIGHLIGHT CELLS RULES > DUPLICATE VALUES

2. Click OK and you get all duplicates highlighted in red.

This works fine if the entire record that is repeated is for a given Invoice number. But what if the same invoice number was listed for the same customer but for a different product and amount, ie. the customer purchased multiple products under the same invoice number. In this case, you would want to keep both records and only highlight them if they were a duplicate across all the columns. So to highlight a duplicate record you will need to write some formulas first (COUNTIF and IF) and then use a further formula in Conditional Formatting. This process is explained in the following Youtube video:

https://www.youtube.com/watch?v=G8rDTwA4c0k

Once you have highlighted duplicates, the next step is to remove them just leaving one unique record. There are 3 options available depending on your IT set up and requirements. They are:

1. the 'Remove duplicates' feature (via the DATA tab)
2. Using the UNIQUE function (only available to Office 365 users)
3. Power Query - which allows you to automate this process if new data is added as part of a monthly report (for example)

Each option is explained in this Youtube video:

https://www.youtube.com/watch?v=ADArCWLz55Y

I hope this has helped you solve your issue. Please let us know if it has.

Kind regards
Martin Sutherland
(Senior Applications Trainer)