Combine multiple columns into one cell with header information

Question

I am trying to create a cell in my worksheet that summarizes all the data in the row. I have found a way to combine all the fields into the first column, but I also need it to include the header name and it needs to ignore fields that are blank. (https://stackoverflow.com/a/13074838)

My excel file has over 50 columns and 5000 rows so I think this might be a job for a macro. Any ideas on how to accomplish this?

╔═════════╦══════╦═════╦═══════════╦═══════╗
║ Summary ║ Name ║ Age ║  County   ║  ZIP  ║
╠═════════╬══════╬═════╬═══════════╬═══════╣
║         ║ Sue  ║     ║ Snohomish ║ 98144 ║
║         ║ Bob  ║  36 ║ Pierce    ║ 98335 ║
║         ║ Pat  ║  32 ║ Spokane   ║       ║
║         ║ Jim  ║  40 ║ King      ║ 98101 ║
╚═════════╩══════╩═════╩═══════════╩═══════╝


Cell A2 would have the following contents:
Name: Sue
County: Snohomish
ZIP: 98144

Cell A3:
Name: Bob
Age: 36
County: Pierce
ZIP 98335

Cell A4:
Name: Pat
Age: 32
County: Spokane

What have you tied, and what problems did you run into?

Tim Williams
– Tim Williams

2013-03-07 19:47:25 +00:00
Commented Mar 7, 2013 at 19:47 — Tim Williams
– Tim Williams, Commented Mar 7, 2013 at 19:47

PowerUser · Accepted Answer · 2013-03-07 20:25:18Z

1

What you need is a formula that :

Uses the IF function to test whether each information cell has data (I'm assuming that you will always have the name).
If the cell does, concatenate the character representation of the ASCII code for a carriage return (CHAR(10)) and the data for that cell.
Be sure to turn on wrapping for the formula cell, or the text that is displayed won't show the line breaks.

The resulting formula looks like this.

 ="Name: " &B1&
   IF(C1="","",CHAR(10)&"Age: "&C1)&
   IF(D1="","",CHAR(10)&"County: "&D1)&
   IF(E1="","",CHAR(10)&"Zip: "&E1)

You could get fancy and indent the data (age, etc.) by the same amount using the LEN function.

edited Mar 7, 2013 at 20:25

PowerUser

11.8k20 gold badges67 silver badges103 bronze badges

answered Mar 7, 2013 at 20:01

chuff

5,9061 gold badge24 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mjking Over a year ago

Thank you. That did the trick. I took it a little further and made it grab the header name dynamically. Here is the formula to grab the first 8 columns of data and summarize them in the first cell. =IF(B2="","",$B$1&": "&B2&CHAR(10)) &IF(C2="","",$C$1&": "&C2&CHAR(10)) &IF(D2="","",$D$1&": "&D2&CHAR(10)) &IF(E2="","",$E$1&": "&E2&CHAR(10)) &IF(F2="","",$F$1&": "&F2&CHAR(10)) &IF(G2="","",$G$1&": "&G2&CHAR(10)) &IF(H2="","",$H$1&": "&H2&CHAR(10)) &IF(I2="","",$I$1&": "&I2&CHAR(10))

chuff Over a year ago

Great! Glad I was able to help.

Collectives™ on Stack Overflow

Combine multiple columns into one cell with header information

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related