Alphabetize sets of rows within a WB

[Toney]

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]

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]

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]

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.

 

[Ron Rosenfeld]

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

I assumed your data was in the format as above.

I assumed the Folder names were differentiated by containing a colon.
I assumed there were no empty lines within the data.
I assumed the first blank line marked the end of the data.
I assumed that every folder has at least one file.


If your data starts in A1 (and you can change that in the code below), the
following macro should sort it according to your specifications:

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro
by name, and <RUN>.

===================================
Option Explicit
Sub SortFileList()
Dim r As Range
Dim i As Long

Set r = Range("A1") 'or wherever data starts
Do
Do Until InStr(r.Offset(i + 1, 0).Value, ":") > 0
i = i + 1
If r.Offset(i + 1).Value = "" Then Exit Do
Loop
Set r = Range(r, r.Offset(i, 0))
With r
.Sort key1:=r(1, 1), _
order1:=xlAscending, _
Header:=xlYes, _
Orientation:=xlSortColumns
End With
Set r = r.Offset(i + 1, 0).Resize(1, 1)
i = 0
Loop Until r.Value = ""

End Sub
=========================================
--ron

 

[Toney]

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

 

[Ron Rosenfeld]

Ron
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)

You're not the first person who did that, and I'm sure you won't be the last


It is just a matter of changing the sort range to include the extra columns.

So instead of:

==============================
Set r = Range(r, r.Offset(i, 0))
With r
...
==============================

use:

===============================
Set r = Range(r, r.Offset(i, 3))
With r
...
===============================
--ron

 

[Toney]

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

 

[Toney]

Ron,

That did the trick! Thanks a lot.

Toney

 

[Ron Rosenfeld]

Ron,

That did the trick! Thanks a lot.

Toney

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