Challenging Sort

[Darren]

I have a list of data in ONE column, it looks like this:

AAA.LP.SITE.CD
AANY.LP.SITE.CD
DESIGN.XT.DALLAS.CD
OPS.XT.DALLAS.CD

I'd like to sort it, leaving it in one column to look like
this:

CD.DALLAS.XT.DESIGN
CD.DALLAS.XT.OPS
CD.SITE.LP.AAA
CD.SITE.LP.AANY

Basically, to reverse the text by the delimited period,
then sort alphabetically.

Can it be done? How?

Thanks!
Darren

 

[Greg]

I have a list of data in ONE column, it looks like this:

AAA.LP.SITE.CD
AANY.LP.SITE.CD
DESIGN.XT.DALLAS.CD
OPS.XT.DALLAS.CD

I'd like to sort it, leaving it in one column to look like
this:

CD.DALLAS.XT.DESIGN
CD.DALLAS.XT.OPS
CD.SITE.LP.AAA
CD.SITE.LP.AANY

Basically, to reverse the text by the delimited period,
then sort alphabetically.

[Tested in Excel 97, should work the same way or very similarly in
other versions.]

Backup your file first in case anything goes wrong.

Make sure you have at least 4 empty columns to the right of your data.
If you don't, insert some blank columns.

Select all data, then choose Data -> Text To Columns

On the first screeen of the Wizard, choose Delimited.
On the second screen of the Wizard, choose Other. In the box next to
the selection for "Other," enter a period.
On the third screen of the wizard, you can leave the default
formatting of General. Select Finish.

You should now have the original data broken into four sections.

Assuming the four sections are in columns A-D, enter the following
formula in E1: =D1 & "." & C1 & "." & B1 & "." & A1

Copy the formula in E1 down that column as far as your data goes.

Everything should look as you want it now. If it does, you can select
the results in column E, then Edit -> Copy followed by Edit -> Paste
Special -> Values. This will allow you to delete the 4 temporary
columns with the broken up pieces of the original data.

 

[Darren]

-----Original Message-----

I have a list of data in ONE column, it looks like this:

AAA.LP.SITE.CD
AANY.LP.SITE.CD
DESIGN.XT.DALLAS.CD
OPS.XT.DALLAS.CD

I'd like to sort it, leaving it in one column to look like
this:

CD.DALLAS.XT.DESIGN
CD.DALLAS.XT.OPS
CD.SITE.LP.AAA
CD.SITE.LP.AANY

Basically, to reverse the text by the delimited period,
then sort alphabetically.

[Tested in Excel 97, should work the same way or very similarly in
other versions.]

Backup your file first in case anything goes wrong.

Make sure you have at least 4 empty columns to the right of your data.
If you don't, insert some blank columns.

Select all data, then choose Data -> Text To Columns

On the first screeen of the Wizard, choose Delimited.
On the second screen of the Wizard, choose Other. In the box next to
the selection for "Other," enter a period.
On the third screen of the wizard, you can leave the default
formatting of General. Select Finish.

You should now have the original data broken into four sections.

Assuming the four sections are in columns A-D, enter the following
formula in E1: =D1 & "." & C1 & "." & B1 & "." & A1

Copy the formula in E1 down that column as far as your data goes.

Everything should look as you want it now. If it does, you can select
the results in column E, then Edit -> Copy followed by Edit -> Paste
Special -> Values. This will allow you to delete the 4 temporary
columns with the broken up pieces of the original data.



--
Greg
phobos78-marslink-net
Replace dashes and move in by 1 planet to reply.
.

Thanks Greg!

That worked great, except, some delimited strings aren't
as long as others. IE.

A.B.C.D.E
A.B.E
X.Y.Z.E
A.E

Combining at the end places a . (period) to begin the line
if there's a blank column after the Text > Column process.

Any other ideas?

Thank you!
Darren

 

[Greg]

Thanks Greg!

That worked great, except, some delimited strings aren't
as long as others. IE.

A.B.C.D.E
A.B.E
X.Y.Z.E
A.E

Combining at the end places a . (period) to begin the line
if there's a blank column after the Text > Column process.

It's ugly, but it works:

=IF(ISBLANK(D1),"",D1 & ".") & IF(ISBLANK(C1),"",C1 & ".") &
IF(ISBLANK(B1),"",B1 & ".") & A1

This replaces the formula that I gave for you before for column E.
The rest of the procedure is the same.

Note:
1. Be careful if you copy and paste from this message into Excel,
since your newsreader is likely to split the formula onto two lines.
2. You will need to extend the formula to be large enough to cover
the maximum number of sections in your original data.

Good luck!

 

[Guest]

Darren
You can also use this

=SUBSTITUTE(TRIM(F1&" "&E1&" "&D1&" "&C1&" "&B1)," ","."

Use a space to seperate the data. The TRIM then reduces any multiple spaces to a single space. The SUBSTITUTE then changes the spaces to periods

Good Luck
Mark Graesse
(e-mail address removed)
----- Darren wrote: ----

-----Original Message----

I have a list of data in ONE column, it looks like this
AAA.LP.SITE.C AANY.LP.SITE.C
DESIGN.XT.DALLAS.C
OPS.XT.DALLAS.C
I'd like to sort it, leaving it in one column to look

like
this

CD.DALLAS.XT.DESIG CD.DALLAS.XT.OP
CD.SITE.LP.AA
CD.SITE.LP.AAN
Basically, to reverse the text by the delimited period,

then sort alphabetically
[Tested in Excel 97, should work the same way or very

similarly i
other versions.

Backup your file first in case anything goes wrong
Make sure you have at least 4 empty columns to the right

of your data
If you don't, insert some blank columns

Select all data, then choose Data -> Text To Columns
On the first screeen of the Wizard, choose Delimited.

On the second screen of the Wizard, choose Other. In the box next t
the selection for "Other," enter a period
On the third screen of the wizard, you can leave the defaul
formatting of General. Select Finish

You should now have the original data broken into four sections
Assuming the four sections are in columns A-D, enter the

followin
formula in E1: =D1 & "." & C1 & "." & B1 & "." & A

Copy the formula in E1 down that column as far as your data goes
Everything should look as you want it now. If it does,

you can selec
the results in column E, then Edit -> Copy followed by Edit -> Past
Special -> Values. This will allow you to delete the 4 temporar
columns with the broken up pieces of the original dataGre
phobos78-marslink-ne
Replace dashes and move in by 1 planet to reply

Thanks Greg

That worked great, except, some delimited strings aren't
as long as others. IE

A.B.C.D.
A.B.
X.Y.Z.
A.

Combining at the end places a . (period) to begin the line
if there's a blank column after the Text > Column process

Any other ideas

Thank you
Darre