Conditionally Concatenate and format multiple cells into one

[sburt09]

Hello, I have attached a formula sheet that makes this easier to explain. Essentially I have about 100 columns. Which may or may not be populated. If they are populated I need to pull the value into a single cell in the format "Column_Name_1: Column_Value_1, Column_Name_2: Column_Value_2, Column_Name_3: Column_Value_3 etc etc while skipping over the blanks. I have tried to use an array function with textjoin but it did not work. Any help would be appreciated

[harrisonland]

If you put the following in G2, it'll give you the result you want for these limited columns

Please Login or Register  to view this content.

But you're going to end up with one hell of a formula for 100+ rows.

It might be best to use a UDF, instead:

Please Login or Register  to view this content.

Put the formula

Please Login or Register  to view this content.

in the last cell in the row - if you want it elsewhere, that's fine but you'll have to take away the "-1" (minus 1) from the LCol.

Remember, also, that UDFs only update when they're forced to recalculate.

Does that get you started?

Tim


EDITED to correct circular reference problem.

[JBeaucaire]

The non-array TEXTJOIN formula for F2, then copied down, would be:

=TEXTJOIN(",",TRUE, IF(B2:E2<>"","["&$B$1:$E$1&"]=["&B2:E2&"]",""))

But I don't understand fully why you have "blanks" in your G4 and G6 output. Whatever reasonings, you can probably add them to the beginning of the formula above as IF prechecks.

[harrisonland]

=TEXTJOIN(",",TRUE, IF(B2:E2<>"","["&$B$1:$E$1&"]=["&B2:E2&"]",""))

Nice. So much better, thanks - I've learned something again today.

Tim


NB that's an array formula, right?

[sburt09]

This was super helpful and solved the problem perfectly!

[JBeaucaire]

It is an array function, but it is not entered with CTRL+SHIFT+ENTER, it is not necessary. TEXTJOIN handles the array functionality by design.