How can I sort with Excel? (Not as simple as it sounds)?

Question

I have a huge list of names and addresses in a excel spreadsheet and need to sort these by name unfortunately some of the address span multiple lines so when it's sorted using the normal sort they get jumbled up and moved to the bottom of the spreadsheet.

Thanks folks - but just figured it out on my ownsome. A bit of a clumsy fix but repeating the name over the rows needed to be kept together keeps them together when sorted. Then the extra names are deleted once sorted.

Answer 1

Thats not too hard.
Just use one line for the whole address.
After that sort out and you will get the result you wanted.

Answer 2

To sort without changing the structure you need to create a new key in an empty column. What you want to sort on is in the key
example
Name, address, key
andy, New York ,formula (result New York)
,USA,formula (result New York)

You can create the key by using a formula that looks at the name cell and if it is blank then it copies the previous rows data as its key. you then copy the results of the formulas over themselves as values. If you then sort on the key first and then descending on name as the second they will stay together.
Let me know how you get on
Formula would be something like if(isblank(a3),b2,b3)
If the addresses extend beyond two lines then you need to nest multiple ifs.

Answer 3

You'll never be able to sort properly with the address in multiple rows.

Can you not place the full address in one cell/row by using "wrap text" ? Another alternative is to use more columns for the addresses.

Answer 4

The only way to sort any table (excel or database) is to know the limits of each record. If the fields stretch over a number of rows then probably the only way to do it would be to use VBA to re-write it based on the row containing the name.

Answer 5

the only way is to cut and paste each separate cell of the address into adjoining cells so that name and full address is on one line.