Jagged columns from file & folder totals

[schatham]

I have a powershell script that I run to produce a folder listing & size file text file. I give it a root folder to start in & it goes thru each folder recursively summing up the file sizes within the folder.

The end result is like this:

Myfolder|5800
Bin|1800
|de|40000
|en|50000
||Random1|5043
||Random2|5044
Data|33000
|Cache|20000
|||TE9|221000
||||TF9|20000

and so on.
Anything without a pipe "|" to it's left is considered a subfolder off the root folder. Anything with a pipe to it's left, is a subfolder of the folder above it. 2 pipes = 2 levels, 3 pipes = 3 levels and so on. I bring it into Excel by saying it is a delimited text file.

The issue is is that the folder total is for only the files in that folder & not the total of the folders below it. In the above example:

Bin|1800
|de|40000
|en|50000
||Random1|5043
||Random2|5044

beginning in cell A1
A1 & B1 = Bin & 1800 respectively.
B2 & C2 = de & 40000 respectively (A2 is blank)
B3 & C3 = en & 50000 respectively (A3 is blank)
C4 & D4 = Random1 & 5043 respectively (A4 & B4 are blank)
C5 & D5 = Random2 & 5044 respectively (A5 & B5 are blank)

the 1800 in B1 is the total of the files in the Bin folder off the root folder. The total of 40000 in C2 is the total of the de sub folder. The total of the en subfolder would be 50000 (C3) + 5043 (D4) + 5044 (D5) = 60087.

What I need to do is to be able to sum the right-most columns, rolling them up & adding them to the end of the row at the next folder level above them. Using the above example, 10087 would be the total in D3. The total in cell C1 would be 40000 (C2) + 50000 (C3) + 5043 (D4) + 5044 (D5) = 100087. I could then add the 1800 (B1) to that total & get the total file size for the Bin folder & all its subfolders.

I would like to be able replicate that anywhere along the way - where I have numbers in 2 adjacent columns to add them & come up with a folder size from that point no matter where I am within that folder structure.

As far as the data is concerned, a blank in a column implies it is a subfolder of the folder above it. And, I know that the last 2 columns of any row will always be the folder name & the folder size. There are no set number of columns, but I think it runs to 10 or 12 now. There are between 20,000 & 30,000 rows at any given time.

I thought subtotals might work, but with up to a dozen of them, that might not be readable. Too, it's the reverse of the problem of totaling off the end - having to replicate data down & not overwrite something.

Is there any good way to do this?

 

[hiker95]

schatham,

It is difficult to follow what you have posted and described.

What version of Excel are you using?

Can you post a screenshot of the raw data worksheet, and, post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker
Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.

If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[schatham]

No problem. Here is the info you asked for. I don't know if I can post pictures or not, but I will do my best.

Excel version 2010

The input file is a text file, pipe delimited. As posted above, I saved it as MyDataTextfile.txt. You can take the lines below & simply paste them into Notepad & save it as a text file.

Myfolder|5800
Bin|1800
|de|40000
|en|50000
||Random1|5043
||Random2|5044
Data|33000
|Cache|20000
||TE9|221000
|||TF9|20000

Once saved, open Excel & go to a blank sheet. Go to the Data tab, click the From Text on the Get External Data & then select the file & Import it. Choose Delimited data type & click Next, click the box beside "Other" delimiter & put in the pipe ("|") symbol. Click Next, Finish and put the result in $A$1.

It should look like this:

Excel 2010
	A	B	C	D	E
1	Myfolder	5800
2	Bin	1800
3		de	40000
4		en	50000
5			Random1	5043
6			Random2	5044
7	Data	33000
8		Cache	20000
9			TE9	221000
10				TF9	20000
Sheet1

I would then like to total things up as follows:

Excel 2010
	A	B	C	D	E	F	G
1	Myfolder	5800					<--- No Subfolder
2	Bin	1800	100087
3		de	40000				<--- No Subfolder
4		en	50000	10087
5			Random1	5043			<--- No Subfolder
6			Random2	5044			<--- No Subfolder
7	Data	33000	261000
8		Cache	20000	241000
9			TE9	221000	20000
10				TF9	20000		<--- No Subfolder
Sheet1 (2)

where anything having a subfolder to it gets a total of the size of all the subfolders below it. In the case above, the values in yellow are the subtotal of all the folder sizes below that folder. Cells B4:D4 (values en, 50000 and 10087 respectively) the 10087 in yellow represents the totals of the two subfolders (Cells C5 & C6, Random1 & Random2) of en (Cell B4).

I know at this point that any folder that has no subfolder below it totals to itself only, and to any folder(s) above it. C5:D5 and C6:D6's totals aren't added to the other's totals, but they would be added to the totals of B4:C4's when calculating the space occupied by the folder en and all it's subfolders (Random1 & Random2). So the total space occupied by folder en would be 60087, and would include the contents of folders en, Random1 & Random2.

The following isn't exactly what I want

Excel 2010
	A	B	C	D	E	F	G	H
1	Myfolder	5800					Myfolder	5800
2	Bin	1800	100087				Bin	101887
3	Bin	de	40000				Bin\de	40000
4	Bin	en	50000	10087			Bin\en	60087
5	Bin	en	Random1	5043			Bin\en\Random1	5043
6	Bin	en	Random2	5044			Bin\en\Random2	5044
7	Data	33000	261000				Data	294000
8	Data	Cache	20000	241000			Data\Cache	261000
9	Data	Cache	TE9	221000	20000		Data\Cache\TE9	241000
10	Data	Cache	TE9	TF9	20000		Data\Cache\TE9\TF9	20000
Sheet1 (3)

but it does get close to what I am wanting to know. What I want to know is the biggest space occupiers at the root level, and 2, 3, 4 levels farther down.

Isolating one set of folders from the above:

Excel 2010
	A	B	C	D	E	F	G	H
2	Bin	1800	100087				Bin	101887
3	Bin	de	40000				Bin\de	40000
4	Bin	en	50000	10087			Bin\en	60087
5	Bin	en	Random1	5043			Bin\en\Random1	5043
6	Bin	en	Random2	5044			Bin\en\Random2	5044
Sheet1 (3)

I know the Bin folder (and all its subfolders) have a file size of 101887. One level farther down, I can see that the Bin\de folder has a size of 40000 & the Bin\en and all its subfolders have a total size of 60087.

I'd like to be able to summarize to the root level & show only these folders & totals:

Excel 2010
	A	B
1	Myfolder	5800
2	Bin	101887
3	Data	294000
Sheet1 (4)

To go 1 level deep & show these folders & totals:

Excel 2010
	A	B
1	Myfolder	5800
2	Bin	1800
3	Bin\de	40000
4	Bin\en	60087
5	Data	33000
6	Data\Cache	261000
Sheet1 (5)

Un-subtotaling as I drill down a level (Note that the Bin folder went from 101887 down to 1800 as I drilled a level down) and so on.

I hope this better shows what I've got & how I'm wanting to use it.

Thanks

SC

 

[hiker95]

schatham,


Based on your original test directory structure level for the text file data.


After you bring the text file data into Excel it looks like this:

Excel 2007
	A	B	C	D	E
1	Myfolder|5800
2	Bin|1800
3	|de|40000
4	|en|50000
5	||Random1|5043
6	||Random2|5044
7	Data|33000
8	|Cache|20000
9	||TE9|221000
10	|||TF9|20000
11
Sheet1

After the macro (the Yellow cells display the totals):

Excel 2007
	A	B	C	D	E
1	Myfolder	5800
2	Bin	1800	100087
3		de	40000
4		en	50000	10087
5			Random1	5043
6			Random2	5044
7	Data	33000	261000
8		Cache	20000	241000
9			TE9	221000	20000
10				TF9	20000
11
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub SeparateTextAddTotals()
' hiker95, 02/22/2013
' http://www.mrexcel.com/forum/excel-questions/686924-jagged-columns-file-folder-totals.html
Dim Area As Range, lr As Long, c As Long, lc As Long, sr As Long, er As Long
Dim mytot As Long, i As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
Application.DisplayAlerts = False
With Range("A1:A" & lr)
  .TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
    TrailingMinusNumbers:=True
End With
'Application.SendKeys ("{ENTER}")
Application.DisplayAlerts = True
lc = Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
Range(Cells(1, 1), Cells(lr, lc)).NumberFormat = "General"
For c = lc To 3 Step -1
  For Each Area In Range(Cells(1, c), Cells(lr, c)).SpecialCells(xlCellTypeConstants).Areas
    With Area
      sr = .Row
      er = sr + .Rows.Count - 1
      If .Rows.Count = 1 Then
        With Cells(sr - 1, c)
          .Value = Cells(sr, c).Value
          .Interior.ColorIndex = 6
        End With
      Else
        For i = sr To er Step 1
          If IsNumeric(Cells(i, c)) = True Then
            mytot = mytot + Cells(i, c) + Cells(i, c + 1)
          End If
        Next i
        With Cells(sr - 1, c)
          .Value = mytot
          .Interior.ColorIndex = 6
        End With
        mytot = 0
      End If
    End With
  Next Area
Next c
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the SeparateTextAddTotals macro.


You will have to SUBTOTAL/Group the data manually right now (your many screenshots were very confusing).

 

[hiker95]

schatham,

If you could supply screenshots of other text files per reply #3, with more than ||| levels, I will try to create a single macro solution for them all.

 

[schatham]

Here is a chunk of the file with multiple levels.

App_Themes|1244616
|Default|418221
||Images|345601
|||Category|183982
|Graphite|408174
||Images|335071
|||Category|183982
Controls|3450046
|App_LocalResources|2978149
Data|33736076702
|Cache|24111918027
||TE9SVE9CQ08|22716482364
|||NTEwRzA2MU0|1075986824
||||MDIwMDAwMDA|5777060
|||||MQ|5777060
||||||MTA|103786
||||||MTE|344636
||||||MTI|1072686
||||MDIwMjA1MDI|4587081
|||||MQ|4587081
||||||MTA|59078
||||||MTE|186804
||||||MTI|591757
||||||MTM|1682916
|||VEVDSExCUlk|1527525
||||MDAwMDAwMDA|1527525
Services|323
Upgrade|239942
|App_LocalResources|199637
Viewer|2217201
|App_LocalResources|1939462
|Images|21474

I had to make a change or two to the macro - it was overflowing on big numbers. The above is more representative of the data I'm actually working with.

I also noticed that it did some unintended totaling also.

Excel 2010
	A	B	C	D	E	F	G	H
1	App_Themes	1244616	947804
2		Default	418221	529583
3			Images	345601	183982
4			927227	Category	183982
5		Graphite	408174	519053
6			Images	335071	183982
7				Category	183982
8	Controls	3450046	2978149
9		App_LocalResources	2978149
10	Data	33736076702	47917462443
11		Cache	24111918027	23805544416
12			TE9SVE9CQ08	22716482364	1089062052
13				NTEwRzA2MU0	1075986824	13075228
14					MDIwMDAwMDA	5777060	7298168
15						MQ	5777060	1521108
16							MTA	103786
17							MTE	344636
18					MDIwMjA1MDI	11694717	MTI	1072686
19					MDIwMjA1MDI	4587081	7107636
20						MQ	4587081	2520555
21							MTA	59078
22							MTE	186804
23							MTI	591757
24				VEVDSExCUlk	3055050		MTM	1682916
25				VEVDSExCUlk	1527525	1527525
26					MDAwMDAwMDA	1527525
27	Services	323
28	Upgrade	239942	199637
29		App_LocalResources	199637
30	Viewer	2217201	1960936
31		App_LocalResources	1939462
32		Images	21474
Sheet3

In the screenshot, the totals in C4 as well as the data in E18:F18, and D24:E24 should not be there. Totaling should be on the outside of the text-to-columns data structure.

Thank you for the help though - this was a long ways from where I was on my own.

 

[hiker95]

schatham,

I can see that your request is extremely complex, and, that I do not understand what the results should look like, except for your first text file.

Click on the Reply to Thread button, and just put the word BUMP in the post. Then, click on the Post Quick Reply button, and someone else will assist you.