Formula for unique value(text) Need assistance is setting up a formula in Column
ID: 3563611 • Letter: F
Question
Formula for unique value(text)
Need assistance is setting up a formula in Column C that will return the # of unique suppliers from data in Column B, based on information in Column A. Column A shows an ingredient number that may appear more than once. Need number of unique suppliers for each unique ingredient number (i.e. 4051 appears 2 times in Column A should return 2 for number of suppliers, whereas 4562 appears 2 times with same supplier should return 1 and so on. The list is over a thousand line items. Can you help.
Example:
MRP
Supplier
# of Suppliers
1203
Kalsec, Inc.
3800
Kalsec, Inc.
3802
Kalsec, Inc.
3850
Kalsec, Inc.
4051
DSM Nutritional Products Inc (formerly Roche)
4051
Roche Vitamins Inc.
4052
DSM Nutritional Products Inc (formerly Roche)
4052
Roche Vitamins Inc.
4562
McCormick and Co., Inc.
4562
McCormick and Co., Inc.
4747
Con-Agra
4747
FDP USA Inc a BCFoods Company
MRP
Supplier
# of Suppliers
1203
Kalsec, Inc.
3800
Kalsec, Inc.
3802
Kalsec, Inc.
3850
Kalsec, Inc.
4051
DSM Nutritional Products Inc (formerly Roche)
4051
Roche Vitamins Inc.
4052
DSM Nutritional Products Inc (formerly Roche)
4052
Roche Vitamins Inc.
4562
McCormick and Co., Inc.
4562
McCormick and Co., Inc.
4747
Con-Agra
4747
FDP USA Inc a BCFoods Company
4747 Silva International Inc. 4747 WorleeExplanation / Answer
Hi,
Put this ARRAY formula in C2. Array enter it; see below for how, and drag down.
=COUNT(1/FREQUENCY(IF($A$1:$A$1000=A2,IF($B$1:$B$1000<>"",MATCH($B$1:$B$1000,$B$1:$B$1000,0))),ROW($B$1:$B$1000)-ROW($B$1)+1))
This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.