transpose list with offset (Excel 2003)

Topic

New Reply

I have a huge list (60K rows) of a hex dump (8 chars per cell). I need to group these from one column (as they are now), into a table 20 wide by 31 tall. I started to do this manually bit there’s no way I can finish it any day soon. I need some help on how to automate it with a better formula, probably offset? The numbers are 31 cells separated by 4 rows so the pattern is consistent.

aa
bb
cc
dd
ee

mm
nn
oo
pp
qq

Output:
aa mm
bb nn
cc oo
dd pp
ee qq

So it is similar to transposing a set of data from rows to cols but with an offset.

How can I do this quickly (and smarter than the dumb way I’m doing it now)? The attached file is 25% of the actual data.

Deb

Reply | Quote

Replies

You can’t do this with formulas on the same sheet – you’d get circular references. Moreover, such a large number of formulas would make the workbook sluggish.
Here is a macro that will transform the data within the same sheet:

Sub Transform()
Const NumRows = 31
Const NumCols = 20
Const Gap = 3
Dim i As Long
Dim r As Long
Dim c As Long
Dim s As Long
Dim m As Long
Dim lngStrips As Long

' Last used row
m = Cells(Rows.Count, 2).End(xlUp).Row
' Number of 31 cell "strips"
lngStrips = -Int(-(m + 2) / (NumRows + Gap))

Application.ScreenUpdating = False

' Loop through the strips
For i = 2 To lngStrips
' Calculate start row
s = (i - 1) * (NumRows + Gap) + 2
' Calculate destination row
r = ((i - 1) NumCols) * (NumRows + Gap) + 2
' And destination column
c = (i - 1) Mod 20 + 2
' Cut strip to new position
Range(Cells(s, 2), Cells(s + NumRows - 1, 2)).Cut Destination:=Cells(r, c)
Next i

Application.ScreenUpdating = True
End Sub

Reply | Quote

!!!!!!!!!!! I am in awe !!!!!!!!!!!!! Dang that was quick.Yes I was using formulas to capture the rows and trying to use offset but as you said, that only works for a bit since I’m not moving the data but adding to it on the page and I’ll run out of rows.

You saved me a TON of time, thank you so much.

Deb

Reply | Quote

Hi me again… I found out the people who wanted this data transformed told me the wrong rules. Instead of appending down the row of data byte by byte (8 bits per cell) they want it on a bit-by-bit level for a total of 160 bits across.

a1111111
b2222222
c3333333
d4444444
e5555555
f6666666
g7777777
h8888888

So the data would turn into abcdefgh 12345678 etc for total of 160 bits across in 1 row. (The actual data is ‘1’ and 0 but easier to understand this way).

Now this means I’ll have to use =left() to grab the most significant bit (on far left) each time down the list, or re-arrange the data such that each bit is in its own cell. One all the most significant bits are exhausted, I move over and grab the next bit (position 7) and repeat the concatenation, followed by all bits in position 6, etc.

I attached another sample data set. My head is spinning trying to modify your code to think in bits and not bytes. Can you please look at this again?

Deb

Reply | Quote

I don’t understand how the data and the requirements fit together. The data are organized in blocks of 31 rows, and you want to read bits in blocks of 160. 160 is not divisible by 31, so
Should the blank rows between the blocks of 31 rows be ignored?

Reply | Quote

You’re right in your observation, my explanation is faulty. We need a total of 160 bits across in one row and then jump down to another row. The fact that it’s broken into groups of 31 is not significant for the transformation. That’s the way the data was dumped by the equipment. I should of removed the blank rows beforehand. The only thing about 31 rows is the output should be 160 bits across (10 cells of 16 bits each) and 31 rows of data in each table.

Data set #1
row1. cell1 = 0000000011111111 cell2 = 16 bits, cell3 = 16 bits ...... for total of 160 bits (10 bits per cell)
row2. cell1 = 0000000011111111 cell2 = 16 bits, cell3 = 16 bits
row31. cell1 = 0000000011111111 cell2 = 16 bits, cell3 = 16 bits

some blank rows, data set #2

row1. cell1 = 0000000011111111 cell2 = 16 bits, cell3 = 16 bits
row2. cell1 = 0000000011111111 cell2 = 16 bits, cell3 = 16 bits
row31. cell1 = 0000000011111111 cell2 = 16 bits, cell3 = 16 bits

Reply | Quote

Why don’t they tell the equipment to output the data in the correct format?

Reply | Quote

Wish it was that simple, but this is how it’s captured. It’s time sliced sampled data at high speed and so every 32nd sample is the state at that time slice.

Deb

Reply | Quote

I still don’t understand how exactly the data should be read. Should we read the first bit in every row (from row 2 down to row 60,000 or whatever the last row is), then the second bit in every row? Or something else?

Reply | Quote

yes exactly, read the first bit on every column as far down as it goes (actually read it in 16 bit chunks since it needs to be grouped in 16 bits). Ever 16 bits read vertically is stuffed in one cell in a table. The next group of 16 bits (read vertically starting at left most bit – the most significant bit), gets read next until the list of exhausted for that bit position.

Then you grab all bits in the 7th bit position and concatenate into groups of 16, repeat for the 6th bit position, 5th, etc.

Reply | Quote

Try the code in the attached text file. I have interspersed the code with comments.

Reply | Quote

I ran it but it’s not quite right yet. Close though

The first 16 bits should be 0011 1100 0011 1101. This is the bits in the first col of bytes, 8th position on far left side (bit8, bit7, bit6, bit5, …, bit1)

This comes from the first bit (far left side is the most significant bit) row 2, 36, 70, 104, 138, 172, 206, 240, 274, 308, 342, 376, 410, 444, 478, 512, 546.

87654321
--------
00000000
00000000
10101010
10101010
10101010
10101010
00000000
00000000
^
|

00111100

Reply | Quote

Sorry, you’ve lost me. You will have to describe much more precisely and completely what you want. (I still think you should demand that the data are organized correctly to start with)

Reply | Quote

Ok, I’m not sure how else to explain w/o talking in person Changing how the data is fed to us isn’t an option, that’s how the equipment was designed.

The bits need to be in a vertical fashion like you are doing it now, you’re very close to solving this mess.

Parse all bits in the bit8 position first through all 60K rows. When that’s exhausted jump to bit7 position and march down that column of bits. It’s as if each bit was in its own column and we’re appending a bit from the next row down (in the same col).

Example:

87654321 (bit order)
----------
a1aaaaaa
b2bbbbbb
c3cccccc
d4dddddd
e5eeeeee
f6ffffff
g7gggggg
h8hhhhhh

So if I was to transpose this into the 16 bit size I’d take data from bit8 position (1st col of data) and write “abcdefgh12345678” into one cell for 16 bits.

Reply | Quote

> Parse all bits in the bit8 position first through all 60K rows. When that’s exhausted jump to bit7 position and march down that column of bits.

That is exactly what the code I wrote does. So if it is not what you want, you will have to describe what yo do want!

Reply | Quote

I understand your frustration with my request and I apologize. I went back to the engineers and created a small test sample which was easier to understand and I have it attached (on sheet called Sample, it has two blocks of 4 bit data, the other sheet is the actual data). Each row in each block has to be matched with the same row in the next block but bit by bit. So bit8 in row 1/block1 gets matched with bit8 of row1/block2, etc. until all bit8’s are accounted for.

When no more bit8 data, jump over to bit7 and grab row1/block1 + row1/block2, etc. I wrote the formula out on sheet Sample. This is the last try I’ll take at this data dump. I tried to edit the code (which is still in the attached workbook), to jump to the next block (add another loop?) but it didn’t work. You’d grab the first bit from first row, jump 34 rows to grab the next one, jump 34 rows grab next one, etc. until reach last row. You have the output format correct in groups of 16 bits per cell for total of 160 bits in one row (for total of 31 rows) per table.

Thanks again, Deb

Reply | Quote

Does the code in the attached text file do what you want?

Reply | Quote

Sorry for taking so long to get back to you on your solution. YES, it was exactly what we needed and worked great. It proved to us that the set up the customer was using was incorrect. It saved us time/money flying an engineer to Brazil and even worse, waiting for equipment to pass through customs.

Thanks so much for your patience and diligence.
Deb

Reply | Quote

I’m glad it worked out in the end!

Reply | Quote