# Thread: transpose list with offset (Excel 2003)

1. ## transpose list with offset (Excel 2003)

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 <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

2. ## Re: transpose list with offset (Excel 2003)

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:
<code>
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</code>

3. ## Re: transpose list with offset (Excel 2003)

!!!!!!!!!!! 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.

<img src=/S/bow.gif border=0 alt=bow width=15 height=15> <img src=/S/bow.gif border=0 alt=bow width=15 height=15> <img src=/S/bow.gif border=0 alt=bow width=15 height=15> <img src=/S/bow.gif border=0 alt=bow width=15 height=15> <img src=/S/bow.gif border=0 alt=bow width=15 height=15> <img src=/S/bow.gif border=0 alt=bow width=15 height=15> <img src=/S/bow.gif border=0 alt=bow width=15 height=15> <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

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

Deb

4. ## Re: transpose list with offset (Excel 2003)

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

5. ## Re: transpose list with offset (Excel 2003)

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 <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
Should the blank rows between the blocks of 31 rows be ignored?

6. ## Re: transpose list with offset (Excel 2003)

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.

<pre>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</pre>

7. ## Re: transpose list with offset (Excel 2003)

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

8. ## Re: transpose list with offset (Excel 2003)

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

9. ## Re: transpose list with offset (Excel 2003)

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?

10. ## Re: transpose list with offset (Excel 2003)

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.

11. ## Re: transpose list with offset (Excel 2003)

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

12. ## Re: transpose list with offset (Excel 2003)

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.

<pre>87654321
--------
00000000
00000000
10101010
10101010
10101010
10101010
00000000
00000000
^
|

00111100
</pre>

13. ## Re: transpose list with offset (Excel 2003)

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)

14. ## Re: transpose list with offset (Excel 2003)

Ok, I'm not sure how else to explain w/o talking in person <img src=/S/smile.gif border=0 alt=smile width=15 height=15> 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:<pre>87654321 (bit order)
----------
a1aaaaaa
b2bbbbbb
c3cccccc
d4dddddd
e5eeeeee
f6ffffff
g7gggggg
h8hhhhhh</pre>

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.

15. ## Re: transpose list with offset (Excel 2003)

> 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!

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•