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.

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.