Here is a sample of the data I am using.
I am importing it from a text file that is pipe delimited. The data represents a folder structure. Items in the same column are on the same level, those that are 1+ columns offset are subfolders of the folder one level to the left, and so on. There are a variable number of both rows and columns, but both would be less than the limits of Excel.
Unless there is some shorter way to do this, I believe that a macro would be the best way to do what I want to do.
My workbook has a sheet in it named "RawData". When a new file is created, I would import it into this worksheet (after clearing the previous contents). The imported data would always go in $A$1, with no header row. Once that is done, I'd like to go to another worksheet in this book named "FolderInfo" and clear any previous contents in it except for formulas in Columns A & B. Then, I would want to copy everything in the "RawData" worksheet into the "FolderInfo" sheet beginning in cell FolderInfo:$C$2 maintaining the relative offset of those columns in the "FolderInfo" sheet.
Once the data is copied/replicated into the new sheet, it would look like this:
Worksheet RawData will always have a variable number of rows & columns, and blanks will be in the data, but acceptable/necessary blank cells will always be to the left of occupied cells. To the best of my knowledge, the data should be in pairs that would be in cells adjacent to one another in the same row.
The data represents folder name & size, and the number of columns over indicates which folder it is a subfolder of.
I have another post in the forum describing this data structure where I was attempting to roll up subfolder totals & be able to drill down several levels (not yet figured out). This is where I am working on Plan B - another alternative to that.
| Excel 2010 | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | Myfolder | 5800 | ||||
| 2 | Bin | 1800 | ||||
| 3 | de | 40000 | ||||
| 4 | en | 50000 | ||||
| 5 | Random1 | 5043 | ||||
| 6 | Random2 | 5044 | ||||
Sheet1 | ||||||
I am importing it from a text file that is pipe delimited. The data represents a folder structure. Items in the same column are on the same level, those that are 1+ columns offset are subfolders of the folder one level to the left, and so on. There are a variable number of both rows and columns, but both would be less than the limits of Excel.
Unless there is some shorter way to do this, I believe that a macro would be the best way to do what I want to do.
My workbook has a sheet in it named "RawData". When a new file is created, I would import it into this worksheet (after clearing the previous contents). The imported data would always go in $A$1, with no header row. Once that is done, I'd like to go to another worksheet in this book named "FolderInfo" and clear any previous contents in it except for formulas in Columns A & B. Then, I would want to copy everything in the "RawData" worksheet into the "FolderInfo" sheet beginning in cell FolderInfo:$C$2 maintaining the relative offset of those columns in the "FolderInfo" sheet.
Once the data is copied/replicated into the new sheet, it would look like this:
| Excel 2010 | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | F1 Col | F2 Col | Col C | Col D | Col E | Col F | Col . | ||
| 2 | Formula1 | Formula2 | Myfolder | 5800 | |||||
| 3 | Formula1 | Formula2 | Bin | 1800 | |||||
| 4 | Formula1 | Formula2 | de | 40000 | |||||
| 5 | Formula1 | Formula2 | en | 50000 | |||||
| 6 | Formula1 | Formula2 | Random1 | 5043 | |||||
| 7 | Formula1 | Formula2 | Random2 | 5044 | |||||
Sheet1 (6) | |||||||||
Worksheet RawData will always have a variable number of rows & columns, and blanks will be in the data, but acceptable/necessary blank cells will always be to the left of occupied cells. To the best of my knowledge, the data should be in pairs that would be in cells adjacent to one another in the same row.
The data represents folder name & size, and the number of columns over indicates which folder it is a subfolder of.
I have another post in the forum describing this data structure where I was attempting to roll up subfolder totals & be able to drill down several levels (not yet figured out). This is where I am working on Plan B - another alternative to that.
