"Make a table"

  • Thread starter Rodrigo Ferreira
  • Start date
R

Rodrigo Ferreira

I have a list (something like this):

aaa=2
ddd=3
ttt=4
ooo=6
aaa=4
ddd=5
ttt=6
ooo=7
aaa=3
ddd=5
ttt=7
ooo=3
....

And I wanto to make something like this

aaa ddd ttt ooo
2 3 4 6
4 5 6 7
3 5 7 3
....

how can I do this?


Rodrigo Ferreira
 
R

Roger Govier

Hi Rodrigo

Copy A1:A4, move cursor to C1, Paste Special>Transpose
In cell C2 enter
=OFFSET($B$1,((ROW()-2)*4)+(COLUMN()-3),0)
copy across through D2:F2
Copy C2:F2 down as far as required
 
R

Rodrigo Ferreira

Great.
But I still have a problem. Sometimes, some lines doesn't have one
parameter:
ddd=3
ttt=4 -- Line not exist
ooo=6
aaa=4
ddd=5
ttt=6
ooo=7
aaa=3
ddd=5
ttt=7
ooo=3
....


Other problem:
Now I have the table:

aaa ddd ttt ooo
2 3 4 6
4 5 6 7
3 5 7 3

And I have to return like the first:
ddd=3
ttt=4
ooo=6
aaa=4
ddd=5
ttt=6
ooo=7
aaa=3
ddd=5
ttt=7
ooo=3

How can I do this?

Rodrigo Ferreira
 
M

Max

One way for your "other" problem ..

Assume table below is in Sheet1 in A1:D4
aaa ddd ttt ooo
2 3 4 6
4 5 6 7
3 5 7 3
Click to expand...

In another sheet,
Put in A1:
=OFFSET(Sheet1!$A$1,,MOD(ROW(A1)-1,4))

Put in B1:
=OFFSET(Sheet1!$A$2,INT((ROW(A1)-1)/4),MOD(ROW(A1)-1,4))
Select A1:B1, fill down until zeros appear in col B, signalling exhaustion
of data extract.

This will yield in A1:B12 the required results:

aaa 2
ddd 3
ttt 4
ooo 6
aaa 4
ddd 5
ttt 6
ooo 7
aaa 3
ddd 5
ttt 7
ooo 3

Adapt to suit ..

....
 
Top