Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automated Transposition
Hi,
I have a very long Excel spreadsheet, in excess of 13,000 rows, generated from a network capture. The original datasheet has multiple columns, but I’m only interested in two: * Column G: which contains attributes, which are to become the column headers in the new spreadsheet (e.g. length; timestamp; source address etc.) * Column I: which contains data (the variables) from each frame (e.g. 64; 000897; 10.10.0.4 etc.) Manual transposition works, but is unfeasible for this amount of data, I’ve seen various posts on automated transposition but I have an additional issue in that each frame is an inconsistent total length. That is Frame 01 would be transposed from A1:P1, but Frame 02 would go from A2:AA2, which appears to be an issue for most VBScripts I’ve seen. There is however some consistency in that the data I which to use consistently runs for 46 rows, and the start of the next frame is flagged by the start attribute “num”. Therefore the attributes are transposed from Column G to row 1, only once to form the headers. IF Field = "frame.number" THEN transpose to column A; IF Field = "len" THEN transpose to column B AND rename "frame.length"; IF Field = "frame.time" THEN transpose to column C; Etc, etc. And the variables are transposed under the relevant Attribute (e.g. column header): IF Field = "frame.number" THEN transpose from column I to column A row 2; REPEAT for row 2 +n UNTIL END; IF Field = "len" THEN transpose from column I to column B row 2; REPEAT for row 2 +n UNTIL END; IF Field = "frame.time" THEN transpose from column I to column C row 2; REPEAT for row 2 +n UNTIL END; What I then end up with is a new spreadsheet that has: frame.number frame.length frame.time Further Attributes ........ 1 60 000000000 .......... 2 64 000000002 .......... 3 62 000000004 .......... Further variables ....... Does anyone have any suggestions as to the best method to attack this problem? Thanks Doug |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automated Transposition
for your need to get the last column
for i =cells(columns.count, "a").end(xlup).row lastcol=cells(i,columns.count,i).end(xltoleft.row msgbox lastcol next i -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... This will give you the column length for each column for i =cells(1,columns.count).end(xlToLeft).count lastrow=cells(rows.count,i).end(xlup).row msgbox lastrow next i -- Don Guillett Microsoft MVP Excel SalesAid Software "Doug" wrote in message ... Hi, I have a very long Excel spreadsheet, in excess of 13,000 rows, generated from a network capture. The original datasheet has multiple columns, but I’m only interested in two: * Column G: which contains attributes, which are to become the column headers in the new spreadsheet (e.g. length; timestamp; source address etc.) * Column I: which contains data (the variables) from each frame (e.g. 64; 000897; 10.10.0.4 etc.) Manual transposition works, but is unfeasible for this amount of data, I’ve seen various posts on automated transposition but I have an additional issue in that each frame is an inconsistent total length. That is Frame 01 would be transposed from A1:P1, but Frame 02 would go from A2:AA2, which appears to be an issue for most VBScripts I’ve seen. There is however some consistency in that the data I which to use consistently runs for 46 rows, and the start of the next frame is flagged by the start attribute “num”. Therefore the attributes are transposed from Column G to row 1, only once to form the headers. IF Field = "frame.number" THEN transpose to column A; IF Field = "len" THEN transpose to column B AND rename "frame.length"; IF Field = "frame.time" THEN transpose to column C; Etc, etc. And the variables are transposed under the relevant Attribute (e.g. column header): IF Field = "frame.number" THEN transpose from column I to column A row 2; REPEAT for row 2 +n UNTIL END; IF Field = "len" THEN transpose from column I to column B row 2; REPEAT for row 2 +n UNTIL END; IF Field = "frame.time" THEN transpose from column I to column C row 2; REPEAT for row 2 +n UNTIL END; What I then end up with is a new spreadsheet that has: frame.number frame.length frame.time Further Attributes ........ 1 60 000000000 .......... 2 64 000000002 .......... 3 62 000000004 .......... Further variables ...... Does anyone have any suggestions as to the best method to attack this problem? Thanks Doug |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automated Transposition
for your need to get the last column
for i =cells(columns.count, "a").end(xlup).row lastcol=cells(i,columns.count,i).end(xltoleft).col msgbox lastcol next i -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... for your need to get the last column for i =cells(columns.count, "a").end(xlup).row lastcol=cells(i,columns.count,i).end(xltoleft.row msgbox lastcol next i -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... This will give you the column length for each column for i =cells(1,columns.count).end(xlToLeft).count lastrow=cells(rows.count,i).end(xlup).row msgbox lastrow next i -- Don Guillett Microsoft MVP Excel SalesAid Software "Doug" wrote in message ... Hi, I have a very long Excel spreadsheet, in excess of 13,000 rows, generated from a network capture. The original datasheet has multiple columns, but I’m only interested in two: * Column G: which contains attributes, which are to become the column headers in the new spreadsheet (e.g. length; timestamp; source address etc.) * Column I: which contains data (the variables) from each frame (e.g. 64; 000897; 10.10.0.4 etc.) Manual transposition works, but is unfeasible for this amount of data, I’ve seen various posts on automated transposition but I have an additional issue in that each frame is an inconsistent total length. That is Frame 01 would be transposed from A1:P1, but Frame 02 would go from A2:AA2, which appears to be an issue for most VBScripts I’ve seen. There is however some consistency in that the data I which to use consistently runs for 46 rows, and the start of the next frame is flagged by the start attribute “num”. Therefore the attributes are transposed from Column G to row 1, only once to form the headers. IF Field = "frame.number" THEN transpose to column A; IF Field = "len" THEN transpose to column B AND rename "frame.length"; IF Field = "frame.time" THEN transpose to column C; Etc, etc. And the variables are transposed under the relevant Attribute (e.g. column header): IF Field = "frame.number" THEN transpose from column I to column A row 2; REPEAT for row 2 +n UNTIL END; IF Field = "len" THEN transpose from column I to column B row 2; REPEAT for row 2 +n UNTIL END; IF Field = "frame.time" THEN transpose from column I to column C row 2; REPEAT for row 2 +n UNTIL END; What I then end up with is a new spreadsheet that has: frame.number frame.length frame.time Further Attributes ........ 1 60 000000000 .......... 2 64 000000002 .......... 3 62 000000004 .......... Further variables ...... Does anyone have any suggestions as to the best method to attack this problem? Thanks Doug |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex Transposition | Excel Discussion (Misc queries) | |||
Automating transposition exercise | Excel Worksheet Functions | |||
Transposition impossibility? | Excel Discussion (Misc queries) | |||
Transposition | Excel Worksheet Functions | |||
Transposition of data | Excel Programming |