| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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\ |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Alphabetize sets of rows within a WB
Ron,
That did the trick! Thanks a lot. Toney |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Rows to columns with multiple sets | Excel Worksheet Functions | |||
| Can I use Sort to alphabetize & leave blank row bet. rows (2003) | New Users to Excel | |||
| Recognizing sets of rows | Excel Discussion (Misc queries) | |||
| Grouping According to Sets of Rows | Excel Worksheet Functions | |||
| How do I set up different column widths for sets or rows? | Excel Discussion (Misc queries) | |||
Linear Mode
