Automated Transposition

[Doug]

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 A11, 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

 

[Don Guillett]

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
(e-mail address removed)
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 A11, 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

 

[Don Guillett]

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]

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