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

DCard

New Member
Joined
Feb 14, 2011
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
Creating number comma (or semi-colon)-delimited values outputfrom separate look-up table and commas/semi-colon text values
1589251245024.png


1589251836853.png


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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
754
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQ
1Category_noCategory_nameWordCategories_ManualCategories_CodesHelper1Helper2Helper3Helper4Helper5Helper6Helper7Helper8Helper9Helper10
2100001*Generalaarticle,bathroom100010 100010        
3100002*AdjectivesAbandonverb,bedroom100003,100011100003100011        
4100003*AdverbsAbbrreviationnoun,time100008100008         
5100004*ConjunctionAbdomennou,travel100008100008         
6100005*DeterminerAbilitynoun,tools;cosmetics100008,100019,100020100008100019100020       
7100006*InterjectionsAdjectadjective100002100002         
8100007*PrepositionsAbleverb;bedroom100003,100011100003100011        
9100008*PronounAbnormalitynoun100008100008         
10100009*VerbsAbnormallyadverb100003100003         
11100010Bathroom
12100011Bedroom
13100012Body
14100013Business
15100014Cars
16100015Christmas & Birthdays
17100016Colours
18100017Cooking
19100018Cooking Ingredients
20100019Cooking Tools
21100020Cosmetics
22100021Countries
23100022Drinking
Sheet1
Cell Formulas
RangeFormula
H2:Q10H2=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:F10F2=TEXTJOIN(",",TRUE,H2:N2)
 

DCard

New Member
Joined
Feb 14, 2011
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Feb 14, 2011
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
PS i need to clean up some inconsistencies too :)
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
754
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Feb 14, 2011
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,154
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

  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

New Member
Joined
Feb 14, 2011
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
=(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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,154
Office Version
  1. 365
Platform
  1. Windows
Thanks for updating your account details. (y)

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)?
 

Watch MrExcel Video

Forum statistics

Threads
1,133,243
Messages
5,657,576
Members
418,401
Latest member
B_A_M155

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
Top