Counting unique data for duplicate customers

C

chrischew2

New Member
Joined
Aug 5, 2011
Messages
12
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):

excelproblem.jpg


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

excelproblem2.jpg


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

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Hi Robert,

Oh ok sure thing. Here is my code (hope it comes out, first time trying this):

HTML: 
[RANGE=cls:xl2bb-100][XR][XH=cs:10]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][XH]E[/XH][XH]F[/XH][XH]G[/XH][XH]H[/XH][XH]I[/XH][/XR][XR][XH]1[/XH][XD=h:c|fw:b|bc:99ccff|c:ff0000]Visitation Date[/XD][XD=h:r|fw:b|bc:99ccff|c:ff0000|fz:9pt]Sales Representative[/XD][XD=h:c|fw:b|bc:99ccff|c:ff0000]Sales Supervisor[/XD][XD=h:l|bc:99ccff]Dealer Name[/XD][XD=h:c|fw:b|bc:99ccff|c:ff0000]Dealer ID[/XD][XD=h:c|bc:99ccff]Issue 1[/XD][XD=h:c|bc:99ccff]Issue 2[/XD][XD=h:c|bc:99ccff]Issue 3[/XD][XD=h:c|bc:99ccff]Issue 4[/XD][/XR][XR][XH]2[/XH][XD=h:c|fz:9pt]11/07/2011[/XD][XD=h:r|fz:9pt]SALES REP 1[/XD][XD=h:r|fz:9pt]SUPERVISOR 1[/XD][XD=h:l|fz:9pt]DEALER A[/XD][XD=h:c|fz:9pt]ID 1[/XD][XD=h:c|fz:9pt]1[/XD][XD=h:c|fz:9pt]1[/XD][XD=h:c|fz:9pt]1[/XD][XD=h:c|fz:9pt]1[/XD][/XR][XR][XH]3[/XH][XD=h:c|fz:9pt]18/07/2011[/XD][XD=h:r|fz:9pt]SALES REP 1[/XD][XD=h:r|fz:9pt]SUPERVISOR 1[/XD][XD=h:l|fz:9pt]DEALER A[/XD][XD=h:c|fz:9pt]ID 1[/XD][XD=h:c|fz:9pt]0[/XD][XD=h:c|fz:9pt]0[/XD][XD=h:c|fz:9pt]1[/XD][XD=h:c|fz:9pt]0[/XD][/XR][XR][XH]4[/XH][XD=h:c|fz:9pt]25/07/2011[/XD][XD=h:r|fz:9pt]SALES REP 1[/XD][XD=h:r|fz:9pt]SUPERVISOR 1[/XD][XD=h:l|fz:9pt]DEALER A[/XD][XD=h:c|fz:9pt]ID 1[/XD][XD=h:c|fz:9pt]0[/XD][XD=h:c|fz:9pt]0[/XD][XD=h:c|fz:9pt]1[/XD][XD=h:c|fz:9pt]0[/XD][/XR][XR][XH]5[/XH][XD=h:c|fz:9pt]01/07/2011[/XD][XD=h:r|fz:9pt]SALES REP 2[/XD][XD=h:r|fz:9pt]SUPERVISOR 1[/XD][XD=h:l|fz:9pt]DEALER B[/XD][XD=h:c|fz:9pt]ID 2[/XD][XD=h:c|fz:9pt]0[/XD][XD=h:c|fz:9pt]0[/XD][XD=h:c|fz:9pt]0[/XD][XD=h:c|fz:9pt]1[/XD][/XR][XR][XH]6[/XH][XD=h:c|fz:9pt]06/07/2011[/XD][XD=h:r|fz:9pt]SALES REP 2[/XD][XD=h:r|fz:9pt]SUPERVISOR 1[/XD][XD=h:l|fz:9pt]DEALER B[/XD][XD=h:c|fz:9pt]ID 2[/XD][XD=h:c|fz:9pt]0[/XD][XD=h:c|fz:9pt]1[/XD][XD=h:c|fz:9pt]0[/XD][XD=h:c|fz:9pt]0[/XD][/XR][XR][XH]7[/XH][XD=h:c|fz:9pt]24/07/2011[/XD][XD=h:r|fz:9pt]SALES REP 2[/XD][XD=h:r|fz:9pt]SUPERVISOR 1[/XD][XD=h:l|fz:9pt]DEALER B[/XD][XD=h:c|fz:9pt]ID 2[/XD][XD=h:c|fz:9pt]0[/XD][XD=h:c|fz:9pt]1[/XD][XD=h:c|fz:9pt]1[/XD][XD=h:c|fz:9pt]1[/XD][/XR][XR][XH=cs:10][RANGE][XR][XD]Sheet2[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]
 
Last edited:
Upvote 0
Hi Chris,
This came as a a HTML code not a proper table.
When pasting your code from HTML Maker go to the small box on the right hand corner of the window where you are writing your post.
When you click the small/ big "A" the border shuold vanished and you can post your code.

56972767.jpg
 
Upvote 0
Oh shucks, sorry for that. Ok let me try again:

Excel Workbook
ABCDEFGHI
1Visitation DateSales RepresentativeSales SupervisorDealer NameDealer IDIssue 1Issue 2Issue 3Issue 4
211/07/2011SALES REP 1SUPERVISOR 1DEALER AID 11111
318/07/2011SALES REP 1SUPERVISOR 1DEALER AID 10010
425/07/2011SALES REP 1SUPERVISOR 1DEALER AID 10010
501/07/2011SALES REP 2SUPERVISOR 1DEALER BID 20001
606/07/2011SALES REP 2SUPERVISOR 1DEALER BID 20100
724/07/2011SALES REP 2SUPERVISOR 1DEALER BID 20111
Sheet2
 
Upvote 0
Hi again,

Sorry I didn't post my desired results. Here's what I'd like:

Excel Workbook
ABCDE
1Sales RepsIssue 1Issue 2Issue 3Issue 4
2SALES REP 11111
3SALES REP 20111
Sheet4


This means that even though Dealer A logged issue 3 as an issue in all three visits, it will still only be counted as 1. Same goes for Dealer B who logged issue 2 twice and issue 4 twice, but I want to only capture it as once for each issue.
 
Upvote 0
try this idea
Excel Workbook
ABCDEFGHI
1Visitation DateSales RepresentativeSales SupervisorDealer NameDealer IDIssue 1Issue 2Issue 3Issue 4
211/07/2011SALES REP 1SUPERVISOR 1DEALER AID 11111
318/07/2011SALES REP 1SUPERVISOR 1DEALER AID 10010
425/07/2011SALES REP 1SUPERVISOR 1DEALER AID 10010
501/07/2011SALES REP 2SUPERVISOR 1DEALER BID 20001
606/07/2011SALES REP 2SUPERVISOR 1DEALER BID 20100
724/07/2011SALES REP 2SUPERVISOR 1DEALER BID 20111
8
9
10Sales RepsIssue 1Issue 2Issue 3Issue 4
11SALES REP 11111
12SALES REP 20111
Sheet1
Excel 2007
Cell Formulas
RangeFormula
B11=IF(COUNTIFS($B$2:$B$7,$A11,F$2:F$7,1),1,0)
 
Upvote 0
Hi Yahya,

Thanks lots, tried that but it doesn't seem to work if I have multiple dealers. Perhaps if I displayed a larger dataset:

Excel Workbook
ABCDEFGH
1Visitation DateSales RepresentativeSales SupervisorDealer NameNetworkIncentiveITCustomer Service
205/07/2011BOB JONESAGNES COLEDEALER 10111
305/07/2011BOB JONESAGNES COLEDEALER 21011
405/07/2011BOB JONESAGNES COLEDEALER 31101
505/07/2011BOB JONESAGNES COLEDEALER 41000
606/07/2011BOB JONESAGNES COLEDEALER 21000
706/07/2011BOB JONESAGNES COLEDEALER 31000
806/07/2011BOB JONESAGNES COLEDEALER 41111
907/07/2011BOB JONESAGNES COLEDEALER 50001
1007/07/2011BOB JONESAGNES COLEDEALER 11111
1107/07/2011BOB JONESAGNES COLEDEALER 21001
1205/07/2011SAM SMITHAGNES COLEDEALER A1001
1305/07/2011SAM SMITHAGNES COLEDEALER B1000
1405/07/2011SAM SMITHAGNES COLEDEALER C1000
1506/07/2011SAM SMITHAGNES COLEDEALER C1000
1606/07/2011SAM SMITHAGNES COLEDEALER B1000
1706/07/2011SAM SMITHAGNES COLEDEALER A1001
1806/07/2011SAM SMITHAGNES COLEDEALER D1000
1907/07/2011SAM SMITHAGNES COLEDEALER E1000
2007/07/2011SAM SMITHAGNES COLEDEALER B1000
2107/07/2011SAM SMITHAGNES COLEDEALER C1000
2207/07/2011SAM SMITHAGNES COLEDEALER A1000
2305/07/2011MIKE JAMESAGNES COLEDEALER X1000
2405/07/2011MIKE JAMESAGNES COLEDEALER Y1111
2505/07/2011MIKE JAMESAGNES COLEDEALER Z1100
2605/07/2011MIKE JAMESAGNES COLEDEALER W1111
2705/07/2011MIKE JAMESAGNES COLEDEALER V0000
2806/07/2011MIKE JAMESAGNES COLEDEALER X0111
2906/07/2011MIKE JAMESAGNES COLEDEALER Z1111
3006/07/2011MIKE JAMESAGNES COLEDEALER X1011
3106/07/2011MIKE JAMESAGNES COLEDEALER U1100
3207/07/2011MIKE JAMESAGNES COLEDEALER Y1111
3307/07/2011MIKE JAMESAGNES COLEDEALER Z1110
3407/07/2011MIKE JAMESAGNES COLEDEALER X0101
3507/07/2011MIKE JAMESAGNES COLEDEALER V0010
Sheet2




Ideally I'd like the results to appear in pivot table format so that supervisors can double click on selected cells and identify precisely which dealers have such issues. I know it involves adding some helper columns, but not sure how to isolate the unique dealer counts to appear as such:

Excel Workbook
ABCDEF
1Result (ideally as a pivot table)SALES REPNETWORKINCENTIVEITCUSTOMER SERVICE
2*BOB JONES4335
3*MIKE JAMES5554
4*SAM SMITH5002
Sheet3
 
Upvote 0
Try this.

In sheet2 cell B3 copy down and accross confirm CTRL+SHIFT+ENTER

=COUNT(1/FREQUENCY(IF(Sheet1!$B$1:$B$35=$B2,IF(Sheet1!E$1:E$35=1,MATCH(Sheet1!$D$1:$D$35,Sheet1!$D$1:$D$35,0))),ROW(Sheet1!$A$1:$A$35)-ROW(Sheet1!Z1)+1))


You can then create pivot table from this data.
 
Upvote 0
Hi Robert,

That works fantastically actually. Thanks a ton. I guess my only worry is whether all the formulas will make the actual database file massive (the actual database captures about 35 different datasets). But I will try it anyways.

Just got two more situations, similar concept yet slightly different data. How could I get the same results if (1) the data entered was letters (C,M,D,O) instead of 1 and 0, or (2) the data entered was in larger quantities instead of 1 and 0, and I wanted a average of the quantities across a month.

Here's the example of a table for the above:

Excel Workbook
ABCDEFGHI
1Visitation DateSales RepresentativeSales SupervisorDealer NameOverall full shop brandingCompetitor C salesCompetitor O salesCompetitor D salesCompetitor M sales
205/07/2011BOB JONESAGNES COLEDEALER 1C80000
305/07/2011BOB JONESAGNES COLEDEALER 2O7320
405/07/2011BOB JONESAGNES COLEDEALER 3M5200
505/07/2011BOB JONESAGNES COLEDEALER 4D160030
606/07/2011BOB JONESAGNES COLEDEALER 2O00010
706/07/2011BOB JONESAGNES COLEDEALER 3M70200
806/07/2011BOB JONESAGNES COLEDEALER 4D7020100
907/07/2011BOB JONESAGNES COLEDEALER 5O40600
1007/07/2011BOB JONESAGNES COLEDEALER 1C60000
1107/07/2011BOB JONESAGNES COLEDEALER 2O10000
1205/07/2011SAM SMITHAGNES COLEDEALER AC351050
1305/07/2011SAM SMITHAGNES COLEDEALER BO0555
1405/07/2011SAM SMITHAGNES COLEDEALER CC31555
1506/07/2011SAM SMITHAGNES COLEDEALER CC250500
1606/07/2011SAM SMITHAGNES COLEDEALER BO0000
1706/07/2011SAM SMITHAGNES COLEDEALER AC50101030
1806/07/2011SAM SMITHAGNES COLEDEALER DM4000
1907/07/2011SAM SMITHAGNES COLEDEALER EM010300
2007/07/2011SAM SMITHAGNES COLEDEALER BO20020
2107/07/2011SAM SMITHAGNES COLEDEALER CC015100
2207/07/2011SAM SMITHAGNES COLEDEALER AC20101020
2305/07/2011MIKE JAMESAGNES COLEDEALER XC2000
2405/07/2011MIKE JAMESAGNES COLEDEALER YO40500
2505/07/2011MIKE JAMESAGNES COLEDEALER ZC35000
2605/07/2011MIKE JAMESAGNES COLEDEALER WD264000
2705/07/2011MIKE JAMESAGNES COLEDEALER VM604010010
2806/07/2011MIKE JAMESAGNES COLEDEALER XC7080010
2906/07/2011MIKE JAMESAGNES COLEDEALER ZC15020025
3006/07/2011MIKE JAMESAGNES COLEDEALER XC12000
3106/07/2011MIKE JAMESAGNES COLEDEALER UC02000
3207/07/2011MIKE JAMESAGNES COLEDEALER YO20000
3307/07/2011MIKE JAMESAGNES COLEDEALER ZC50000
3407/07/2011MIKE JAMESAGNES COLEDEALER XC5000
3507/07/2011MIKE JAMESAGNES COLEDEALER VM52000
Sheet1


And the desired result should be like this (again ideally as a pivot):

Excel Workbook
IJKLM
23*Avg Competitor sales
24SALES REPCDMO
25BOB JONES121761842
26MIKE JAMES2135016109
27SAM SMITH49622732
28*****
29*****
30*Full Shop Branding
31SALES REPCDMO
32BOB JONES1112
33MIKE JAMES3111
34SAM SMITH2012
Sheet3


Really appreciate all the help (and Excel lessons) I'm getting here :cool:
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Count Unique Number Values When Data Filtered
2
Replies
10
Views
330
Fluff
Fluff
H
  • Solved
How to make excel count based on time data?
Replies
3
Views
510
Fluff
Fluff
J
  • Question Question
Pulling Data From ~300 Individual Sheets Onto a Master Sheet
Replies
1
Views
192
ranman256
ranman256
O
  • Solved
Counting instances of unique entries, with conditions
Replies
1
Views
582
ollyhughes1982
O
N
  • Question Question
2-step Dynamic Ranges and Charts
Replies
0
Views
1K
Nick Loughton
N

Forum statistics

Threads
1,224,581
Messages
6,179,668
Members
452,936
Latest member
anamikabhargaw

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