Alphabetize sets of rows within a WB
 

Hi all

I have a list of files I got from my IT dept to review. The list shows the
directory and then the files in that directory. Unfortunately the files are
not alphabetized under the directory heading. I currently have each directory
and file as a seperate row.

Is there a way to alphabetize a set of rows, then alphabetize another set of
rows without mixing the sets? To further complicate things, there could be
any number of rows (files). I can make the directories an unique string that
could indicate a start/stop point.
Example
Data as recieved
K:\PMC\BECKY\
File D.doc
File A.doc
File C.doc
File B.doc
K:\PMC\DAN\
File E.doc
K:\PMC\STEVE\
File E.doc
File A.doc
File Z.doc

Result
K:\PMC\BECKY\
File A.doc
File B.doc
File C.doc
File D.doc
K:\PMC\DAN\
File E.doc
K:\PMC\STEVE\
File A.doc
File E.doc
File Z.doc

Alphabetize sets of rows within a WB
 

I would put the folder on each of the lines with the file names.

Insert a new column A
In A1, put =B1

Then in A2, put this formula:
=if(mid(b2,2,1)=":",b2,a1)
Then drag this formula down as far as you need.

Now select column A and convert it to values
(edit|copy, edit|Paste special|values)

Then you can sort both columns using column A as the primary key and column B as
the secondary key.

This does assume that you're only using mapped drives.

Toney wrote:

Hi all

I have a list of files I got from my IT dept to review. The list shows the
directory and then the files in that directory. Unfortunately the files are
not alphabetized under the directory heading. I currently have each directory
and file as a seperate row.

Is there a way to alphabetize a set of rows, then alphabetize another set of
rows without mixing the sets? To further complicate things, there could be
any number of rows (files). I can make the directories an unique string that
could indicate a start/stop point.
Example
Data as recieved
K:\PMC\BECKY\
File D.doc
File A.doc
File C.doc
File B.doc
K:\PMC\DAN\
File E.doc
K:\PMC\STEVE\
File E.doc
File A.doc
File Z.doc

Result
K:\PMC\BECKY\
File A.doc
File B.doc
File C.doc
File D.doc
K:\PMC\DAN\
File E.doc
K:\PMC\STEVE\
File A.doc
File E.doc
File Z.doc

--

Dave Peterson

Alphabetize sets of rows within a WB
 

Dave,
Thanks for the quick reply.
This works except when the directory name is alphabetically behind the file
name. What I get after the sort by column A and B is...
Col A Col B
K:\PMC\BECKY\ File A.doc
K:\PMC\BECKY\ File B.doc
K:\PMC\BECKY\ File C.doc
K:\PMC\BECKY\ File D.doc
K:\PMC\BECKY\ K:\PMC\BECKY\

Alphabetize sets of rows within a WB
 

I'd either ignore that line or delete it.

If you want to delete it, you can apply data|filter|autofilter (xl2003 menus) to
that column.

Use a custom filter on that column:
Contains
\

And then delete those visible cells.

====
Or you could sort the data by column B and the drive/folder names will be in one
location. Delete those rows and sort by column A and B.



Toney wrote:

Dave,
Thanks for the quick reply.
This works except when the directory name is alphabetically behind the file
name. What I get after the sort by column A and B is...
Col A Col B
K:\PMC\BECKY\ File A.doc
K:\PMC\BECKY\ File B.doc
K:\PMC\BECKY\ File C.doc
K:\PMC\BECKY\ File D.doc
K:\PMC\BECKY\ K:\PMC\BECKY\

--

Dave Peterson

Alphabetize sets of rows within a WB
 

Ron
I assumed your data was in the format as above.
That was my fault.
The macro worked great except I didn't give enough detail. Each record
actually consists of a file name, date, time and size except for the
directories which has only the name...
K:\PMC\BECKY\
File D.doc Date Time Size
File A.doc Date Time Size
File C.doc Date Time Size
File B.doc Date Time Size
K:\PMC\DAN\
File E.doc Date Time Size

So the final result would be...
K:\PMC\BECKY\
File A.doc Date Time Size
File B.doc Date Time Size
File C.doc Date Time Size
File D.doc Date Time Size
K:\PMC\DAN\
File E.doc Date Time Size

As I said your macro worked great except it re-ordered the just the file
names and left the date, time and size columns alone. Sorry for the confusion

(I've learned my lesson...give ALL the info in the first post. It saves time
later)

Thanks

Toney

Alphabetize sets of rows within a WB
 

Dave,

Thanks for the reply. Unfortunately I need to keep the directory name listed
before the files. I have to import another file list into this WB and it's
set up with the directory as a separate row in front of the files.

However, I've already figured out another use for the formula you suggested.

Thanks for your help

Toney

"Dave Peterson" wrote:

I'd either ignore that line or delete it.

If you want to delete it, you can apply data|filter|autofilter (xl2003 menus) to
that column.

Use a custom filter on that column:
Contains
\

And then delete those visible cells.

====
Or you could sort the data by column B and the drive/folder names will be in one
location. Delete those rows and sort by column A and B.



Toney wrote:

Dave,
Thanks for the quick reply.
This works except when the directory name is alphabetically behind the file
name. What I get after the sort by column A and B is...
Col A Col B
K:\PMC\BECKY\ File A.doc
K:\PMC\BECKY\ File B.doc
K:\PMC\BECKY\ File C.doc
K:\PMC\BECKY\ File D.doc
K:\PMC\BECKY\ K:\PMC\BECKY\

--

Dave Peterson

Alphabetize sets of rows within a WB
 

Ron,

That did the trick! Thanks a lot.

Toney

Alphabetize sets of rows within a WB
 

On Fri, 18 Sep 2009 09:33:03 -0700, Toney
wrote:

Ron,

That did the trick! Thanks a lot.

Toney

You're welcome. Glad to help. Thanks for the feedback.
--ron