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 linked 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!

The example worksheet itself can be downloaded here: Example.xlsx

 

[NotoriousPopol]

Hi,

I achieved something thanks to pivot table. Basically, I added a column "Month" to your data : = TEXT(D2, "mmmm"). So now we have a column that can resume everything you did for a specific month.

So you can do a pivot table with : Filter = Company ; Column = Transaction Type ; Line = Month then Date ; Value = Summ of Total £. You can also go for Line = Month then Client name then Date. I would prefer this way.

Then for your "CLient count" I did something with a formula but I'm not very proud of it...

First, is the pivot table good for you?

 

[CC4581]

Hi, thank you for your response and your assistance with this issue. Unfortunately, a pivot table is just giving me the same data I was already able to interpret. The only data I am having issue with is keeping an accurate unique client count for each month. Everything else I am able to do myself, so perhaps your formula is the solution I need!

 

[MickG]

Try this for results in sheet "Company A Table":-

[COLOR="Navy"]Sub[/COLOR] MG15Feb34
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] cTable [COLOR="Navy"]As[/COLOR] Variant, Mth [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, p [COLOR="Navy"]As[/COLOR] Variant, Typ [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]With[/COLOR] Sheets("Source Data")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
ReDim cTable(1 To 13, 1 To 6)
cTable(1, 1) = "Month": cTable(1, 2) = "Client Count": cTable(1, 3) = "Type 1"
cTable(1, 4) = "Type 2": cTable(1, 5) = "Type 3": cTable(1, 6) = "Type 4":
[COLOR="Navy"]For[/COLOR] n = 2 To 13
    [COLOR="Navy"]For[/COLOR] Ac = 2 To 6
        cTable(n, Ac) = 0
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]For[/COLOR] n = 1 To 12
    cTable(n + 1, 1) = MonthName(n)
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
n = 1
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] p [COLOR="Navy"]In[/COLOR] .Item(K)
        Mth = Month(p.Offset(, 2).Value) + 1
        cTable(Mth, 2) = cTable(Mth, 2) + 1
        Typ = Split(p.Offset(, 3), " ")(1) + 2
        cTable(Mth, Typ) = p.Offset(, 4).Value
    [COLOR="Navy"]Next[/COLOR] p
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] With
'[COLOR="Green"][B]Change range address below to suit !!!![/B][/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Company A Table").Range("A20").Resize(13, 6)
    .Value = cTable
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[NotoriousPopol]

Hi,

I did something using additionnal columns, I let you mix everything if you prefer.

So first I add a column "Month" in col F : =MONTH(C2). This way I can easily look in the right data.

Now, I write a column that will count how many time the client name appears IN THIS SPECIFIC MONTH. So col G : =COUNTIFS($A$2:$A$19;$A$2:$A$19;$F$2:$F$19;F2).

Now, the result : I count 1/(col G) for the specific month. So col H : =SUM(IF($F$2:$F$19=F2;1/$G$2:$G$19;0)).

THIS IS AN ARRAY FORMULA so you need to press ctrl+shift+enter!!

Works for me, give me feedbacks!

Cheers.