Build Delimited List from Column values

[mmsstthomas]

I have a sheet that has a list of usernames and with a series of 20+ columns with values of: 1,2,3 or just blank.

I need to create a delimited list with the Column name and value, if the value of the column is 1,2, or 3.

For example my sheet is:



The end result I need is is listed in the Result column.

Any help is greatly appreciated!

[CARACALLA]

Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[mmsstthomas]

Attached Sample sheet

[CARACALLA]

F2=TEXTJOIN("|",TRUE,IF(B2:E2<>"",B2:E2,""))

Copy down

[mmsstthomas]

Thanks for the help, but this only pulls back the columns value. How would I pull by the column name then a semicolon?

[CARACALLA]

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

Copy down

[Fluff13]

Another option

Please Login or Register  to view this content.

[mmsstthomas]

This worked! Thank you so much!

[mmsstthomas]

also worked, thanks!

[Fluff13]

You're welcome & thanks for the feedback.