Count duplicates and count duplicates based on criteria

[leterrier]

Products	Suppliers
doors	Harper
windows	Smith
floors	Brown
lights	Smith
tables	Johnson
chairs	Jones
beds	Cooper
sinks	Jones
baths
sofas	Smith

<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.

 

[leterrier]

Any ideas anyone? Thanks

 

[Peter_SSs]

If you are using Excel 2010 or later, try these two formulas, copied down.

Excel Workbook
	A	B	C	D	E
1	Products	Suppliers		Multiples	Count
2	doors	Harper		Smith	3
3	windows	Smith		Jones	2
4	floors	Brown
5	lights	Smith
6	tables	Johnson
7	chairs	Jones
8	beds	Cooper
9	sinks	Jones
10	baths
11	sofas	Smith
12
Duplicates

 

[Peter_SSs]

... alternatively, if you don't mind using a helper column.

C1 is blank or houses a zero.

C2:E2 copied down

Excel Workbook
	A	B	C	D	E
1	Products	Suppliers		Multiples	Count
2	doors	Harper	0	Smith	3
3	windows	Smith	1	Jones	2
4	floors	Brown	1
5	lights	Smith	1
6	tables	Johnson	1
7	chairs	Jones	2
8	beds	Cooper	2
9	sinks	Jones	2
10	baths		2
11	sofas	Smith	2
12
Duplicates (2)

 

[leterrier]

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]

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 Labels	Count of Products
Brown	1
Cooper	1
Harper	1
Johnson	1
Jones	2
Smith	3
(blank)	1
Grand Total	10

<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>

 

[Aladin Akyurek]

Any ideas anyone? Thanks

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(B3:B12<>"",MATCH(B3:B12,B3:B12,0)),ROW(B3:B12)-ROW(B3)+1)>1,1))

 

[Peter_SSs]

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
	A	B	C	D	E	F
1
2	Products	Suppliers		Multiples	Count	No. of Multiple Names
3	doors	Harper		Smith	3	2
4	windows	Smith		Jones	2
5	floors	Brown
6	lights	Smith
7	tables	Johnson
8	chairs	Jones
9	beds	Cooper
10	sinks	Jones
11	baths
12	sofas	Smith
13
Duplicates

 

[leterrier]

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]

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

	A	B	C	D	E	F
1
2	Products	Suppliers		Multiples	Count	No. of Multiple Names
3	doors	Harper		Smith	3	2
4	windows	Smith		Jones	2
5	floors	Brown
6	lights	Smith
7	tables	Johnson
8	chairs	Jones
9	beds	Cooper
10	sinks	Jones
11	baths
12	sofas	Smith
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

Cell Formula F3 =COUNT(E3:E12) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4