I did a google scripting solution because I wanted to play with key map pairs.
function myFunction() {
var myMap = {"candy":0};
var sh = SpreadsheetApp.getActiveSpreadsheet();
var ss = sh.getSheetByName("FIRSTSHEETNAME");
var os = sh.getSheetByName("Ingredients");
var data = ss.getDataRange().getValues();
for (var i=0; i<data.length;i++)//full
//for (var i=1; i<4000;i++)//test
{
var array = data[i][0].split( ",");
for (var j=0; j<array.length;j++)
{
var item = array[j];
//Logger.log(array[j]);
if (myMap[item]>-1){
//Logger.log("REPEAT INGREDIENT");
var num = parseInt(myMap[item]);
num++;
myMap[item]=num;
//Logger.log(item +" "+num);
} else {
myMap[item]=1;
//Logger.log("New Ingredient: "+item);
//Logger.log(myMap);
}
}
}
//Logger.log(myMap);
var output=[];
for (var key in myMap){
//Logger.log("Ack");
output.push([key,myMap[key]]);
}
//Logger.log(output);
os.getRange(2,1,output.length,output[0].length).setValues(output);
}
You'll need to add an "Ingredients" tab for the output and change your first tab to be called FIRSTSHEETNAME (or change the code). In my testing it took 4 seconds for 4 items, 5 seconds for 400 items, and 6 seconds for 4000 items. there might be an issue with leading spaces but this gives you a place to start.
