CODEWITHSUNDEEP
excelvba
mjking
mjking

Reputation: 3

Combine multiple columns into one cell with header information

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

Upvotes: 0

Views: 2629

Answers (1)

chuff
chuff

Reputation: 5866

What you need is a formula that :

  1. Uses the IF function to test whether each information cell has data (I'm assuming that you will always have the name).
  2. If the cell does, concatenate the character representation of the ASCII code for a carriage return (CHAR(10)) and the data for that cell.
  3. 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.

Upvotes: 1

Related Questions