Custom Sorting of Lists to be Data Merged

Hi all.
Hope everybody is doing well.
Here is my quandry...
I receive room number/room names from the architects that need to be merged into my design application (InDesign, Flexi Design).
These lists typically are all over the place as far as sequential numbering goes.
And many times there are over 1000 rows in the list.
Here is a quick example...

10000 STORAGE
10001 SOILED/ DECONTAM WORK AREA
9999 WORK AREA
8888 STORAGE
6987 ELEVATOR CONTROL ROOM
12000 STERILE STORAGE
12000A BREAKDOWN
12000B STERILE STORAGE
6987B STERILE STORAGE
6987A PREP AND PACK
6987C PREP AND PACK
8880 PREP AND PACK
15000 PREP AND PACK
10000B STORAGE
10001C WORK AREA


How would I sort the list to keep any value with a suffix (1000A ,1000B, 1000C etc) in sequential order?
Here is the result after sorting low to high, which does not help me much.

6987 ELEVATOR CONTROL ROOM
8880 PREP AND PACK
8888 STORAGE
9999 WORK AREA
10000 STORAGE
10001 SOILED/ DECONTAM WORK AREA
12000 STERILE STORAGE
15000 PREP AND PACK
10000B STORAGE
10001C WORK AREA
12000A BREAKDOWN
12000B STERILE STORAGE
6987A PREP AND PACK
6987B STERILE STORAGE
6987C PREP AND PACK

I also am looking for a macro to insert line breaks after the words in the room names based on how wide my sign is...
We use 6", 9" & 12" signs.

Please see attached for more detailed info.
Thanks for your assistance!
-KD

Change A column format to text then sort
You've there mixed format (numbers and text) so it is sorted differently

Hey Sandy.
Sorry, formatting to text doesn't work for me.
Here is the result...

6987
8880
8888
9999
10000
10001
12000
15000
10000B
10001C
12000A
12000B
6987A
6987B
6987C

Sort1 is correct. How did you do it?

Dave,
Each method is good for achieving the correct result

Hey guys...
Thanks for the prompt response(s)!
Can't seem to get either method to work, but it is late and can barely keep the old eyes open...
Will give it a shot tomorrow.
Appreciate y'all's help.
=KD

Originally Posted by FlameRetired

Yes of course. Thought it might be of interest re: the sort keys formulas.

It is more subtle than mine

And here is the final result...
HELP!
Thanks.

Result.png

I do believe you may have helped me in the past....
worked on a couple data merge applications, complex pricing worksheets...
Anyway, thanks for the tip, I will try again.
Did you want to see any of those images?
I have removed them all.

Still can't get to work.
When I paste the formula in formula bar for cell "C1" I get a return of "10000" (the value of "A1").
Sorting 2 levels seem to make no difference.
Attached is a new sheet with the formulas applied.
See if you can see where I am going wrong.
Thanks sandy

It worked! Thanks!
DUH!Didn't recognize the "both drag down"...

Now, the both drag down part... What if my table has 1800+ rows, which is not uncommon.
Is there a way to do that quicker and more accurately?
And now with this longer sheet I am getting #VALUE Errors on the last 9 rows.
See attached for a sheet with 593 rows.

I see it will be a long time

I'll try step by step

Your data is in range (with example) A1:B15
You need divide text from A1 (A2, A3,...etc) to a number and text
First formula extract numbers from A1, second formula extract letters (if any)
so
first formula with correct cell should be in C1
=--IF(ISTEXT(A1),MID(A1,1,LEN(A1)-1),A1)
second formula should be in D1
=IF(ISTEXT(A1),RIGHT(A1,1),"")

now select C1 and D1 and both drag down to the end of your data (here:to row 15)

Now select entire data A1:D15
go to the ribbon
Data
Sort
first level Sort by C column
add second level Sort by D column

hope it will help

I see.... Letter preceding a number.
Need to modify the formula in F1

Yes I got it to work and I now understand HOW it works...
Let me see if I can figure out how to fix the rows with letters first then numbers.
Thank you kindly!
I will get back with you with the results.

I have one more request which I will post to a new thread.

No problem
but first you should define HOW it should be sorted with all kinds of data

Sort Rooms with Letter Prefixes (A & Lowest Number to Z & Highest Number)
Example...
A100
B100
B900
C200
C200A
C200B
Z10000

I would like to display those at the bottom of the sheet.

Or in this specific case..

C263 DISH ROOM
C337 ADMIN OFFICES
C337A ADMIN OFFICES
C358 READING SUITE
C358 READING SUITE
C513 STAFF ONLY
C513 STAFF ONLY
C514B STAFF ONLY
C514B STAFF ONLY

Pattern would be numerical lowest to highest.
Then after the highest number (at the bottom) start values with letter in front (sort A-Z, lowest to highest)...
Like this...
1000
2000
3000
5000
A100
B100
B900
C200
C200A
C200B
Z10000

See attached for workbook as example.

Ok, see attached file
select entire data and use Data - Sort - levels (for three columns: C, D, E)

formula in C1 is array entered
both next - normal
drag all three formulas down to the end of the data

OK. I understand.
Right now I need the last example to come out like this...

Please Login or Register  to view this content.

Good nite & thanks!

Got it to work!
Thanks so much.
I see that the result depends on the order of sorting levels.

One more question...
When copy --> paste the formulas to a new sheet, the value for A1 (1502) in the cell with the array formula is not formatted correctly...
Comes back as $2.00, when I change format to General it returns as "2".
How come?
See attached please for example.

read here: spontaneously formats

That is messed up!
Fix didn't work for this.
Keeps going back to the wrong format!
Any ideas?
I am running 2010

Yes I read that. I have never had this problem before.
Never.
I will mark this thread as solved.
Not your fault Microsoft BS.
Thank you sandy.
-KD

You're right - call MS

yeah right.
HAHAHA

Here's what I am doing wrong...
When exiting the cell, remember to do it with CTRL+SHIFT+ENTER
Now works like a charm.
Appreciate your help on this~
Have a great day!
-KD