highlighting duplicates and merg

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Highlighting Duplicates and Merging them at the same time

Highlighting Duplicates and Merging them at the same time

resolvedResolved · Urgent Priority · Version 365

Klaudia has attended:
Excel Intermediate course
Excel Introduction course

Highlighting Duplicates and Merging them at the same time

I have a spend data report with invoices, a lot of them are the same. How can I highlight duplicates and merge the rows at the same time ? Is this even possible? I don't want to do it manually as this will take forever.

Many Thanks!

RE: 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)


Thu 13 Jul 2023: Automatically marked as resolved.

 

Training courses

 

Training information:

See also:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Validating text entries

1. Select the range of cells.
2. From the Data menu, select Validation.
3. Select the Settings tab.
4. From the Allow dropdown list, select Custom.
5. In the Formula box, enter the following formula:

=IsText (A1)

where A1 is the first cell in the range.
6. Click OK.

View all Excel hints and tips


Server loaded in 0.07 secs.