Using concatenate with variable outputs.

[Newbienew]

My formula doesn't work on the different Excel versons. I am looking for a formula that does what the one below does. If B12 has text in it then B11 will be displayed; same goes with C12 and C11 and so on. However, they all need to be joined together with a comma in between each one if the results are true. I attempted to use the concatenate formula but realized I was unsure of how to build it due to row 12 changes up pending different entries.

simply put I would like to duplicate textjoin formula with concatenate or so to work on all verisons.

VISION CORR 20/20	NCP	NH	NSI	SCE	SUB QUAL'ED	MOS OBLI SERV	US CIT
Note 1	X	Note 6	0	Note 7	0	72	X

=_xlfn.TEXTJOIN(",",,IF(ISTEXT($B$12),RIGHT($B$11,6),""),IF(ISTEXT($C$12),$C$11,""),IF(ISTEXT($D$12),$D$11,""),IF(ISTEXT($E$12),$E$11,""),IF(ISTEXT($F$12),$F$11,""),IF(ISTEXT($G$12),$G$11,""),IF(ISTEXT($H$12),$H$11,""),IF(ISTEXT($I$12),$I$11,""))

 

[BrerRabbit]

Start slow, don't build teh entire thing at one. Start with the first two cells, then if that works that add the next two cells.

=concatenate(ifB12<>""(B12&", ",""),if(B11<>"",B11&", ","")

 

[kvsrinivasamurthy]

Try this

=SUBSTITUTE(SUBSTITUTE("$$"&IF(ISTEXT($B$12),","&RIGHT($B$11,6),"")&IF(ISTEXT($C$12),","&$C$11,"")&IF(ISTEXT($D$12),","&$D$11,"")&IF(ISTEXT($E$12),","&$E$11,"")&IF(ISTEXT($F$12),","&$F$11,"")&IF(ISTEXT($G$12),","&$G$11,"")&IF(ISTEXT($H$12),","&$H$11,"")&IF(ISTEXT($I$12),","&$I$11,""),"$$,",""),"$$","")

 

[Peter_SSs]

If B12 has text in it then B11 will be displayed;

From the image, we have no idea what is B12 or B11. Any reason you can't use 'Mini Sheet' with XL2BB rather than 'Table Only'?

How many columns do you actually have (or might you have)?

Would a vba solution be acceptable?

 

[Newbienew]

From the image, we have no idea what is B12 or B11. Any reason you can't use 'Mini Sheet' with XL2BB rather than 'Table Only'?

How many columns do you actually have (or might you have)?

Would a vba solution be acceptable?

I would LOVE a VBA but VBA doesn't work on all platforms. IF this is put in to google sheets or one drive or so Marcos or VBAs dont work. This is why I am trying to use baseline formulas.

I didn't post the mini sheet due to the possible confusion in the formulas in those cells. The top row is 11 bottom row is 12.

The number of columns is just what you see here. Added I was informed the MOS OBLI SERV column does not need to be counted in the formula. It cant be skipped.

ASVAB CAL-WLPRLH247389.xlsb
	B	C	D	E	F	G	H	I
11	VISION CORR 20/20	NCP	NH	NSI	SCE	SUB QUAL'ED	MOS OBLI SERV	US CIT
12	Note 1	X	Note 6	0	Note 7	0	72	X
HOME

Cell Formulas
Range	Formula
B12	B12	=IF(VLOOKUP($A$6,RATINGS!$A$4:$V$124,14,FALSE)=0,,VLOOKUP($A$6,RATINGS!$A$4:$V$124,14,FALSE))
C12	C12	=IF(VLOOKUP($A$6,RATINGS!$A$4:$V$124,15,FALSE)=0,,VLOOKUP($A$6,RATINGS!$A$4:$V$124,15,FALSE))
D12	D12	=IF(VLOOKUP($A$6,RATINGS!$A$4:$V$124,16,FALSE)=0,,VLOOKUP($A$6,RATINGS!$A$4:$V$124,16,FALSE))
E12	E12	=IF(VLOOKUP($A$6,RATINGS!$A$4:$V$124,17,FALSE)=0,,VLOOKUP($A$6,RATINGS!$A$4:$V$124,17,FALSE))
F12	F12	=IF(VLOOKUP($A$6,RATINGS!$A$4:$V$124,18,FALSE)=0,,VLOOKUP($A$6,RATINGS!$A$4:$V$124,18,FALSE))
G12	G12	=IF(VLOOKUP($A$6,RATINGS!$A$4:$V$124,19,FALSE)=0,,VLOOKUP($A$6,RATINGS!$A$4:$V$124,19,FALSE))
H12	H12	=IF(VLOOKUP($A$6,RATINGS!$A$4:$V$124,20,FALSE)=0,,VLOOKUP($A$6,RATINGS!$A$4:$V$124,20,FALSE))
I12	I12	=IF(VLOOKUP($A$6,RATINGS!$A$4:$V$124,21,FALSE)=0,,VLOOKUP($A$6,RATINGS!$A$4:$V$124,21,FALSE))

 

[Peter_SSs]

Thanks for the clarifications.

Did you try any of the suggested formulas? It seems to me that the post #3 suggestion does the same thing as your formula.

However, I think this considerably shorter version also does the same thing.

=REPLACE(IF(ISTEXT(B12),","&RIGHT(B11,6),"")&IF(ISTEXT(C12),","&C11,"")&IF(ISTEXT(D12),","&D11,"")&IF(ISTEXT(E12),","&E11,"")&IF(ISTEXT(F12),","&F11,"")&IF(ISTEXT(G12),","&G11,"")&IF(ISTEXT(I12),","&I11,""),1,1,"")

 

[kvsrinivasamurthy]

Have you seen post#3

 

[Newbienew]

@Peter_SSs & @kvsrinivasamurthy I have just attempted to try both formulas, and YES these work amazing and hit the target spot on. I am not as familiar with the Replace or Substitute formula and its application. Thank you so much.