Excel Formula to change Excel data vertical to horizontal

B

bbcoachbradley

Joined
Apr 3, 2008
Messages
1
Reaction score
0
I have several thousands of lines so copy paste is not an option plus nothing is regular. Here is an example:

ID # Per Room#
22222 1 F 223
22222 2 C 223
22222 3 E 208
22222 4 E 206
22222 5 B 216
22222 6 A 102
22222 7 E 122
42939 2 E126
43281 2 A102
46896 1 C 143
46896 2 D 224
46896 3 A 102
46896 4 D 215
46896 5 D 215
46896 6 D 215
46896 7 D 215
51126 1 C 101
51126 2 C 102
51126 3 D 224
51126 4 A 104
51126 5 D 229

I need to get information to look like:
22222 1 F223 2 C223 3 E208 4 E206 5 B216 6 A102 7 E122
42939 2 E126
43281 2 A102
46896 1 C143 2 D224 3 A102 4 D215 5 D215 6 D215 7 D215
51126 1 C101 2 C102 3 D224 4 A104 5 D229

Any suggestions would be greatly appreciated. I have spent too many hours on this so far to no avail.
 
To change your data from vertical to horizontal and visa versa... you will have to use the "paste special" option.

-Copy your data using Ctrl+C
-Choose the cell where you want to paste your data
-Go to the Edit Menu and choose "Paste Special"
-In the paste special dialog box choose "All" and click the "Transpose" option...and then click ok.

There you go, Bobs your uncle......your data has been transposed from vertical to horizontal.
 
I think you need to set up a pivot table here. Switching from rows to columns will not produce the example where the rows are changed to horizontal lines according to the first cell value and then the first cell value only appears once.


I am not yet sufficiently familiar with pivot tables to tell you how to set this up.
 
Back
Top