Creating number comma (or semi-colon)-delimited values outputfrom separate look-up table and commas/semi-colon text values

[DCard]

Creating number comma (or semi-colon)-delimited values outputfrom separate look-up table and commas/semi-colon text values

so i want to manually enter or copy comma (or semi-colon - not both ) text labels in Categories-Manual column, look up text value match in category look-up table and create the relevant comma (or semi-colon codes) in categories-code column

kind regards

don

 

[GraH]

I actually don't know a way without helper columns, so it might not be what you'd like. With helpers, and hoping you'd have the functions in your Excel version, it could work like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Category_no	Category_name		Word	Categories_Manual	Categories_Codes		Helper1	Helper2	Helper3	Helper4	Helper5	Helper6	Helper7	Helper8	Helper9	Helper10
2	100001	*General		a	article,bathroom	100010			100010
3	100002	*Adjectives		Abandon	verb,bedroom	100003,100011		100003	100011
4	100003	*Adverbs		Abbrreviation	noun,time	100008		100008
5	100004	*Conjunction		Abdomen	nou,travel	100008		100008
6	100005	*Determiner		Ability	noun,tools;cosmetics	100008,100019,100020		100008	100019	100020
7	100006	*Interjections		Adject	adjective	100002		100002
8	100007	*Prepositions		Able	verb;bedroom	100003,100011		100003	100011
9	100008	*Pronoun		Abnormality	noun	100008		100008
10	100009	*Verbs		Abnormally	adverb	100003		100003
11	100010	Bathroom
12	100011	Bedroom
13	100012	Body
14	100013	Business
15	100014	Cars
16	100015	Christmas & Birthdays
17	100016	Colours
18	100017	Cooking
19	100018	Cooking Ingredients
20	100019	Cooking Tools
21	100020	Cosmetics
22	100021	Countries
23	100022	Drinking
Sheet1

Cell Formulas
Range	Formula
H2:Q10	H2	=IFERROR(INDEX($A$2:$A$1000,MATCH("*"&(FILTERXML("<Word><b>"&SUBSTITUTE(SUBSTITUTE($E2,";",","),",","</b><b>")&"</b></Word>","/Word/b["&COLUMNS($H$2:H$2)&"]")&"*"),$B$2:$B$1000,0)),"")
F2:F10	F2	=TEXTJOIN(",",TRUE,H2:N2)

 

[DCard]

HI THERE GraH.

well, you made me see a few inconsistencies in my data, and also issue of text not in cat table.

but the major issue with your solution is that it is doing a partial string match for cat type, eg verb = adverb.

i think if we can fix that, it might be a solution we can work with; but it is possibly true that i wanted 'cooking' to match all 'cooking *'.

no. the match becomes much too messy and problematic, so it is much better to do a whole cell match, eg adverb = adverb, verb <> adverb, cooking = cooking, cooking <> cooking ingredients

kindest regards

don

 

[DCard]

PS i need to clean up some inconsistencies too

 

[GraH]

To make the match EXACT remove these blue parts =IFERROR(INDEX($A$2:$A$1000,MATCH("*"&(FILTERXML("<Word><b>"&SUBSTITUTE(SUBSTITUTE($E2,";",","),",","</b><b>")&"</b></Word>","/Word/b["&COLUMNS($H$2:H$2)&"]")&"*"),$B$2:$B$1000,0)),"")
I went for this formula because you have 9 entries starting with "*". So probably removing that form your list entries would be a good thing.

 

[DCard]

hi again

thanks so much.

i have not finished yet, but i realised if i need all those 'helper' columns, then that defeats the purpose - the whole idea was to avoid an arbitrary number of separate columns, one for every separate category

the whole purpose of the comma-delimited col/field was to avoid additional static col/fields that would be mostly never/used/blank - a variable length comma-delimited string in a single col/field is by far the better solution

 

[Peter_SSs]

1. What are the actual expected results for your sample data in post #1?
2. What Excel version are your using? Please update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[DCard]

=(INDEX(Categories!$B$2:$B$100,MATCH(F4,Categories!$A$2:$A$100,0),1)&","&(INDEX(Categories!$B$2:$B$100,MATCH(G4,Categories!$A$2:$A100,0),1)))

this is working fine .... IF there are values in BOTH F4 and G4, but returns NA if EITHER is blank.

i have tried various IFBLANK tests/logic, but cannot get the logic to work

 

[Peter_SSs]

Thanks for updating your account details.

The formula is no help as I don't know what is in F4 or G4 or columns A & B of "Categories".
I tried moving the sample data values around but with just guessing the formula returned #N/A all the time.

Can we have a small set of meaningful, varied sample data from both sheets including the expected results (with XL2BB so we don't have to manually re-type it to test)?

 

[DCard]