Final Friday Fix: July 2022 Challenge

On the final Friday of each month, we are going to set an Excel / Power BI challenge for you to puzzle over so that you can get your “Excel fix”.  Challenge your office colleagues to see who can solve the puzzle quickest.  There are no prizes at this stage: you are playing for bragging rights only!

The Challenge

This month, we have the following problem.  There is a table with multiple rows of transactions under the same invoice numbers.  Our goal is to group rows so that those with the same Invoice ID will be on the same line, and column ‘Invoice ID’ will only show a unique list of invoices.

This issue may be common for anyone who wants to remove duplicates on a key column while still keeping specific data from others.  You can download the question file here.

This month’s challenge is to combine rows of the provided table by column ‘Invoice ID’.  The result should look like the table generated at the bottom of the picture below:

Below are some conditions:

• ‘Invoice ID’ and ‘Make’ columns only show up once within that invoice group
• ‘Transaction ID’ and ‘Description’ columns combine all data on the same line with a delimiter (e.g. “, ”)
• ‘Transaction Date’ column lists the latest transaction date of each invoice
• ‘Plate’ column ignores the blanks and shows only the last item within that group
• ‘Amount’ column calculates sum of the amount of each invoice.

Sounds easy?  Then why not have a go?  We’ll publish one solution in Monday’s blog.  Have a great weekend in the meantime!