Count Of Locations Using Items

[Nanogirl21]

I am looking to count what locations are using a specific item. I am not sure the best way to do this based on how my data is generated from an external source. I don't like adding sheets/columns if needed. Any suggestions?

CURRENT SET UP
SHEET 1
COL A= Locations (70,000 rows, but only 10 unique values)
COL B = Items used formated as ["Apples","Bananas","Mushrooms"]. There can be up to 30 items listed

SHEET 2
COL A= list of 58 items that can be used.

RESULTS WANTED
SHEET 3
COL A = Same list of 58 items from SHEET 2 COL A
COL B = Location 1 using item in COL A
COL C = Location 2 using item in COL A
COL D = Location 3 using item in COL A
continue same format to COL K that would be Location 10.
**Not all items will have all 10 locations, but all will have at least 1 location

 

[Fluff]

In sheet3 A2 copied down
=Sheet2!A2
B1 copied across
=IFERROR(INDEX(Sheet1!$A2:$A32,MATCH(0,INDEX(COUNTIF($A$1:A1,Sheet1!$A2:$A32),0),0)),"")
B2 copied down & across
=COUNTIFS(Sheet1!$A$2:$A$64,B$1,Sheet1!$B$2:$B$64,"*"&$A2&"*")

 

[Nanogirl21]

In sheet3 A2 copied down
=Sheet2!A2
B1 copied across
=IFERROR(INDEX(Sheet1!$A2:$A32,MATCH(0,INDEX(COUNTIF($A$1:A1,Sheet1!$A2:$A32),0),0)),"")
B2 copied down & across
=COUNTIFS(Sheet1!$A$2:$A$64,B$1,Sheet1!$B$2:$B$64,"*"&$A2&"*")

Perfect. Thank you.

 

[Nanogirl21]

In sheet3 A2 copied down
=Sheet2!A2
B1 copied across
=IFERROR(INDEX(Sheet1!$A2:$A32,MATCH(0,INDEX(COUNTIF($A$1:A1,Sheet1!$A2:$A32),0),0)),"")
B2 copied down & across
=COUNTIFS(Sheet1!$A$2:$A$64,B$1,Sheet1!$B$2:$B$64,"*"&$A2&"*")

Actually, I noticed that it's not counting when only 1 item is used. How would I add/modify for that? Format for 1 item is ["item"]

 

[Fluff]

Works for me.
Can you post some sample data?

 

[Nanogirl21]

Works for me.
Can you post some sample data?

Works for me.
Can you post some sample data?

I was able to figure it out, turns out that wasn't the issue at all. The problem is if part of a item name appears in multiple items both items are counted.


Example:
ITEMS
Banana
Banana Nut

Banana and Banana Nut both is counting as 2 instead of 1 becasue Banana is in both words. I need these to be counted both as 1 even though they both contain the word Banana.

 

[Fluff]

Are the items in sheet1 wrapped in quotes, as shown in your op?

 

[Nanogirl21]

Are the items in sheet1 wrapped in quotes, as shown in your op?

None of my items on Sheet 2 are in quotes.

Some of the items on Sheet 1 are in quotes, but not all.

Sheet 1 Examples
["Banana"]
["Banana", "Apple"]
Banana

 

[Fluff]

In that case I'm afraid I can't help any further.