Counting unique data for duplicate customers

[chrischew2]

Hi there,

I'm new here, but been a huge admirer of this message board for quite a while now. So I'm really hoping someone here can help me with a problem I've been struggling with for a few days now.

Here's my situation: I have a group of salesmen visit dealers several times a month, and I would like to capture and analyse the data from their visits so that I can pivot the database and use it to automatically update some graphs. However, I want each dataset to be unique to each dealer so that it isn't counted everytime the salesman visits the dealer. How do I do this?

Here's a sample of my data (in reality I have about 1000 rows, and columns pulling data across about 15 different fields):

In the end, the data should translate into a graph that looks like this:

Not sure if I've given sufficient info, please let me know if I am omitting something. Thanks a ton in advance!

 

[Robert Mika]

The same layout as before (your bottom tanbe in Sheet 2)
For Avg Competitor sales
=AVERAGE(IF(Sheet1!$B$2:$B$34=Sheet2!$B2,IF(Sheet1!$E$2:$E$34=Sheet2!C$1,Sheet1!$F$2:$F$34),""))
Copy down and accross
But...
For Bob Jones the Average is 70 for C if we take 80 and 60.
How did you arrive at 121?

Not sure about this part

Full Shop Branding

How did you come for 1 Bob Jones ,C?

 

[chrischew2]

Hi Robert,

The 121 comes by summing all the averages of all dealers with C. So by individual dealers (based on my quick averageif formula) it would be:

DEALER 1 - 70
DEALER 2 - 6
DEALER 3 - 6
DEALER 4 - 36
DEALER 5 - 4

Adding it all up, it would be 121.

For full shop branding, the 1 comes because in Bob Jones's case, he only has one dealer (Dealer 1) who registered the full shop branding as C. He also has one for M (Dealer 3), one for D (Dealer 4) and two for O (Dealer 2 and 5). So in total he has visited 5 dealers, so the sum of J32:M32 should be 5. Same applies for the rest.

 

[chrischew2]

Hello,

Quick bump for this, hoping someone can help me with the second query?

 

[chrischew2]

Hi again,

Is anyone able to help me on my second query about counting unique data that isn't input as 1s and 0s, but as letters or large numbers instead? Would really appreciate any help, thanks.

 

[Aladin Akyurek]

Hi again,

Is anyone able to help me on my second query about counting unique data that isn't input as 1s and 0s, but as letters or large numbers instead? Would really appreciate any help, thanks.

Would you create a tiny sample along with the desired result for that sample?

 

[chrischew2]

Hello,

Sure thing, the following table is a sample of the data:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Visitation Date	Sales Representative	Sales Supervisor	Dealer Name	Overall full shop branding	Competitor C sales	Competitor O sales	Competitor D sales	Competitor M sales
2	05/07/2011	BOB JONES	AGNES COLE	DEALER 1	C	80	0	0	0
3	05/07/2011	BOB JONES	AGNES COLE	DEALER 2	O	7	3	2	0
4	05/07/2011	BOB JONES	AGNES COLE	DEALER 3	M	5	2	0	0
5	05/07/2011	BOB JONES	AGNES COLE	DEALER 4	D	1	60	0	30
6	06/07/2011	BOB JONES	AGNES COLE	DEALER 2	O	0	0	0	10
7	06/07/2011	BOB JONES	AGNES COLE	DEALER 3	M	7	0	20	0
8	06/07/2011	BOB JONES	AGNES COLE	DEALER 4	D	70	20	10	0
9	07/07/2011	BOB JONES	AGNES COLE	DEALER 5	O	4	0	60	0
10	07/07/2011	BOB JONES	AGNES COLE	DEALER 1	C	60	0	0	0
11	07/07/2011	BOB JONES	AGNES COLE	DEALER 2	O	10	0	0	0
12	05/07/2011	SAM SMITH	AGNES COLE	DEALER A	C	35	10	5	0
13	05/07/2011	SAM SMITH	AGNES COLE	DEALER B	O	0	5	5	5
14	05/07/2011	SAM SMITH	AGNES COLE	DEALER C	C	3	15	5	5
15	06/07/2011	SAM SMITH	AGNES COLE	DEALER C	C	25	0	50	0
16	06/07/2011	SAM SMITH	AGNES COLE	DEALER B	O	0	0	0	0
17	06/07/2011	SAM SMITH	AGNES COLE	DEALER A	C	50	10	10	30
18	06/07/2011	SAM SMITH	AGNES COLE	DEALER D	M	4	0	0	0
19	07/07/2011	SAM SMITH	AGNES COLE	DEALER E	M	0	10	30	0
20	07/07/2011	SAM SMITH	AGNES COLE	DEALER B	O	2	0	0	20
21	07/07/2011	SAM SMITH	AGNES COLE	DEALER C	C	0	15	10	0
22	07/07/2011	SAM SMITH	AGNES COLE	DEALER A	C	20	10	10	20
23	05/07/2011	MIKE JAMES	AGNES COLE	DEALER X	C	2	0	0	0
24	05/07/2011	MIKE JAMES	AGNES COLE	DEALER Y	O	40	5	0	0
25	05/07/2011	MIKE JAMES	AGNES COLE	DEALER Z	C	35	0	0	0
26	05/07/2011	MIKE JAMES	AGNES COLE	DEALER W	D	26	40	0	0
27	05/07/2011	MIKE JAMES	AGNES COLE	DEALER V	M	60	40	100	10
28	06/07/2011	MIKE JAMES	AGNES COLE	DEALER X	C	70	80	0	10
29	06/07/2011	MIKE JAMES	AGNES COLE	DEALER Z	C	150	20	0	25
30	06/07/2011	MIKE JAMES	AGNES COLE	DEALER X	C	12	0	0	0
31	06/07/2011	MIKE JAMES	AGNES COLE	DEALER U	C	0	20	0	0
32	07/07/2011	MIKE JAMES	AGNES COLE	DEALER Y	O	20	0	0	0
33	07/07/2011	MIKE JAMES	AGNES COLE	DEALER Z	C	50	0	0	0
34	07/07/2011	MIKE JAMES	AGNES COLE	DEALER X	C	5	0	0	0
35	07/07/2011	MIKE JAMES	AGNES COLE	DEALER V	M	52	0	0	0
Sheet1

And the following table is the ideal result, which I'd prefer to appear as a pivot, so that users can double click the cells and identify specific dealers:

Excel Workbook
	C	D	E	F	G
23	*	Avg Competitor sales
24	SALES REP	C	D	M	O
25	BOB JONES	122	76	18	42
26	MIKE JAMES	213	50	16	109
27	SAM SMITH	49	62	27	32
28	*	*	*	*	*
29	*	*	*	*	*
30	*	Full Shop Branding
31	SALES REP	C	D	M	O
32	BOB JONES	1	1	1	2
33	MIKE JAMES	3	1	1	1
34	SAM SMITH	2	0	1	2
Sheet3

So this means ideally, I'm looking for additional columns to the first table, so I can pivot that table and create the results table, which in turn can be used to generate charts.

 

[Aladin Akyurek]

Is this a small sample?...

OK, how did you calculate 122 for Bob Jones under C?

 

[chrischew2]

Haha sorry, wasn't sure how else to display a proper range of data...

Ok I got 122 for Bob Jones by adding up all the averages of all dealers that he handles in column F. So by individual dealers it would be:

DEALER 1 - 70 ((F2 + F10)/2)
DEALER 2 - 6 ((F3+F6+F11)/3)
DEALER 3 - 6 ((F4+F7)/2)
DEALER 4 - 36 ((F5+F8/2)
DEALER 5 - 4 (F9/1)

Adding it all up, it would be 122.

 

[Robert Mika]

Unless Aladin will come up with a formula which is adding up 5 Averages without using "+" ,please see attached spreahseet.
This is a work around but it works.
Create a pivot table (column K:O)with averages for all competitors columns:

and then use formula in the table.

Formula in T2 copy down an accross:
=ROUNDUP(SUM(INDEX(L$1:L$100,MATCH($S2,$K$1:$K$100,0)):INDEX(L$1:L$100,MATCH($S3,$K$1:$K$100,0)-1)),0)

(array enteed confirm CTRL+SHIFT+ENTER)

 

[chrischew2]

Ah ok, will give that a try.

Any ideas on getting the Full Shop Branding results?