Comma separated search value matching

[tommsegers]

Hi everyone

I have a Sheet containing a column which contains comma separated values containing locations and a value

Item ID	Location results
112	Location A :1;Location B:3;Location C:4;Location D:1
113	Location A :5;Location F:1;Location X:9;Location Z:1
116	Location AA:3

On a new sheet I have a list of all available locations and need to generate a table that looks like showing the value after the : of that specific location.
And preferable also adds the columns if the ID exists so i don't have to manually create every column header

Location	112	113	116
Location A	1	5	0
Location B	3	0	0
Location AA	0	0	3

Another issue is that the Location names need to be an exact match as some names contain similar words like "Brussels North", "Brussels", "Antwerp North"

I have tried Search formulas without success so far

Thank you
Tom

 

[Fluff]

Hi & welcome to MrExcel.
Could the Item ID appear multiple times in the data, or just once?

 

[tommsegers]

Thanks!
The Item ID is unique in the data set

 

[Fluff]

Ok, how about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Item ID	Location results			112	113	116
2	112	Location A :1;Location B:3;Location C:4;Location D:1		Location A	1	5
3	113	Location A :5;Location F:1;Location X:9;Location Z:1		Location B	3
4	116	Location AA:3		Location AA			3
5				Location B	3
6				Location C	4
7				Location D	1
Data

Cell Formulas
Range	Formula
E1:G1	E1	=TRANSPOSE(UNIQUE(FILTER(A2:A100,A2:A100<>"")))
E2:G7	E2	=IFNA(VLOOKUP($D$2:$D$7,TRIM(TEXTSPLIT(INDEX($B$2:$B$100,MATCH(E1,$A$2:$A$100,0)),":",";")),2,0),"")
Dynamic array formulas.

 

[tommsegers]

Wow it works! This saves me a lot of manual hours! Thanks a lot

It works in Excel , unfortunately not in Google Sheets? As I have some datastudio reports hooked up to the data would be even greater to have it also work in Gsheet, i imagined the same formula would work like some basic formulas

 

[Fluff]

I doubt that you would get a formula that will work in both Xl & Sheets, as there are a lot of differences between them.

 

[tommsegers]

Excel now seems to see the result as TEXT instead of numbers so i can't use the info yet that has been split. Any way to convert this in the formula to numbers?

Tom

 

[Fluff]

xcel now seems to see the result as TEXT instead of numbers

That's because they are text, try

=IFNA(VLOOKUP($D$2:$D$7,TRIM(TEXTSPLIT(INDEX($B$2:$B$100,MATCH(E1,$A$2:$A$100,0)),":",";")),2,0)+0,"")

 

[tommsegers]

Wow this is amazing, i don't know how much i can thank you for this!

 

[Fluff]

You're welcome & thanks for the feedback.