As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/
Options

Excel - Trying to organize my network sharespace

NijaNija Registered User regular
I am trying to keep some sort of organization on our network.
I am not the network administrator, however, part of my job is to make sure I have enough space to do what I need to do.
Everything on this particular drive is relevant to me. I need to know when folders can be archived.

Looking at the drive, I see the following list (examples):

2-102
00012-1653
00017-8234
17-8243
24-02
24-300
24-0802
32-0108
200-2869
00200-6399

Everything should start with a number.

Folders are frequently added and so they fall into place. The format should be xxxxx-yyyy. That is a separate battle.
I have a .bat file I run on a monthly basis (syntax: dir > get.folders.txt )

This gets me all files and folders on the top-level folder.

I am copied on a closed matters e-mail distribution list. These come in a very unorganized manner, but generally 1 e-mail is 1 closed matter
I have to open the e-mails and get the matter number ( see the above list for how things are submitted).
I usually append these matter numbers to the end of my Excel sheet.

In Column I, we have the network list. In Column J we have the following formula =MATCH(I4,R4:R4501,0) .Column R has the closed
matters numbers (received from the e-mail distribution list)

My problems:

-I want to only add the new folders added to the network to Column I. The existing folders should still be available.
-I want to easily check Column R against Column I, anywhere they overlap means I need to archive a matter number

I am open to suggestions on improving this workflow, as long as I don't have to buy new software. I am OK with Excel but there are large
swaths of it I have never used.

So far everything has been working decently, but there is a better way, and I think that I am just not seeing it.

Any and all help is appreciated!

Priest lvl 110 Warlock lvl 9x DK lvl 110 Paladin lvl 9x Rogue lvl 8x

Steam Me

Posts

  • Options
    MrTLiciousMrTLicious Registered User regular
    I'm having a little trouble understanding exactly what's happening here. Is this file being automatically updated or are you manually entering data?

    Is your first problem that you want to automatically figure out which files from the bat are duplicates that you don't need to add?

    In the second issue, are you looking for a way to get a list of the intersections rather than scanning column j?

    Are you averse to using macros?

  • Options
    NijaNija Registered User regular
    edited March 2013
    The Excel list I am creating is composed of two separate outputs:
    Column I is the output from the .bat file
    Column R is the output from manual data entry from individual e-mails.

    Being able to automatically figure out which files from the bat are duplicated is desired. Ideally, it would be great if I had to do little/no extra work. If the (non-duplicated) output from the bat could be appended to the corresponding Column I that would be awesome although, I don't think that is possible.

    My aversion to macros is mostly ignorance. I don't know how to write them.
    Here is a link of how the Excel Sheet looks
    PoacfPo.jpg?1
    The #N/A is the return from the MATCH formula.

    Thanks MrTlicious

    Nija on
    Priest lvl 110 Warlock lvl 9x DK lvl 110 Paladin lvl 9x Rogue lvl 8x

    Steam Me
  • Options
    MrTLiciousMrTLicious Registered User regular
    As far as I know what you're asking for is going to require a macro, but it would be extremely straightforward to implement and modify.

    Does the .bat file automatically update column I or does it spit out another file that you then paste in?

  • Options
    NijaNija Registered User regular
    The .bat file outputs to a separate .txt file. I would then format the text in a blank workbook. Originally, I would just replace the old information in Column I with the newly formatted text.

    Priest lvl 110 Warlock lvl 9x DK lvl 110 Paladin lvl 9x Rogue lvl 8x

    Steam Me
  • Options
    MrTLiciousMrTLicious Registered User regular
    edited March 2013
    Disclaimer: Please make a backup before running any kind of code. I don't want to potentially be responsible for losing your company data :P

    You can run a really simple program to do what I think you're asking for. Code in spoilers
    Sub Update()
    ' Initialize Variables
    Application.Calculation = xlCalculateManual
    Dim Iold As Integer, Rold As Integer, row As Integer, datarow As Integer, newstring As String, irow As Integer, Dupe As Boolean
    Dim rrow As Integer, Dupe2 As Boolean, duperow As Integer
    
    Sheets("Sheet2").Range("C2:E1048576").Clearcontents ' Clear any previously generated lists
    
    ' Figure out how many entries you already have. This will not work if you have blanks
    Iold = WorksheetFunction.CountA(Sheets("Sheet1").Columns("I"))
    irow = Iold + 1
    Rold = WorksheetFunction.CountA(Sheets("Sheet1").Columns("R"))
    duperow = 1
    
    
    'loop through your new entries. Again, this will not work if you get blanks
    For datarow = 1 To WorksheetFunction.CountA(Sheets("Sheet2").Columns("A"))
        Dupe = False ' So far, no duplicate
        newstring = Sheets("Sheet2").Cells(datarow, "A") ' Store value in memory rather than checking cell repeatedly
        row = 1 'Start at the beginning of column I
        While row <= Iold And Not Dupe 'Keep looking until you run out of choices or you find a duplicate
            If StrComp(Sheets("Sheet1").Cells(row, "I"), newstring, 0) = 0 Then 'Check if they are the same
                Dupe = True 'It's a duplicate
            Else
                row = row + 1 'Keep looking
            End If
        Wend
        
        If Not Dupe Then 'If this is a new entry, then we add it and check against column R
            Sheets("Sheet1").Cells(irow, "I").Value = newstring ' Add to Column I
            Dupe2 = False
            rrow = 1
            While rrow <= Rold And Not Dupe2 'Now we start checking R to see if it's in that column
                If StrComp(Sheets("Sheet1").Cells(rrow, "R"), newstring, 0) = 0 Then ' Check if it's in column R
                    Dupe2 = True
                    duperow = duperow + 1
                    Sheets("Sheet2").Cells(duperow, "C") = newstring 'If so, add to list on Sheet 2 near new data
                    Sheets("Sheet2").Cells(duperow, "D") = rrow
                    Sheets("Sheet2").Cells(duperow, "E") = irow
                    Sheets("Sheet1").Cells(irow, "J") = rrow
                Else
                    rrow = rrow + 1
                End If
            Wend
            irow = irow + 1
        End If
    Next datarow
       
    End Sub
    

    This will take a set of names in Sheet2, columnA, and do the following:

    First, it will compare it to your current column I in Sheet1.

    If it doesn't already exist it will append it, and check it against column R.
    If it's in column R, it will put what row (of column R) it's in in column J, and put the following information on Sheet 2.
    Column C: File name
    Column D: Column R row
    Column E: Column I row

    This will give you the same setup as you had in column J to scan, or you can check the list generated in Sheet2 for the list of things that match column R. This list will start in row 2 in case you want to put headers.

    A couple important notes:
    First, you will probably need to change the sheet names. Wherever there's a Sheet1, you're going to need to change that to whatever the sheet name is where your data is being stored. Where there's a Sheet2, you'll need to change it to whatever you call your new sheet that you'll have to add. In this sheet, all you need to do is paste the filenames into column A.

    Second, if there are blanks in either column I, column R, or what you paste into the new sheet, this could really mess up your system.. This finds the end of your file by counting the number of things in those columns, so if the number of things isn't equal to the last row, there's going to be a problem.

    Third, Excel will try to change some of these to dates on you. To prevent this, make sure that column I and column A of the new page are set to type "Text" as opposed to "General" before you paste anything or run the program.


    If you're not at all familiar with macros, let me know and I will tell you how to set this up to run.

    MrTLicious on
  • Options
    NijaNija Registered User regular
    Thank you, I haven't had a chance to try this out yet, but it looks like it does everything I need it to.

    Thank you again!

    Priest lvl 110 Warlock lvl 9x DK lvl 110 Paladin lvl 9x Rogue lvl 8x

    Steam Me
  • Options
    AiouaAioua Ora Occidens Ora OptimaRegistered User regular
    Hey @Nija, do you want a script to rename your folders so they fit the format? I'm learning powershell, seems like a fun project.

    life's a game that you're bound to lose / like using a hammer to pound in screws
    fuck up once and you break your thumb / if you're happy at all then you're god damn dumb
    that's right we're on a fucked up cruise / God is dead but at least we have booze
    bad things happen, no one knows why / the sun burns out and everyone dies
  • Options
    NijaNija Registered User regular
    Aioua wrote: »
    Hey @Nija, do you want a script to rename your folders so they fit the format? I'm learning powershell, seems like a fun project.
    I went through, manually, and did some editing. They were suppose to be CCCCC-MMMM-Name1_v_Name2 and I just didn't think any amount of automation was going to help. If you want the challenge, be my guest. I have a non-production environment I can run it on.

    Priest lvl 110 Warlock lvl 9x DK lvl 110 Paladin lvl 9x Rogue lvl 8x

    Steam Me
  • Options
    AiouaAioua Ora Occidens Ora OptimaRegistered User regular
    edited April 2013
    All right @Nija, It's powershell time.

    Just replace "H:\test\" in the first and penultimate lines with the appropriate path.
    #get all the folder names
    $NameList=( Get-ChildItem H:\test\ | Where-Object{$_.PsIsContainer} | ForEach-Object{$_.name} )
    
    #analyzing each name
    $NameList | ForEach-Object {
        $FormatError=$false
        $FormatErrorList=@()
        
        #split up name by hyphens to analyze the parts 
        $Splits=$_ -split "-"
        
        #check number of splits (needs to be 3)
        IF ($Splits.length -ne 3) {
            $FormatError=$true; $FormatErrorlist+="Wrong number of hyphens, can't analyze."
        }
        ELSE {
            #check format of first number, first for letters
            IF (!($Splits[0] -match "^[^a-z]+$")) {
                $FormatError=$true; $FormatErrorlist+="First `"number`" has letters in it."
            }
            #check the length
            IF ($Splits[0].length -gt 5) {
                #check for leading zeros
                IF (($Splits[0].substring(0,$Splits[0].length-5)) -match "^0+$") {
                    $new1st=$Splits[0].substring($splits[0].length-5)
                }
                #too long error
                ELSE {$FormatError=$true; $FormatErrorlist+="First number is too long."}  
            }
            #too short, add zeros
            ELSEIF ($Splits[0].length -lt 5) {
                $new1st=$Splits[0].padleft(5,"0")
            }
            #just right, don't change
            ELSE {$new1st=$Splits[0]}
            
            #---
            
            #same thing for second number
            IF (!($Splits[1] -match "^[^a-z]+$")) {
                $FormatError=$true; $FormatErrorlist+="Second `"number`" has letters in it."
            }
            #check the length
            IF ($Splits[1].length -gt 4) {
                #check for leading zeros
                IF (($Splits[1].substring(0,$Splits[1].length-4)) -match "^0+$") {
                    $new2nd=$Splits[1].substring($splits[1].length-4)
                }
                #too long error
                ELSE {$FormatError=$true; $FormatErrorlist+="Second number is too long."}  
            }
            #too short, add zeros
            ELSEIF ($Splits[1].length -lt 4) {
                $new2nd=$Splits[1].padleft(4,"0")
            }
            #just right, don't change
            ELSE {$new2nd=$Splits[1]} 
                   
            #---
            
            #check name for a single "_V_"
            IF (($Splits[2] -split "_v_").length -ne 2) {
                $FormatError=$true; $FormatErrorlist+="Name has wrong format."
            }
        }
        #whitespace
        Write-Output ""
        
        #check for errors
        IF ($FormatError) {
            Write-Output "Could not rename folder $_ :"
            $FormatErrorList
        }
        
        #rename folder if there are any changes
        ELSE {
            $newname=$new1st+"-"+$new2nd+"-"+$splits[2]
            IF ($newname -eq $_) {Write-Output "Folder $_ is formatted correctly"}
            ELSE {
                Rename-Item "H:\test\$_\" "$newname"
                Write-Output "Renamed folder $_ as $newname."
            }
        }
    }
    

    Aioua on
    life's a game that you're bound to lose / like using a hammer to pound in screws
    fuck up once and you break your thumb / if you're happy at all then you're god damn dumb
    that's right we're on a fucked up cruise / God is dead but at least we have booze
    bad things happen, no one knows why / the sun burns out and everyone dies
  • Options
    GethGeth Legion Perseus VeilRegistered User, Moderator, Penny Arcade Staff, Vanilla Staff vanilla
    Aioua has been kidnapped by dresdenphile and is being held for ransom! Solve the riddle to set em' free, or I'll hand em' over to Clamps!

    Don’t ask yes or nos, give me the THIRD OPTION
    Think you see me? “Ran away” reads the caption
    Now if you gonna front, you’re cruising for a BATTLE
    (Just don’t get Mully, she’ll own you like chattle)
    Which forumer am I?

  • Options
    GethGeth Legion Perseus VeilRegistered User, Moderator, Penny Arcade Staff, Vanilla Staff vanilla
    Aioua has been rescued by POKÉMON MASTER WT SHERMAN.

  • Options
    NijaNija Registered User regular
    edited April 2013
    MrTLicious wrote: »
    Disclaimer: Please make a backup before running any kind of code. I don't want to potentially be responsible for losing your company data :P

    You can run a really simple program to do what I think you're asking for. Code in spoilers
    Sub Update()
    ' Initialize Variables
    Application.Calculation = xlCalculateManual
    Dim Iold As Integer, Rold As Integer, row As Integer, datarow As Integer, newstring As String, irow As Integer, Dupe As Boolean
    Dim rrow As Integer, Dupe2 As Boolean, duperow As Integer
    
    Sheets("Sheet2").Range("C2:E1048576").Clearcontents ' Clear any previously generated lists
    
    ' Figure out how many entries you already have. This will not work if you have blanks
    Iold = WorksheetFunction.CountA(Sheets("Sheet1").Columns("I"))
    irow = Iold + 1
    Rold = WorksheetFunction.CountA(Sheets("Sheet1").Columns("R"))
    duperow = 1
    
    
    'loop through your new entries. Again, this will not work if you get blanks
    For datarow = 1 To WorksheetFunction.CountA(Sheets("Sheet2").Columns("A"))
        Dupe = False ' So far, no duplicate
        newstring = Sheets("Sheet2").Cells(datarow, "A") ' Store value in memory rather than checking cell repeatedly
        row = 1 'Start at the beginning of column I
        While row <= Iold And Not Dupe 'Keep looking until you run out of choices or you find a duplicate
            If StrComp(Sheets("Sheet1").Cells(row, "I"), newstring, 0) = 0 Then 'Check if they are the same
                Dupe = True 'It's a duplicate
            Else
                row = row + 1 'Keep looking
            End If
        Wend
        
        If Not Dupe Then 'If this is a new entry, then we add it and check against column R
            Sheets("Sheet1").Cells(irow, "I").Value = newstring ' Add to Column I
            Dupe2 = False
            rrow = 1
            While rrow <= Rold And Not Dupe2 'Now we start checking R to see if it's in that column
                If StrComp(Sheets("Sheet1").Cells(rrow, "R"), newstring, 0) = 0 Then ' Check if it's in column R
                    Dupe2 = True
                    duperow = duperow + 1
                    Sheets("Sheet2").Cells(duperow, "C") = newstring 'If so, add to list on Sheet 2 near new data
                    Sheets("Sheet2").Cells(duperow, "D") = rrow
                    Sheets("Sheet2").Cells(duperow, "E") = irow
                    Sheets("Sheet1").Cells(irow, "J") = rrow
                Else
                    rrow = rrow + 1
                End If
            Wend
            irow = irow + 1
        End If
    Next datarow
       
    End Sub
    

    This will take a set of names in Sheet2, columnA, and do the following:

    First, it will compare it to your current column I in Sheet1.

    If it doesn't already exist it will append it, and check it against column R.
    If it's in column R, it will put what row (of column R) it's in in column J, and put the following information on Sheet 2.
    Column C: File name
    Column D: Column R row
    Column E: Column I row

    This will give you the same setup as you had in column J to scan, or you can check the list generated in Sheet2 for the list of things that match column R. This list will start in row 2 in case you want to put headers.

    A couple important notes:
    First, you will probably need to change the sheet names. Wherever there's a Sheet1, you're going to need to change that to whatever the sheet name is where your data is being stored. Where there's a Sheet2, you'll need to change it to whatever you call your new sheet that you'll have to add. In this sheet, all you need to do is paste the filenames into column A.

    Second, if there are blanks in either column I, column R, or what you paste into the new sheet, this could really mess up your system.. This finds the end of your file by counting the number of things in those columns, so if the number of things isn't equal to the last row, there's going to be a problem.

    Third, Excel will try to change some of these to dates on you. To prevent this, make sure that column I and column A of the new page are set to type "Text" as opposed to "General" before you paste anything or run the program.


    If you're not at all familiar with macros, let me know and I will tell you how to set this up to run.

    @MrTLicious - This worked out perfectly. It did everything I needed! You have saved me a large amount of time. Thank you.

    @Aioua - I haven't had a chance to work with the powershell script yet.

    Nija on
    Priest lvl 110 Warlock lvl 9x DK lvl 110 Paladin lvl 9x Rogue lvl 8x

    Steam Me
  • Options
    MrTLiciousMrTLicious Registered User regular
    Happy to be of service!

Sign In or Register to comment.