The new forums will be named Coin Return (based on the most recent vote)! You can check on the status and timeline of the transition to the new forums here.
The Guiding Principles and New Rules document is now in effect.
Excel - Trying to organize my network sharespace
Nija
Registered User regular
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!
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!
0
Posts
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?
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
Thanks MrTlicious
Steam Me
Does the .bat file automatically update column I or does it spit out another file that you then paste in?
Steam Me
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 SubThis 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
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.
Thank you again!
Steam Me
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
Steam Me
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." } } }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
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?
@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.
Steam Me