Counting number of entries duplicated more than three times

R

Rusty315

Board Regular
Joined
Sep 13, 2012
Messages
54
Hi

My apologies in advance if I don't explain this properly. I wasn't even sure how to search for a solution here.

I have a table where column A has the names of salespeople, column B has the name of a magazine into which they've booked a client and column C has the name of the client.

A series booking is where a client has been booked by a salesperson into a magazine for three issues or more. There would be three separate entries for each issue and all would look the same (an example is below).

I want to be able to count the number of series bookings automatically.

As an example:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]John[/TD]
[TD]Bits and Bobs Magazine[/TD]
[TD]Client A[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Bits and Bobs Magazine[/TD]
[TD]Client A[/TD]
[/TR]
[TR]
[TD]John [/TD]
[TD]Bits and Bobs Magazine
[/TD]
[TD]Client D[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Bits and Bobs Magazine[/TD]
[TD]Client A[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Fast Cars Magazine[/TD]
[TD]Client C[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Fast Cars Magazine[/TD]
[TD]Client E[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Fast Cars Magazine[/TD]
[TD]Client F[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Fast Cars Magazine[/TD]
[TD]Client F[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Fast Cars Magazine[/TD]
[TD]Client F[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Bits and Bobs Magazine[/TD]
[TD]Client G[/TD]
[/TR]
</tbody>[/TABLE]

















In this example, John has booked Client A into Bits and Bobs Magazine 3 times so that is one (1) series booking. Peter has booked Client F into Fast Cars Magazine 3 times so that is one (1) series booking for him also.

There is much more data on the table than this but none of it pertains to series bookings so I've omitted it. I use a pivot table to sort it all out and I get everything I need from the table except for series booking data.

What's the best way to go about this?

If anyone would like to see the actual spreadsheet, I'm happy to send it to them.

Thanks in advance for any help anyone can give me and my apologies if I haven't explained this clearly.

Cheers!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Hi Rusty does each 3 count as another one, if so have you tried creating another table with Employes as the headings and Clients as the first row

You could then do a countifs, divide by three and round down
=ROUNDDOWN(COUNTIFS($A$1:$A$10,F$1,$C$1:$C$10,$E2)/3,0)

Where:
A is your staff in the 1st table
F is your staff as the row in the second table
C is your clients in the first table
E is your clients as a column in the second table
 
Upvote 0
Hi Rusty

What happens if it's more than 3 times?

For ex.: John has booked Client A into Bits and Bobs Magazine 10 times

Dows it count as 1, because it's more than 3 times?
 
Upvote 0
Hi Jugantarji & PGC

In answer to both your questions, even if a client has booked in 10 times, as long as it's for the same magazine it's considered one series booking.

If a client booked in 3 times for one magazine and that same client has also booked in 10 times for another magazine, they'd be considered 2 series bookings, one series booking for each magazine.

Jugantarji, thanks so much for your solution. Unfortunately, dividing by 3 won't work because of what I've mentioned above. If a client has booked in 6 times, it's still only one series booking whereas dividing by 3 would count it as two series bookings.

If it helps (not sure it will) I put all this data into a pivot table but I can't get counts on there, for example how many clients a

Does that explain it clearly? Thanks guys, really appreciate your help.
 
Upvote 0
Rusty315 said:
Hi

My apologies in advance if I don't explain this properly. I wasn't even sure how to search for a solution here.

I have a table where column A has the names of salespeople, column B has the name of a magazine into which they've booked a client and column C has the name of the client.

A series booking is where a client has been booked by a salesperson into a magazine for three issues or more. There would be three separate entries for each issue and all would look the same (an example is below).

I want to be able to count the number of series bookings automatically.

As an example:
[...]
In this example, John has booked Client A into Bits and Bobs Magazine 3 times so that is one (1) series booking. Peter has booked Client F into Fast Cars Magazine 3 times so that is one (1) series booking for him also.

There is much more data on the table than this but none of it pertains to series bookings so I've omitted it. I use a pivot table to sort it all out and I get everything I need from the table except for series booking data.

What's the best way to go about this?

If anyone would like to see the actual spreadsheet, I'm happy to send it to them.

Thanks in advance for any help anyone can give me and my apologies if I haven't explained this clearly.

Cheers!
Click to expand...

Consider...

[TABLE="width: 479"]
<tbody>[TR]
[TD="class: xl65, width: 76, bgcolor: transparent"]Rep
[/TD]
[TD="class: xl65, width: 228, bgcolor: transparent"]Mag
[/TD]
[TD="class: xl65, width: 90, bgcolor: transparent"]Cus
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 182, bgcolor: transparent"]>=3 at a time subscribers
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 76, bgcolor: transparent"]John
[/TD]
[TD="class: xl64, width: 228, bgcolor: transparent"]Bits and Bobs Magazine
[/TD]
[TD="class: xl64, width: 90, bgcolor: transparent"]Client A
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]john
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 76, bgcolor: transparent"]John
[/TD]
[TD="class: xl64, width: 228, bgcolor: transparent"]Bits and Bobs Magazine
[/TD]
[TD="class: xl64, width: 90, bgcolor: transparent"]Client A
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]peter
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 76, bgcolor: transparent"]John
[/TD]
[TD="class: xl64, width: 228, bgcolor: transparent"]Bits and Bobs Magazine
[/TD]
[TD="class: xl64, width: 90, bgcolor: transparent"]Client D
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 76, bgcolor: transparent"]John
[/TD]
[TD="class: xl64, width: 228, bgcolor: transparent"]Bits and Bobs Magazine
[/TD]
[TD="class: xl64, width: 90, bgcolor: transparent"]Client A
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 76, bgcolor: transparent"]John
[/TD]
[TD="class: xl64, width: 228, bgcolor: transparent"]Newsweek
[/TD]
[TD="class: xl64, width: 90, bgcolor: transparent"]Client A
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 76, bgcolor: transparent"]John
[/TD]
[TD="class: xl64, width: 228, bgcolor: transparent"]Newsweek
[/TD]
[TD="class: xl64, width: 90, bgcolor: transparent"]Client A
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 76, bgcolor: transparent"]John
[/TD]
[TD="class: xl64, width: 228, bgcolor: transparent"]Newsweek
[/TD]
[TD="class: xl64, width: 90, bgcolor: transparent"]Client A
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 76, bgcolor: transparent"]John
[/TD]
[TD="class: xl64, width: 228, bgcolor: transparent"]Newsweek
[/TD]
[TD="class: xl64, width: 90, bgcolor: transparent"]Client A
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 76, bgcolor: transparent"]John
[/TD]
[TD="class: xl64, width: 228, bgcolor: transparent"]Fast Cars Magazine
[/TD]
[TD="class: xl64, width: 90, bgcolor: transparent"]Client C
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 76, bgcolor: transparent"]John
[/TD]
[TD="class: xl64, width: 228, bgcolor: transparent"]Chess 4.0
[/TD]
[TD="class: xl64, width: 90, bgcolor: transparent"]Client P
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 76, bgcolor: transparent"]John
[/TD]
[TD="class: xl64, width: 228, bgcolor: transparent"]Chess 4.0
[/TD]
[TD="class: xl64, width: 90, bgcolor: transparent"]Client P
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 76, bgcolor: transparent"]John
[/TD]
[TD="class: xl64, width: 228, bgcolor: transparent"]Chess 4.0
[/TD]
[TD="class: xl64, width: 90, bgcolor: transparent"]Client P
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 76, bgcolor: transparent"]Peter
[/TD]
[TD="class: xl64, width: 228, bgcolor: transparent"]Fast Cars Magazine
[/TD]
[TD="class: xl64, width: 90, bgcolor: transparent"]Client E
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 76, bgcolor: transparent"]Peter
[/TD]
[TD="class: xl64, width: 228, bgcolor: transparent"]Fast Cars Magazine
[/TD]
[TD="class: xl64, width: 90, bgcolor: transparent"]Client F
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 76, bgcolor: transparent"]Peter
[/TD]
[TD="class: xl64, width: 228, bgcolor: transparent"]Fast Cars Magazine
[/TD]
[TD="class: xl64, width: 90, bgcolor: transparent"]Client F
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 76, bgcolor: transparent"]Peter
[/TD]
[TD="class: xl64, width: 228, bgcolor: transparent"]Fast Cars Magazine
[/TD]
[TD="class: xl64, width: 90, bgcolor: transparent"]Client F
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 76, bgcolor: transparent"]John
[/TD]
[TD="class: xl64, width: 228, bgcolor: transparent"]Bits and Bobs Magazine
[/TD]
[TD="class: xl64, width: 90, bgcolor: transparent"]Client G
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

D2: john
D3: peter

E2, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=SUM(IF(FREQUENCY(IF($C$2:$C$18<>"",IF($A$2:$A$18=D2,
  MATCH($B$2:$B$18&"|"&$C$2:$C$18,$B$2:$B$18&"|"&$C$2:$C$18,0))),
   ROW($A$2:$A$18)-ROW(A2)+1)>=3,1))
 
Upvote 0
Untested (I don't have 2007+ at home for the countifs) you could extend @Jugantarji's formula to

=IF(ROUNDDOWN(COUNTIFS($A$1:$A$10,F$1,$C$1:$C$10,$E2)/3,0),1,0)

Any value greater than 1 gives you 1 and 0 gives you 0.
 
Upvote 0
May i Suggest you make a pivot table with employees and clients as row lables, magazines as column lables, and Count of clients in your values you could then do a simple if formula to tell you if the employee has a series booking

eg.
=IF(B6>2,1,0)

And fill down the formula.
 
Upvote 0
An alternative, showing (Name, Mags/No Sold, Sold Counts > 3)
Results start "F1".
Code: 
[COLOR="Navy"]Sub[/COLOR] MG15Jan08
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dic1        [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] Dic2        [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] k
[COLOR="Navy"]Dim[/COLOR] Num         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]With[/COLOR] ActiveSheet
[COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Set[/COLOR] Dic2 = CreateObject("scripting.dictionary")
        Dic2.CompareMode = vbTextCompare
[COLOR="Navy"]Set[/COLOR] Dic1 = CreateObject("scripting.dictionary")
        Dic1.CompareMode = vbTextCompare
   [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Temp = Dn.Offset(, 1) & ", " & Dn.Offset(, 2)
    [COLOR="Navy"]If[/COLOR] Not Dic1.Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]Set[/COLOR] Dic2 = CreateObject("scripting.dictionary")
            Dic2.CompareMode = vbTextCompare
            Dic2.Add Temp, 1
            Dic1.Add Dn.Value, Dic2
    [COLOR="Navy"]Else[/COLOR]
       [COLOR="Navy"]If[/COLOR] Not Dic1(Dn.Value).Exists(Temp) [COLOR="Navy"]Then[/COLOR]
              Dic1(Dn.Value).Add (Temp), 1
        [COLOR="Navy"]Else[/COLOR]
             Dic1.Item(Dn.Value)(Temp) = Dic1.Item(Dn.Value)(Temp) + 1
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Dim[/COLOR] G
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
ReDim ray(1 To Dic1.Count, 1 To 3)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic1.keys
    c = c + 1
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] G [COLOR="Navy"]In[/COLOR] Dic1.Item(k)
        Num = IIf(Dic1(k).Item(G) >= 3, 1, 0)
        ray(c, 1) = k: ray(c, 2) = ray(c, 2) & G & " (" & Dic1(k).Item(G) & ")" & Chr(10): ray(c, 3) = ray(c, 3) + Num
    [COLOR="Navy"]Next[/COLOR] G
[COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]With[/COLOR] Range("F2").Resize(c, 3)
    .Offset(-1) = Array("Name", "Magazine/Count", "Counts > 3")
    .Value = ray
    .Rows.AutoFit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thanks very much for your help, everyone.

Mick, unfortunately I don't know how to use VB yet (and I say yet, I will learn!) so for now, I'd like to use a solution I can understand and adapt if and when things change. Aladin's response has worked a treat for me (thank you very much!) but I will continue to experiment with the PT as Jugantarji has suggested.

I genuinely appreciate everyone's input, thank you very much.

Mr Excel is fast becoming one of my best mates....
 
Upvote 0
Rusty315 said:
Thanks very much for your help, everyone.

Mick, unfortunately I don't know how to use VB yet (and I say yet, I will learn!) so for now, I'd like to use a solution I can understand and adapt if and when things change. Aladin's response has worked a treat for me (thank you very much!) but I will continue to experiment with the PT as Jugantarji has suggested.

I genuinely appreciate everyone's input, thank you very much.

Mr Excel is fast becoming one of my best mates....
Click to expand...

You are welcome. Thanks for providing feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Repeating a pivot table many times and changing only one column -- are there more efficient approaches?
Replies
0
Views
448
sschaefer
S
U
  • Solved
Count Number of Times Values appear in a Column and Rank top 3
Replies
5
Views
190
Fluff
Fluff
S
  • Solved
Comparing time stamp values to remove 'duplicate' time entries (rows).
Replies
7
Views
293
felixstraube
F
D
  • Solved
How do I generate random numbers for duplicate entries in Column A
Replies
8
Views
530
Eric W
Eric W
C
  • Question Question
VBA Copy, Paste, Match query
Replies
3
Views
757
ckdragon
C

Forum statistics

Threads
1,223,249
Messages
6,171,031
Members
452,374
Latest member
keccles

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top