Solved

Need unique count of customer name to reseller name formula ASAP!!!

Posted on 2011-03-09
20
459 Views
Last Modified: 2012-06-21
I have a large file of customers and resellers and need unique count of company name for the resellers.

Reseller      Customer
SAR Inc       McDonalds
SAR Inc       Burger King
SAR Inc       Wendy's
SAR Inc        Pizza Hut
PAUL DOT    Macy's
PAUL DOT    McDonalds
PAUL DOT    Wendy's

How do I show that SAR truly has 2 unique companies and PAUL DOT has 1 Unique company.

I gave a simple example but I have 3K resellers tied to numerous companies.

Any quick help will be appreciated!!!
0
Comment
Question by:glabossi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 5
  • +2
20 Comments
 
LVL 33

Assisted Solution

by:jppinto
jppinto earned 167 total points
ID: 35085341
I've used a 3rd column on my attached example to count how many times the Customer appears and then I've made a Pivot Table where I filter only the ones where the Count is like 1. This will give you the number of unique Customer that each Reseller has.

Hope this is clear to you.

jppinto
CountUniques.xlsx
0
 

Author Comment

by:glabossi
ID: 35086020
This almost worked.  But it is still not giving it.  I have some of them that are 1, but the rest are more than one.  The example below should show a unique count of 1 for this reseller and it shows two.

Reseller   Customer                        Unique Count
INSIGHT       1252 Properties, Inc.      2
INSIGHT       1252 Properties, Inc.      2
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35086066
You didn't mentioned that you had duplicate on your data... let me see now.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Expert Comment

by:barry houdini
ID: 35086369
Hello glabossi,

If you have a reseller name in D3 then you can use this formula for a count of different customers, see attached.

=SUMPRODUCT((MATCH(A$2:A$8&"-"&B$2:B$8,A$2:A$8&"-"&B$2:B$8,0)=ROW(A$2:A$8)-ROW(A$2)+1)*(A$2:A$8=D3))

regards, barry
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35086380
Here's the attachment......

barry
26874647.xlsx
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35086428
It's not that simple Barry...he wants unique costumers that are only from that reseller! That's the problem...
0
 
LVL 81

Expert Comment

by:byundt
ID: 35086500
In jppinto's sample workbook, you can use the formula for the count of unique customers of a reseller:
=SUMPRODUCT((A$2:A$90=F4)/((A$2:A$90<>F4)+COUNTIFS(A$2:A$90,F4,B$2:B$90,B$2:B$90)))

The reference to row 90 is arbitrary, and may extend beyond the data. The reference to cell F4 is to the reseller's name.
0
 
LVL 81

Expert Comment

by:byundt
ID: 35086585
Barry's formula works as requested (and tolerates blank cells to boot)--but mine is shorter for a change.

Here are both formulas in Barry's sample workbook:
26874647.xlsx
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35086588
I'm I the only one that understood the question or the only one that didn't?!?

It's my understanding that the user wants unique customers of a reseller that are not customers of other resellers, right?

jppinto
0
 

Expert Comment

by:darcy_morse
ID: 35086589
use a countif on the customer for the same column.  That will give the number of customers in that column.  Then filter on the count field, and filter for the quantity of 1.  This will display the reseller and unique customer.
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35086625
@darcy_morse: The problem is when there are duplicate resellers+customers! If there wheren't duplicates, my fisrt solution would work!
0
 

Author Comment

by:glabossi
ID: 35086651
jppinto is correct.  My original posts did not include duplicates but the formula needs to account for it.

0
 

Expert Comment

by:darcy_morse
ID: 35086783
col A             Col B               col C                                               Col D
Reseller        Customer     = concatenate(A1,"-",B1)        =countif(B:B,B1) - countif(C:C,C1)

Column D searches for number of customers and sutracts number of reseller customer combinations.  From there you can look where column D = 0.



0
 
LVL 81

Expert Comment

by:byundt
ID: 35086837
To get the counts of 2 and 1, you might use:
=SUMPRODUCT((A$2:A$90=D3)*(COUNTIF(B$2:B$90,B$2:B$90)=1))
26874647.xlsx
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35086864
Sorry glabossi, I misunderstood the requirement, sounds like you understood it better jp!

Here's a formula solution

=SUM(IF(FREQUENCY(IF(COUNTIFS(A$2:A$10,"<>"&D2,B$2:B$10,B$2:B$10)=0,IF(B$2:B$10<>"",MATCH(B$2:B$10,B$2:B$10,0))),ROW(B$2:B$10)-ROW(B$2)+1),1))

That's an array formula that needs to be confirmed with CTRL+SHIFT+ENTER

where D2 contains the reseller name

see attached example

regards, barry
26874647v2.xlsx
0
 
LVL 81

Expert Comment

by:byundt
ID: 35087075
If there might be duplicates, then you get 2 and 1 with:
=SUMPRODUCT((COUNTIF(B$2:B$9,B$2:B$9)=COUNTIFS(A$2:A$9,D3,B$2:B$9,B$2:B$9))/((COUNTIF(B$2:B$9,B$2:B$9)<>COUNTIFS(A$2:A$9,D3,B$2:B$9,B$2:B$9))+COUNTIFS(A$2:A$9,D3,B$2:B$9,B$2:B$9)))
26874647.xlsx
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35087637
just to clarify, then - I think the last formula suggested by Brad will give the same results as the last formula that I suggested - my version also allows blank rows......

regards, barry
0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 166 total points
ID: 35087912
I agree that Barry's formula works for the duplicate possibility and tolerates blank rows. Here is a somewhat shorter one that handles duplicates but doesn't tolerate the blanks:
=SUM(IF(COUNTIF(B$2:B$9,B$2:B$9)=COUNTIFS(A$2:A$9,D3,B$2:B$9,B$2:B$9),1/COUNTIF(B$2:B$9,B$2:B$9),0))

Like Barry's formula, it must be array-entered (CTRL + Shift + Enter).


26874647.xlsx
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 167 total points
ID: 35088365
That looks better, Brad....

I think, though, that you could combine some elements of both for a shorter non-array version, i.e.

=SUMPRODUCT((COUNTIFS(A$2:A$9,"<>"&D3,B$2:B$9,B$2:B$9)=0)/COUNTIF(B$2:B$9,B$2:B$9))

confirmed with CTRL+SHIFT+ENTER

That doesn't allow blanks - if you want to allow those then try

=SUMPRODUCT((COUNTIFS(A$2:A$9,"<>"&D3,B$2:B$9,B$2:B$9)=0)*(B$2:B$9<>"")/COUNTIF(B$2:B$9,B$2:B$9&""))

regards, barry
0
 
LVL 81

Expert Comment

by:byundt
ID: 35089231
Barry,
Nicely done!

Brad
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question