Windows Simple Excel 2010 question

Discussion in 'Tech Support' started by rainbowbridge, 10 Mar 2012.

  1. rainbowbridge

    rainbowbridge Minimodder

    Joined:
    26 Apr 2009
    Posts:
    3,171
    Likes Received:
    69
    OK simple question for any excel pros :)

    ...dirrectory tree report created,

    - want to be able to sort the output so the longest is top or the bottom and it deprecitates going to the shortest.

    Also, I would like to be able to create a new cell on the right and get the count of chracters used for the file path,


    So like this..

    [​IMG]
     
    Last edited: 10 Mar 2012
  2. Kernel

    Kernel Likes cheese

    Joined:
    29 Sep 2003
    Posts:
    1,183
    Likes Received:
    37
    If no one else sorts this for you, I'll do it in about 3 hours.
    But it'll be a VBA script.
     
  3. rainbowbridge

    rainbowbridge Minimodder

    Joined:
    26 Apr 2009
    Posts:
    3,171
    Likes Received:
    69
    go for it, ill send £6.50 via paypal if you get me a fix for this.
     
  4. Atomic

    Atomic Gerwaff

    Joined:
    6 May 2002
    Posts:
    9,646
    Likes Received:
    94
    Add this formula to the 'Total Chars' column and sort on that.

    Code:
    =len(A1)
    =len(A2)
    =len(A3)
    ....
    
    edit: It'd be easiest to have the path in column A then the formula in column B (no blank columns)
     
    Last edited: 10 Mar 2012
  5. rainbowbridge

    rainbowbridge Minimodder

    Joined:
    26 Apr 2009
    Posts:
    3,171
    Likes Received:
    69
  6. rainbowbridge

    rainbowbridge Minimodder

    Joined:
    26 Apr 2009
    Posts:
    3,171
    Likes Received:
    69
    ok cool now how can I apply that to all feilds in G going down wards?

    Edit, ok cool copy A1, all the way down and it updates it.
     
  7. Atomic

    Atomic Gerwaff

    Joined:
    6 May 2002
    Posts:
    9,646
    Likes Received:
    94
    Formula dragging is great eh :)
     
  8. longweight

    longweight Possibly Longbeard.

    Joined:
    7 May 2011
    Posts:
    10,517
    Likes Received:
    217
    This is a really good little tip!
     
  9. rainbowbridge

    rainbowbridge Minimodder

    Joined:
    26 Apr 2009
    Posts:
    3,171
    Likes Received:
    69
    pm me your email for pal £

    also for ref why I am doing this, ntfs has a file limitation of 255 chracters, Through bad data management basicly some dirrectorys have exceeded the 255 limit, I need to work on this and a report to see whats going would be helpfull, the data is just standard business reports, pdfs, excels, research...
     
  10. Atomic

    Atomic Gerwaff

    Joined:
    6 May 2002
    Posts:
    9,646
    Likes Received:
    94
    Interesting as I had the same issue at work earlier this year. Migrating to a new file server/SAN, although we have software to rename directories pre and post copy as there's over 4TB of office files it'd take forever doing that manually!

    You can put a conditional formatting to highlight the over length paths.

    http://www.howtoexcelatexcel.com/excel-tips-tricks/conditional-formatting/
     

Share This Page