I have a spreadsheet where 5 locker combos for each locker were entered but we did them in a list going down the rows. Now they want the five to go across in columns. Is there a way to change these without copying and pasting each one.
-
Could you show some example data? Sounds like you want Paste Special -->Transpose.– Scott CranerJul 22, 2016 at 13:58
-
See here for something similar: stackoverflow.com/questions/38272074/…. Or here: stackoverflow.com/questions/38378863/… Modifying one of those techniques will work for you.– Scott CranerJul 22, 2016 at 14:06
-
I added a pic of my sheet the top is what they want and the bottom is what I have now– BradytJul 22, 2016 at 14:07
-
Unfortunately I am not good with formulas and could not figure out how to modify the one you sent me.– BradytJul 22, 2016 at 14:29
-
It worked great! Thank you so much. Sorry for being so needy but I am slowly learning!!– BradytJul 22, 2016 at 19:32
|
Show 1 more comment
2 Answers
So a quick formula, This is assuming your data starts in column A, if not adjust your columns accordingly:
=IF($A1<>"",INDEX($C:$C,ROW()+COLUMN(A:A)),"")
Adjust your columns accordingly on the $A1 and $C:$C. Leave the COLUMN(A:A) alone as we are using it as a counter and not an actual reference to Column A.
This would go in the cell right next to the first combination, and will return the second combination. Then copy over three more column and down the entire length of the dataset.
Then copy and paste just the values on the whole sheet. Then simple sort on column A, which will bring all the correct rows to the top. Then delete all the rows beneath.
answered Jul 22, 2016 at 14:25
You can use Excel's transpose feature to convert data in columns to rows (and vice-versa):
- Copy the data to be transposed
- Right-click a cell to paste the data TO
On the Paste Special... fly-out menu choose Transpose:
