Formula for unique value(text) Need assistance is setting up a formula in Coolum
ID: 3564717 • Letter: F
Question
Formula for unique value(text) Need assistance is setting up a formula in Coolumn 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 eacch unique ingredient numbeer (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 linee 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 4747 Silva International Inc. 4747 Worlee
Explanation / 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 formulaa which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you ddo it correctly then Excel will put curlyy brackets
'around thee formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter...