Count duplicates and count duplicates based on criteria

leterrier

New Member
Joined
Jan 5, 2013
Messages
44
Products
Suppliers
doorsHarper
windowsSmith
floorsBrown
lightsSmith
tablesJohnson
chairsJones
bedsCooper
sinksJones
baths
sofasSmith

<tbody>
</tbody>

Friends, I again request your assistance.

I have 2 columns of sales data, A and B. Range A3:A12 contains unique product names, range B3:B12 contains non-unique (possibly blank) supplier names.

I wish to know how to construct formulas to tell me;

i) how many suppliers provide more than one product i.e. how many duplicates are in col B? The answer is 2 (Smith and Jones)
and;

ii) for each of these duplicates, how many products do they supply? i.e how many times does each duplicate supplier appear? The answers are Smith = 3, Jones = 2.

Thank you sincerely in anticipation.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,650
Office Version
365
Platform
Windows
If you are using Excel 2010 or later, try these two formulas, copied down.

Excel Workbook
ABCDE
1ProductsSuppliersMultiplesCount
2doorsHarperSmith3
3windowsSmithJones2
4floorsBrown
5lightsSmith
6tablesJohnson
7chairsJones
8bedsCooper
9sinksJones
10baths
11sofasSmith
12
Duplicates
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,650
Office Version
365
Platform
Windows
... alternatively, if you don't mind using a helper column.

C1 is blank or houses a zero.

C2:E2 copied down

Excel Workbook
ABCDE
1ProductsSuppliersMultiplesCount
2doorsHarper0Smith3
3windowsSmith1Jones2
4floorsBrown1
5lightsSmith1
6tablesJohnson1
7chairsJones2
8bedsCooper2
9sinksJones2
10baths2
11sofasSmith2
12
Duplicates (2)
 

leterrier

New Member
Joined
Jan 5, 2013
Messages
44
Dear Peter_SSs,

Thank you so much for responding! I must apologise, I did not make myself clear, I need the formula to count rather than list the number of duplicates. Can I have a formula show "2"" as a count for Smith (1) and Jones (1)? has again!
 

liveinhope

Well-known Member
Joined
Dec 16, 2013
Messages
857
Another way to do this is use a pivot table instead of formulas - easier to understand what you are doing

row field = supplier , value field count of product . You can filter the result to show only supplies with more than 1 product

Row LabelsCount of Products
Brown1
Cooper1
Harper1
Johnson1
Jones2
Smith3
(blank)1
Grand Total10
<colgroup><col width="91" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3328;"> <col width="118" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4315;"> <tbody> </tbody>
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,650
Office Version
365
Platform
Windows
Dear Peter_SSs,

Thank you so much for responding! I must apologise, I did not make myself clear, I need the formula to count rather than list the number of duplicates.
You asked two questions. Does not my suggestion exactly answer your second request?
ii) for each of these duplicates, how many products do they supply? i.e how many times does each duplicate supplier appear? The answers are Smith = 3, Jones = 2


I hadn't answered your first request but it is a simple consequence of the second. For either of my previous suggestions just add as below.
(Note that I have now moved data down a row to match your original description.)

Excel Workbook
ABCDEF
1
2ProductsSuppliersMultiplesCountNo. of Multiple Names
3doorsHarperSmith32
4windowsSmithJones2
5floorsBrown
6lightsSmith
7tablesJohnson
8chairsJones
9bedsCooper
10sinksJones
11baths
12sofasSmith
13
Duplicates
 

leterrier

New Member
Joined
Jan 5, 2013
Messages
44
Dear Livinhope,

Thank you for responding For this task a formula is required but thanks for the suggestion, I will make a point of coming back to pivot tables for my next project. Thanks again!
 

leterrier

New Member
Joined
Jan 5, 2013
Messages
44
Dear Peter_SSs,

Thank you again for coming back. Yes, you have indeed answered my questions despite my lack of clarity. If I could ask your indulgence once more, is there a single formula I can place on the sheet that will tell me that 2 suppliers provide more than one product? Though I can make it work with the help you have kindly provided this particular task is really looking for a one ell / formula reply - if that is feasible. Thank you for taking the time to help out a novice.

You asked two questions. Does not my suggestion exactly answer your second request?



I hadn't answered your first request but it is a simple consequence of the second. For either of my previous suggestions just add as below.
(Note that I have now moved data down a row to match your original description.)

Duplicates

ABCDEF
1
2ProductsSuppliers MultiplesCountNo. of Multiple Names
3doorsHarper Smith32
4windowsSmith Jones2
5floorsBrown
6lightsSmith
7tablesJohnson
8chairsJones
9bedsCooper
10sinksJones
11baths
12sofasSmith
13

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 86px;"><col style="width: 80px;"><col style="width: 24px;"><col style="width: 79px;"><col style="width: 80px;"><col style="width: 149px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
F3=COUNT(E3:E12)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 

Forum statistics

Threads
1,085,489
Messages
5,383,958
Members
401,868
Latest member
herbalgirlskincare

Some videos you may like

This Week's Hot Topics

Top