title: Getting the unique values and counts of them in a column of comma separated values in Google Sheets without using JOIN?
body: <p>I have a column with a bunch of ingredients lists in it. I'm trying to figure out how many times different individual ingredients appear. There are 73,000 rows. The answers on this <a href="https://stackoverflow.com/questions/20715099/getting-the-count-and-unique-values-of-a-column-of-comma-separated-values">question</a> works for a small amount of data in Google Sheets.</p>
  
  <p>Formula is <code>=UNIQUE(TRANSPOSE(SPLIT(JOIN(", ";A2:A);", ";FALSE)))</code></p>
  
  <p>But I've overwhelmed <code>JOIN</code> with more than 50000 characters here. Is there another way to tackle this?</p>
  
  <p>Sheet: <a href="https://docs.google.com/spreadsheets/d/1t0P9hMmVpwhI2IbATmIMjobuALTg8VWhl8-AQaq3zIo/edit?usp=sharing" rel="nofollow noreferrer">https://docs.google.com/spreadsheets/d/1t0P9hMmVpwhI2IbATmIMjobuALTg8VWhl8-AQaq3zIo/edit?usp=sharing</a></p>
  
  <p><a href="https://i.stack.imgur.com/AdBTq.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/AdBTq.png" alt="enter image description here"></a></p>