Jul 09 2020 09:31 AM
Hi! I'm going to analyze the ingredients in different food products. I have put each product on a different row, with each ingredient in a single cell. And some products have more ingredients while some have a few only. How can I create a list showing all the ingredients appearing in the whole spreadsheet and then find the frequency of each ingredient?
Jul 09 2020 10:58 AM
@vvicvicc IF you have the new array functions you should be able to use (based on the ingredients being in the range $B$2:$Z$200):
=UNIQUE($B$2:$Z$200)
note: this will fill down in the column you place the formula
If you (like me) don't have those array functions yet you can use this formula I created:
=IFERROR(OFFSET($A$1,MOD(AGGREGATE(15,7, (ROW($B$2:$Z$200)+100000*COLUMN($B$2:$Z$200))/(--(COUNTIF(AA$2:AA2,$B$2:$Z$200)=0)), 1),100000)-1,INT(AGGREGATE(15,7, (ROW($B$2:$Z$200)+100000*COLUMN($B$2:$Z$200))/(--(COUNTIF(AA$2:AA2,$B$2:$Z$200)=0)), 1)/100000)-1),"")
also assuming ingredients being in the range $B$2:$Z$200 and you paste this in AA2 and fill down
Once you have the above UNIQUE list you can use COUNTIF() in the next column (col AB in this case):
=COUNTIF($B$2:$Z$200,AA2)
Jul 10 2020 08:18 AM
@mtarler thank you so much !!!! it took me days to find the solution!!