DISPLAYING DATA SIDEWAYS

[qwerty]

I have a sheet that looks like this

id value
1 a
1 b
1 c
2 d
2 e


and im trying to display it this way....

id value1 value2 value3
1 a b c
2 d e

easy what to do this?

 

[Max]

Assuming the source data is in Sheet1,
cols A and B, from row2 down
and the ids are in groups of 3 in col A

In Sheet2
-----------
Put in B2: =OFFSET(Sheet1!$A$2,ROW(1:1)*3-3+COLUMN()-2,1)
Copy across to D2, then down

For a neater look, suppress zeros from display via:
Tools > Options > View tab > Uncheck "Zero values" > OK

 

[David McRitchie]

Hi "qwerty" <[email protected]>
If they are not in groups of three, see
http://www.mvps.org/dmcritchie/excel/snakecol.htm#joining

You would have to modify to use Cells(row,column) to put then into
actual columns, as I just separated them with commas. Actual
you could use text to columns and separate on comma if it is a
one time thing; otherwise, change the macro.

 

[Soo Cheon Jheong]

qwerty,
Try:

Sheet1:
--------------------------
A B
1 id value
2 1 a
3 1 b
4 1 c
5 2 d
6 2 e
7 3 f
8 4 g
9 4 h
10 4 i
11 5 j
12 5 k
.. . .
.. . .
.. . .


Sheet2:
----------------------------------------------------
A B C D E F
1 id value1 value2 value3 value4 value5
2 1 a b c
3 2 d e
4 3 f
5 4 g h i
6 5 j k
.. . . . .
.. . . . .
.. . . . .


Put the formula in Sheet2!B2,

=IF(COUNTIF(Sheet1!$A:$A,$A2)<COLUMNS($B2:B2),"",
OFFSET(Sheet1!$A$1,MATCH($A2,Sheet1!$A:$A,0)+COLUMNS($B2:B2)-2,1))

then fill right and fill down