Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Jan 2005
    Location
    Gwent, Wales
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Ordering a list (Excel 2002)

    Hi,

    I have a spreadsheet which has a list of paths of files (which can be variable in position and path, but not file name) which I need to paste into another spreadsheet. The only problem is that they are not in the correct order for the other spreadsheet. I need to reorder the list in a specific order. The attached sheet, shows the picture. Column A contains the raw path data, column F contains the order that the paths need to be pasted in the other spreadsheet. The names would be actual names and not model 1, model 2 (so can't solve this with numbers )etc. I then need the reordered list pasted in column H, for example.

    Any ideas?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Ordering a list (Excel 2002)

    Maybe I am being a little obtuse, but if you know the specific order you want, why don't you just use this "ordered list" of them in the specific order instead of the list in the "incorrect order"?

    Am I missing something?

    Steve

  3. #3
    Lounger
    Join Date
    Jan 2005
    Location
    Gwent, Wales
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ordering a list (Excel 2002)

    The paths change based on user input i.e. they can choose to save the files anywhere. The only constant is the model name and its order. I need the full path to be pasted into the other spreadsheet.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Ordering a list (Excel 2002)

    I still don't quite understand? Is it a matter a separating the paths from the filenames, sorting by filenames and then combining or is there more to it?

    Steve

  5. #5
    Lounger
    Join Date
    Jan 2005
    Location
    Gwent, Wales
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ordering a list (Excel 2002)

    I think that something like that may work. The important thing is that the correct filename and path apears next to the Column F file name. This is the order that is important.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ordering a list (Excel 2002)

    The user defined function below will solve part of the problem. This function wil allow you to extract the filename from the path and put it into an empty column. You can then sort on this column. However, the way you have named your files, they are still not going to sort into the order you want since Model10 and Model11 will sort before Model2, etc. The file names would have to be Model01, Model02, etc. for them to sort in the order you want. The function below may be able to fix that problem, but I would have to know exactly what the real names look like.

    <pre>Public Function ParseFileName(strPath As String) As String
    If InStr(strPath, "") = 0 Then
    ParseFileName = strPath
    Exit Function
    End If
    ParseFileName = Right(strPath, Len(strPath) - InStrRev(strPath, ""))
    End Function
    </pre>

    Legare Coleman

  7. #7
    Lounger
    Join Date
    Jan 2005
    Location
    Gwent, Wales
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ordering a list (Excel 2002)

    Hi, thanks for this. I get the following when I try to call it from a macro "Compile error: Argument not optional"??

    I think that this may work, as if I have the file names seperated, I can then compare these to the list in column F (although Column F won't contain the full file name and version number, just the file name). So if, using the code above, I extract the name....Happy Model V2, I somehow need to compare this to the values in column F (I would be looking for Happy Model, not the version number). If there is a match and say Happy Model is in the 3rd cell down in the range I would then need to copy the path and the model name to this possition. This would then need to be continued for all the models. Any ideas, psa for further info on what I need to achieve.

  8. #8
    Lounger
    Join Date
    Jan 2005
    Location
    Gwent, Wales
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ordering a list (Excel 2002)

    Ok, I have something that works (kind of) The following code will work to a point:

    Dim FileName 'Lookup name
    Dim CountFiles
    Dim Pos 'row number where new path should be
    Dim NewLoc As String 'Cell reference where new path should be pasted


    CountFiles = 0

    For Each Mycell In Range("A1:A11")

    Range("F1").Activate
    Set FileName = ActiveCell.Offset(CountFiles, 0)
    Set Pos = ActiveCell.Offset(CountFiles, 1)


    Range("A1:A11").Select
    Selection.Find(What:=FileName, After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Activate

    ActiveCell.Copy

    NewLoc = "K" & Pos
    Range(NewLoc).Select
    ActiveCell.PasteSpecial

    CountFiles = CountFiles + 1

    Next Mycell


    End Sub

    The problem occurs where you are trying to find a value which has similar text to another in the list eg. If FileName contained ppy Model, so the code goes off and tries to find it, but occuring earlier in the range was an entry called Happy Model. The code would incorrectly return this and paste its path next to ppy in column F. This, I guess, is the problem with using the Find function, does anyone have a better way?

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Ordering a list (Excel 2002)

    Instead of:
    LookAt:=xlPart,

    Use
    LookAt:=xlWhole,

    Then it has to match the whole name, not some of the name.

    Steve

  10. #10
    Lounger
    Join Date
    Jan 2005
    Location
    Gwent, Wales
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ordering a list (Excel 2002)

    That gives me an error. Is it because the lookup name (which has no version number) is being compared to the path, name and version number, so it can't find the lookup name or something?

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Ordering a list (Excel 2002)

    Are you trying to find the full name or only a part

    If you want to find a complete match, you must search for xlWhole, if you want to accept a partial match (which I thought you indicated that you did not want) you need xlPart.

    If you want the find the "whole" the item to search must match the entire contents of the cell...

    There is not too much of an "in-between". Though I suppose you could look for instead of the "filename", something like: "filename.xls" and use xlPart. By adding the xls to show the end and the "" to show the beg, it would be part match of the cell but a full match of the filename

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •