VBA - Multiple values in a cell (comma separated) and return the corresponding values to a single cell (comma separated) with characters more than 255

[Hari1992]

Hi,

I am looking for a VBS/ formula for the following. Here I am trying to achieve Dynamic Lookup for multiple values in a cell (comma separated) and return the corresponding values to a single cell (comma separated also). I am able to populate the B13 through the formula with Barnames column which has less than 255 char, but not working for B14 which has more than 255 char. Could someone suggest to me the Formula/Macro/VBS achieve this?

Thanks in advance,

Cell Formulas

B4- =REPT("-",255)

C4- =REPT("-",255)

B13- =TEXTJOIN(CHAR(10),1,IF(ISNUMBER(FIND(","&$A$4:$A$7&",",","&A13&",")),$B$4:$B$7,""))

B14- =TEXTJOIN(CHAR(10),1,IF(ISNUMBER(FIND(","&$A$4:$A$7&",",","&A14&",")),$C$4:$C$7,""))

 

[kweaver]

Worked for me. ??

 

[Hari1992]

Worked for me. ??

View attachment 38582

My Bad

C4- =REPT("-",256) - correction it is 256 char

 

[kweaver]

Well, that's the issue...there's a character limit in a cell to 255 characters. There's probably some CONCATENATE work-around, but I don't know it.

 

[Fluff]

The character limit for a cell is ~32,700 for a string, the problem is that that the IF function is limited to 255 characters/cell.

 

[Hari1992]

Could you suggest any work around like VBA code/Macro

 

[Fluff]

Not sure if this will work in xl 2019, but try

+Fluff 1.xlsm
	A	B	C
1
2
3	Name	BARNAMES	CARNAMES
4	Comp	---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------	----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5	Vlp	BAR2	CAR2
6	Verifications	BAR3	CAR3
7	Non	BAR4	CAR4
8
9
10
11
12
13	Comp,Vlp,Non	---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- CAR2 CAR4
Master

Cell Formulas
Range	Formula
B4	B4	=REPT("-",255)
C4	C4	=REPT("-",256)
B13	B13	=TEXTJOIN(CHAR(10),,INDEX(C4:C7,MATCH(FILTERXML("<k><m>"&SUBSTITUTE(A13,",","</m><m>")&"</m></k>","//m"),A4:A7,0)))

It may need to be entered with Ctrl Shift Enter.

 

[Hari1992]

Not sure if this will work in xl 2019, but try

+Fluff 1.xlsm
	A	B	C
1
2
3	Name	BARNAMES	CARNAMES
4	Comp	---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------	----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5	Vlp	BAR2	CAR2
6	Verifications	BAR3	CAR3
7	Non	BAR4	CAR4
8
9
10
11
12
13	Comp,Vlp,Non	---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- CAR2 CAR4
Master

Cell Formulas
Range	Formula
B4	B4	=REPT("-",255)
C4	C4	=REPT("-",256)
B13	B13	=TEXTJOIN(CHAR(10),,INDEX(C4:C7,MATCH(FILTERXML("<k><m>"&SUBSTITUTE(A13,",","</m><m>")&"</m></k>","//m"),A4:A7,0)))

It may need to be entered with Ctrl Shift Enter.

Awesome!! you are the savior

It worked. I have another question.....What if I want to add 2 lines of space between. Here Char(10) would give me 1 line. I am looking for a two-line or Three-line space for a clear view. Please suggest

 

[Fluff]

Just add another line feed like CHAR(10)&CHAR(10)

 

[Hari1992]

Just add another line feed like CHAR(10)&CHAR(10)

Worked! thankyou very much