Comma separated search value matching

tommsegers

New Member
Joined
Dec 8, 2022
Messages
5
Office Version
  1. 365
Platform
  1. MacOS
Hi everyone

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

Item IDLocation results
112Location A :1;Location B:3;Location C:4;Location D:1
113Location A :5;Location F:1;Location X:9;Location Z:1
116Location 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
Location112113116
Location A150
Location B300
Location AA 003

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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Hi & welcome to MrExcel.
Could the Item ID appear multiple times in the data, or just once?
 
Upvote 0
Ok, how about
Fluff.xlsm
ABCDEFG
1Item IDLocation results112113116
2112Location A :1;Location B:3;Location C:4;Location D:1Location A15 
3113Location A :5;Location F:1;Location X:9;Location Z:1Location B3
4116Location AA:3Location AA3
5Location B3
6Location C4
7Location D1
Data
Cell Formulas
RangeFormula
E1:G1E1=TRANSPOSE(UNIQUE(FILTER(A2:A100,A2:A100<>"")))
E2:G7E2=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.
 
Upvote 0
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
 
Upvote 0
I doubt that you would get a formula that will work in both Xl & Sheets, as there are a lot of differences between them.
 
Upvote 0
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
 
Upvote 0
xcel now seems to see the result as TEXT instead of numbers
That's because they are text, try
Excel Formula:
=IFNA(VLOOKUP($D$2:$D$7,TRIM(TEXTSPLIT(INDEX($B$2:$B$100,MATCH(E1,$A$2:$A$100,0)),":",";")),2,0)+0,"")
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,360
Messages
6,171,631
Members
452,411
Latest member
sprichwort

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top