[Excel 2010] Removing Duplicate Data, Leaving 1 Per Month

[CC4581]

Hi all,

Apologies if the title doesn't make much sense, I'll do my best to clarify. I'm looking for a way to go through client data from the past year and work out our total earnings from them based on their affiliated 3rd party company, with duplicate clients from each month being removed, only leaving 1 in each month. However, I still need the money associated with all of the duplicated clients to show. I have created a quick example workbook to try and display the issue I am encountering.

In the first tab (Source Data) is all the information untouched. This is pretty much how the extracted report is laid out in the application where everything is stored. My initial process was to do the following:

1) Create a tab for the first company I would create a report for (Company A).

2) Filter down the Source Data tab to show only clients associated with Company A (In the example sheet only Company A is an entity.)

3) Copy/Paste this data into its own tab (Company A Clients Deduped) and use the Remove Duplicates function to see only unique clients.

4) Use a COUNTIF formula that targets the Company A Clients Deduped tab on the Company A Table tab with necessary date ranges to display how many unique clients we had last year.

5) Use SUMIF formulas on the Company A Table that targets the Source Data tab and allows me to get the money only associated with Company A and is also broken down by our different transaction types.

Unfortunately, this results in the Company A table displaying mismatched data. For example the March section claims we had 0 clients affiliated with Company A in that month, but received £2,614 of Transaction Type 3. Of course, I understand why this happens, as the columns are targeting a different data set to each other.

Essentially, what I am looking for is a way to not count duplicate clients, but only if they have already appeared in the target month. The best example of this in my example workbook is Client B, which appears in January, March and June, but is removed from the March and June counts. As it appears in January twice, I want it to count Client B only once in January, and then once more in both March and June, while ensuring all the money from all 4 Client B entries are counted.

An automated process would certainly be greatly preferred as the real data contains the following:

4 transaction types
33 3rd party companies
14,000 rows of data

However, a manual process can also be considered as long as it wouldn't be incredibly time consuming.

Hope someone is able to help me out!

[dflak]

This sounds like a job for a pivot table. I wish I could point you in the direction of a good pivot table tutorial, but I learned on 2003 and have been learning it incrementally as Microsoft made improvements. I suggest you do a web search for Pivot Table Tutorial 2010.