Make formula for the below detail

%3CLINGO-SUB%20id%3D%22lingo-sub-2620810%22%20slang%3D%22en-US%22%3EMake%20formula%20for%20the%20below%20detail%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2620810%22%20slang%3D%22en-US%22%3E%3CTABLE%20width%3D%22438%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2265%22%3E%3CSTRONG%3ECategory%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22373%22%3E%3CSTRONG%3EDriving%20Authority%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA%3C%2FTD%3E%3CTD%3EMotorcycle%2C%20Moped%2C%20Scooter%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EB%3C%2FTD%3E%3CTD%3ECar%2C%20jeep%2C%20Delivery%20Van%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3ETempo%2C%20Auto%20Rickshaw%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EC1%3C%2FTD%3E%3CTD%3EE-Rickshaw%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ED%3C%2FTD%3E%3CTD%3EPower%20Tiller%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EE%3C%2FTD%3E%3CTD%3ETractor%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EF%3C%2FTD%3E%3CTD%3EMinibus%2C%20Minitruck%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EG%3C%2FTD%3E%3CTD%3ETruck%2C%20Bus%2C%20Lorry%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EH%3C%2FTD%3E%3CTD%3ERoad%20Roller%2C%20Dozer%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EH1%3C%2FTD%3E%3CTD%3EDozer%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EH2%3C%2FTD%3E%3CTD%3ERoad%20Roller%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EI%3C%2FTD%3E%3CTD%3ECrane%2C%20Fire%20Brigade%2C%20Loader%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EI1%3C%2FTD%3E%3CTD%3ECrane%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EI2%3C%2FTD%3E%3CTD%3EFire%20Brigade%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EI3%3C%2FTD%3E%3CTD%3ELoader%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJ1%3C%2FTD%3E%3CTD%3EExcavator%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJ2%3C%2FTD%3E%3CTD%3EBackhoe%20Loader%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJ3%3C%2FTD%3E%3CTD%3EGrader%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJ4%3C%2FTD%3E%3CTD%3EForklift%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJ5%3C%2FTD%3E%3CTD%3EOther%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EK%3C%2FTD%3E%3CTD%3EScooter%2C%20Moped%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3Bmake%20a%20function%20or%20formula%20or%20vba%2C%20for%20the%20following%20condition.%3C%2FP%3E%3CP%3EI%20have%20to%20write%20the%20category%20in%20the%20same%20cell%20with%20comma%20for%20example%20see%20below.%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22128%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22128%22%20height%3D%2217%22%3EA%2C%20B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20the%20driving%20authority%20must%20come%20automatically%20in%20the%20other%20cell%20as%20below%3A%3C%2FP%3E%3CTABLE%20width%3D%22592%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22592%22%3EMotorcycle%2C%20Moped%2C%20Scooter%2C%20Car%2C%20jeep%2C%20Delivery%20Van%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EI%20may%20need%20to%20write%20up%20to%207%20category%26nbsp%3BIn%20the%20same%20cell.%3C%2FP%3E%3CP%3Eplease%20make%20a%20formula%20for%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2620810%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2620872%22%20slang%3D%22en-US%22%3ERe%3A%20Make%20formula%20for%20the%20below%20detail%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2620872%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1122369%22%20target%3D%22_blank%22%3E%40songgum%3C%2FA%3E%26nbsp%3BAnd%20what's%20the%20logic%20behind%20all%20of%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2620873%22%20slang%3D%22en-US%22%3ERe%3A%20Make%20formula%20for%20the%20below%20detail%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2620873%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20I%20have%20to%20make%20report%20on%20the%20driving%20authority%20of%20the%20person%20where%20i%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2620956%22%20slang%3D%22en-US%22%3ERe%3A%20Make%20formula%20for%20the%20below%20detail%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2620956%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1122369%22%20target%3D%22_blank%22%3E%40songgum%3C%2FA%3E%26nbsp%3BSorry%2C%20but%20what's%20the%20logic%20for%20combining%20the%20entries%20for%20A%20and%20B%20into%20a%20string%20like%20%22%3CSPAN%3E%3CEM%3EMotorcycle%2C%20Moped%2C%20Scooter%2C%20Car%2C%20jeep%2C%20Delivery%20Van%3C%2FEM%3E%22.%20What's%20the%20next%20string%20you%20want%20an%20based%20on%20which%20rules%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2621186%22%20slang%3D%22en-US%22%3ERe%3A%20Make%20formula%20for%20the%20below%20detail%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2621186%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1122369%22%20target%3D%22_blank%22%3E%40songgum%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EWith%20the%20permission%20of%20everyone%20involved%2C%20here%20is%20an%20example%20to%20unfold%20for%20yourself.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EPlease%20see%20the%20inserted%20file%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20patience%20and%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
CategoryDriving Authority
AMotorcycle, Moped, Scooter
BCar, jeep, Delivery Van
CTempo, Auto Rickshaw
C1E-Rickshaw
DPower Tiller
ETractor
FMinibus, Minitruck
GTruck, Bus, Lorry
HRoad Roller, Dozer
H1Dozer
H2Road Roller
ICrane, Fire Brigade, Loader
I1Crane
I2Fire Brigade
I3Loader
J1Excavator
J2Backhoe Loader
J3Grader
J4Forklift
J5Other
KScooter, Moped

 make a function or formula or vba, for the following condition.

I have to write the category in the same cell with comma for example see below.

A, B

 

The the driving authority must come automatically in the other cell as below:

Motorcycle, Moped, Scooter, Car, jeep, Delivery Van

I may need to write up to 7 category In the same cell.

please make a formula for it.

8 Replies

@songgum And what's the logic behind all of this?

@Riny_van_Eekelen  I have to make report on the driving authority of the person where i work.

@songgum Sorry, but what's the logic for combining the entries for A and B into a string like "Motorcycle, Moped, Scooter, Car, jeep, Delivery Van". What's the next string you want an based on which rules?

@songgum 

With the permission of everyone involved, here is an example to unfold for yourself.

Please see the inserted file.

 

Thank you for your patience and time.

 

Nikolino

I know I don't know anything (Socrates)

 

 

@songgum 

=TEXTJOIN(", ",TRUE,XLOOKUP(FILTERXML("<y><z>"&SUBSTITUTE(E1,",","</z><z>")&"</z></y>","//z"),A1:A14,B1:B14))
Hey man i don't know the rule and logic i just want the above thing done.
hy it does not work or . i make a mistake. please elaborate it

@songgum 

Column A contains the Category and column B contains the Driving Authority. E1 contains the search criteria.