Excel Help - Complicated question?

[SeCreTEnDinG]

Note: Sorry! I posted a crappy heading, should have called it need help with counting functions.

Hey, new to the forum, hoping to lend a hand and learn some new skills. My job is basically database reporting, and I'm fairly proficient with Excel. The Excel version is 2007, and the system is Windows XP.

The problem:

In one column, I have a list of digital sales reps, in the column next to the names, I have their client. I would use delete duplicates on the client row to help single out clients by reps, then countif the names - in a third column - of the reps to determine how many clients each rep had left.

Since doing that, I was double checking my revenue, and the amount was always off a little bit after doing this. I realized that when I deleted duplicate clients, there must be some reps that overlap clients, thus it deleted the rep, throwing my dollars off, which also means the countif wasn't actually counting all of the reps.

Is there a code that could count the number of non-duplicate clients in column E4:E5000 for each individual rep in column C4:C5000? I've been fiddling around with some codes, but my brain doesn't seem to want to think of the function that I'm missing here.

Thanks,
SE

 

[SeCreTEnDinG]

Looks like I figured it out.

=COUNTIFS($E$4:$E$1359,E4,$C$4:$C$1359,C4)

Seems to be working, now I can just delete duplicates of the sales reps and everything is good!

Never mind, I still need it to not count duplicate clients. *Facepalm*

 

[MickG]

Try this :- This is for Clients in column "E", and Reps in column "C" !!!
Results start "F1 & G1

[COLOR="Navy"]Sub[/COLOR] MG08Jan57
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] k [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object
 
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("C4"), Range("C" & Rows.Count).End(xlUp))
 [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = 1
   [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
            [COLOR="Navy"]End[/COLOR] If
                Dic(Dn.Value)(Dn.Offset(, 2).Value) = Empty
    [COLOR="Navy"]Next[/COLOR] Dn
   
  [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic.Keys
        c = c + 1
        Cells(c, "F") = k
        Cells(c, "G") = Dic(k).Count
    [COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[SeCreTEnDinG]

[TABLE="width: 459"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Salesrep Name
[/TD]
[TD]Client Name[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]BARRY CULLEN CHEV OLDS[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]BARRY CULLEN CHEV OLDS[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]GUELPH CITY MAZDA[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]GUELPH CITY MAZDA[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]GUELPH HYUNDAI[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]GUELPH HYUNDAI[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]GUELPH KIA[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]GUELPH KIA[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]GUELPH NISSAN[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]GUELPH NISSAN[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]GUELPH TOYOTA [/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]GUELPH TOYOTA [/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]GUELPH TOYOTA [/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]GUELPH TOYOTA [/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]GUELPH VOLKSWAGEN[/TD]
[/TR]
[TR]
[TD]Anne[/TD]
[TD]GUELPH VOLKSWAGEN[/TD]
[/TR]
[TR]
[TD]Anne[/TD]
[TD]HJM Insurance-Cam Guthrie[/TD]
[/TR]
[TR]
[TD]Anne[/TD]
[TD]HJM Insurance-Cam Guthrie[/TD]
[/TR]
[TR]
[TD]Anne[/TD]
[TD]Olympic Honda[/TD]
[/TR]
[TR]
[TD]Anne[/TD]
[TD]Olympic Honda[/TD]
[/TR]
[TR]
[TD]Anne[/TD]
[TD]Robinson Pontiac Buick[/TD]
[/TR]
[TR]
[TD]Anne[/TD]
[TD]Robinson Pontiac Buick[/TD]
[/TR]
[TR]
[TD]Anne[/TD]
[TD]STONE ROAD MALL[/TD]
[/TR]
[TR]
[TD]Anne[/TD]
[TD]STONE ROAD MALL[/TD]
[/TR]
[TR]
[TD]Anne[/TD]
[TD]Wayne Pitman Ford Sales[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Wayne Pitman Ford Sales[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Wellington Motors[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Wellington Motors[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Buy N Sell[/TD]
[/TR]
</tbody>[/TABLE]


Data example. Column A are the names of the reps, column B I need to count and give a total amount of non-duplicated clients to each of the reps. Before I would delete duplicates in column B, then copied in column C a countif on column A to figure out how many reps were left which I assumed would give me a proper count. I would then just copy and paste values from C back into C, and then remove duplicates from A.

 

[SeCreTEnDinG]

Thanks MickG, I'll try it soon, just running off to a meeting with the boss.

 

[SeCreTEnDinG]

You're brilliant Mick! Thank you very much for the help! I greatly appreciate it. Might even modify the code a little to calculate the total sum of their revenue later today to save myself some steps.

Cheers!