Microsoft Excel question

[mbalbritton]

Great program for sorting Indexes for our Maps at work. We have a couple Macros that save a ton of time. But teh one thing that you'd think would be so simple is the biggest PITA.

Sorting numbers.

Is there setting somewhere that will allow you to sort 1,2,3,4,5,6,7,8,9,10 etc with out throwing the 10 up front?

Many cities we map have numbered streets that go up into 100's Even 3-400's. You go to resort the index into Alpha numerical and it screws it all up since they aren't numbered 001 through 100.

I find it humorous that this pretty complex program can't count 1-1000 in a useful manner.

 

[Rich]

Format the cells as numbers doesn't work? I use excel a bunch at work, end even though I don't know much about it, that always seems to work.

 

[fsj80]

We must be missing some info because I just sorted a bunch of data using the listings (1-787) and they sorted correctly.

 

[mbalbritton]

maybe the missing part is that these are road names example

9th St
9th St South
9th Ter
9th Ter
10th Ave
10th Ave East
10th St

or Interstates, State Hwys and County Roads:

I-95
I-4
I-26

NC 109
NC 62
NC 8

I'll try formating as numbers, but not sure I can since there's letters.

 

[shawn]

Select the column that you want, then hit alt-F4.

That should do the trick.

 

[mbalbritton]

and that was suppose to...............

or maybe that's a PC thing....

let the Mac joke begin..c'mon Caver

 

[SHINTON]

Yep, you are not sorting NUMBERS you are sorting "Letters" and 1-9/0 come before A...

what you CAN do is to split the street NUMBER from the street name. Setup a small test using DATA>TEXT TO COLUMNS

You can use SPACES to split them or others...

So what I would do is this... COPY the entire column to a fresh column with plenty of blank columns to the right.

THen highlight this data and do the DATA/Text to columns...and use a SPACE as the "delimiter" that should put all the street NUMBERS in one column, with maybe a few exceptions.

Still not done tho, I think there is a RIGHT sort button that sorts right to left in a cell, you will need to do this to find all the 800A, 1008B (My old addy was 5521-B for instance)

you can also use the "-" as a delimter

 

[family xj]

Can you text to columns and then sort? basically that allows the 9th to be seperated to 9 th. Just an idea.

 

[Rich]

I would think that would screw up I-95 then...

 

[mbalbritton]

COPY the entire column to a fresh column with plenty of blank columns to the right.

issue I see with this is that's not the only column, and I need to keep Rows together
Example of a row:
Street Location Page Grid
9th Ter NYC 16 A1
9th Ter Nouth NYC 16 A2
9th Ter South NYC 16 A3


or a screen shot, this one happens to be sans the Location abreavation:

 

[SHINTON]

Sorry, what we are doing is moving the data to the "right" side out of the way, to create a "new" column that is a "sort" column.

So leave the rest of the data in the various columns, create a new "sortable" column on the right that you sort the entire dataset by... have had to do this a bazillion times in various conversions from one DBase to another in various accounting packages, all sorts of little tricks that are easy to do but hard to explain using sorts, Text to columns, and "insert" shift to right...(used that before by seperating using delimiters of space, 1 character wide each)

So afterwords (ignore the ..... that is just ASCII art/to space them out)

SORT COLUMN OLD COLUMN

9.........................9th St
10.......................10th St
10.......................10th Ave

etc...so you sort on the "Sort Column" and the data in the "old Column" and other columsn now appears in the manner you want it!

Or...you can pay me the big bucks and I will do this for you heh...

Sam


10
10

 

[mbalbritton]

I'm with you now!

Short of typing 100's of cells in:

9
10
10

from your example, how to pull those out and copy them to the new column?

 

[SHINTON]

COPY that current column (say it is A)

over to a new column (lets say it is P..)

aw heck just call me 999/ 999 / 9999

Sam

 

[mbalbritton]

Just got off the phone.. thanks for the concatenate.....ing.... Lesson Sam!

You da Man!

 

[SHINTON]

for the rest of you that are "mighty interested" I am sure...

Copy
Pasted data into new column

on new column...
Text as columns, SPACE delimted (1 char wide each)

Then sorted all data, with the 3 new columns
1...0 ...t...h ave
9...t...h...ave

etc

this put all the 9s together, etc, then on that smaller group... did insert, cells, SHIFT TO RIGHT (this aligned up the 100s, 10s, and 1s)

=concatenate(col1, col2, col3) to restitch them back as a number

then copied that formula down

finally, did a copy, paste special (values) and that created the sortable new column of numbers....

...see, "simple" heh..

Sam...spends WAY too much time in Excel...Hinton

 

[mbalbritton]

dang good teaching, Patient and effective explanations.

 

[shawn]

let the Mac joke begin..c'mon Caver

Okay, then in your case, you want to hit squiggly, open apple, control, option and Q.

 

[Tradarcher]

Why are you using Excel for that?

 

[mbalbritton]

Why are you using Excel for that?

Is there something more suitable to sort Index info?

What I'm using it for is We have an idex of an entire counties worth of Info. Streets, Major Roads, Hwy's, Interstates, POI's, Schoold, Marinas, Parks, etc.

We may do a smaller area inside the county, or need to merge new information into the existing index. So we copy the index from our InDesign Layout file into Excel to merge, then extract any uneeded info, then Sort again. and copy back into InDesign for our new Layout.

Suggestions always welcome, but Excel seems like the best option for sorting.