How to make contents of multiple cells act as one cell for a table

**boyle5150** · 02-15-2011, 01:16 PM

Hello, I have a spreadsheet that has a list of names that I need to be able to create a table for. The problem is that there are multiple cells for say the address column and so on. How can I make it to where I can sort all the cells in multiple columns to be seen as one? So If I sort by name all the other columns with multiple cells sort to that persons name.

**6StringJazzer** · 02-15-2011, 01:36 PM

This format is really very, very difficult to use. You won't be able to do the kind of sorting you want without writing VBA code, and even then it will still be difficult to actually do anything with the data in this layout.

I would recommend that it be reformatted so that each person is on a single row with the following columns, then your problem is solved:

surname
first name
position
DOB
nationality
company
street
city
state
zip
email
phone
cell
other
Next-of-kin street
Next-of-kin city
Next-of-kin state
Next-of-kin zip
Next-of-kin email
Next-of-kin phone
Next-of-kin cell
Next-of-kin other
Name
Relationship

**boyle5150** · 02-15-2011, 02:09 PM

yeah I was kind of afraid of that, because I would have to start from scratch and my list is about 250+ people.

**6StringJazzer** · 02-15-2011, 02:50 PM

OK, I see your problem and I feel sorry for you.

See the attached. Take what I did in J3:J7, copy it. Then select J8:Jx where x is your last row, and paste. You have to select a number of rows that is a multiple of 5 to paste into. The format of your real data has to be EXACTLY like what you provided, as far as number of rows used for each person, and the person's name always in the second row.

Then select all your data from row 3 to the last row, and sort by column J.

And then in your spare time go back and reformat the data

**boyle5150** · 02-15-2011, 04:46 PM

Originally Posted by 6StringJazzer

OK, I see your problem and I feel sorry for you.

See the attached. Take what I did in J3:J7, copy it. Then select J8:Jx where x is your last row, and paste. You have to select a number of rows that is a multiple of 5 to paste into. The format of your real data has to be EXACTLY like what you provided, as far as number of rows used for each person, and the person's name always in the second row.

Then select all your data from row 3 to the last row, and sort by column J.

And then in your spare time go back and reformat the data

Thank you so much, that was very helpful.