can you sort a bunch of tables going vertically?

[Markus]

Hi there everyone. A customer of mine recently sent me a word document full
of names and phone numbers. There is no unique and consistent method for how
he did this, and it is very disorganized. It also is not in alphabetical
order..

It's basically setup like this:

Joe Shmoe
123-123-1234
123-123-1235 office
123-123-1236 cell

etc..etc.. And there are hundreds of names in there.. I copied and pasted
the word document directly in to Excel..

It would seriously take hours if not days to reorganize this whole list in
to a proper list, so I'm wondering if there is a quick method for sorting all
this stuff in alphabetical order..

Now this might have been pointless on my part, but just for the heck of it,
I highlighted each individual name and the numbers that belong to that name,
and put them in it's very own table.. So there's basically like 300-400
tables going vertically in one column.. The person's name is actually the
header of each table..

Is there any way to sort those tables? If I can't use Excel to sort tables,
does anyone know of another way to go about doing it?

Thanks for your help.. Sorry for the ridiculousness..

 

[Dave Peterson]

Cleaning up data like this can be a challenge.

Since you've already created a table with a bunch of cells in MSWord, you could
sort there. But it will sort in alphabetical order based on the first name
(first set of characters in the cell).

If I wanted more control, I'd copy and paste into column A of a new worksheet.
And then try to put each group on a single row (column A is name, column B is
the first #, C holds the next, ...)

Then comes the more difficult part.

Is each group exactly 4 rows deep?

Or is each group separated by an empty row?

Or is it even worse--no gap between groups and no set number of rows per group?

If it's the last (ughh) version, can you pick out an indicate that holds true
for each group--maybe the first row in a group always starts with a letter (not
a number).

Depending on your responses, a macro could be written that does some of the
work.

And after you split the name into its own column, you'll want to put the first
name in one column and the lastname in another. If there are only 2 names (no
Sammy Davis, Jr MD), in the name field, then data|Text to columns could be used.

I guess you have a lot of inspecting to do??

 

[Elkar]

See if this works for you. Rather than sorting your data as Tables, use a
helper column.

Insert a new column to the left of your data
In the first cell of the new column (I'll use A1 for this example), enter
the formula: =B1
In A2, enter: =IF(ISNUMBER(--LEFT(B2,1))),A1,B2)
Copy cell A2 down through all of your data
Now, sort your data base on column A
Your data should now be sorted alphabeticlally
You can now delete column A, or hide it if you want to use it again in the
future

HTH,
Elkar

 

[Toppers]

You need to get the data into columns i.e column A with name and columns B
onwards with the various telephone numbers.

If you want to send me a sample (or full) book, I'll have a look at it.

toppers at REMOVETHISjohntopley.fsnet.co.uk

 

[BoniM]

Hi there everyone. A customer of mine recently sent me a word document full
of names and phone numbers. There is no unique and consistent method for how
he did this, and it is very disorganized. It also is not in alphabetical
order..

It's basically setup like this:

Joe Shmoe
123-123-1234
123-123-1235 office
123-123-1236 cell

etc..etc.. And there are hundreds of names in there.. I copied and pasted
the word document directly in to Excel..

It would seriously take hours if not days to reorganize this whole list in
to a proper list, so I'm wondering if there is a quick method for sorting all
this stuff in alphabetical order..

Now this might have been pointless on my part, but just for the heck of it,
I highlighted each individual name and the numbers that belong to that name,
and put them in it's very own table.. So there's basically like 300-400
tables going vertically in one column.. The person's name is actually the
header of each table..

Is there any way to sort those tables? If I can't use Excel to sort tables,
does anyone know of another way to go about doing it?

Thanks for your help.. Sorry for the ridiculousness..

 

[BoniM]

My quick fix for vertically stored data:
If each persons entry has a double space between, in word, use edit replace
to replace each end of paragraph with tabs, then replace each double tab with
a paragraph mark. Each persons data should now be in a single tab seperated
row.
Copy to excel add a couple or three columns between A and B and use text to
columns or the RIGHT function to divide names on spaces and in just a few
minutes you have a very easy to use table. If he wasn't consistent in the
order, etc, of the phone numbers, it could take a little more work to get
that part organized, but just having them by last name sounds like it would
be a big improvement!
Good luck.