|
|
|
|
New Member
|
|
Apr 1, 2011, 06:18 AM
|
|
Columns of data in Excel into rows of data
Hi, I have 2 columns (column A, column B) of data in Excel. I need to make the data into row in such arrangement column A1 goes to row 1, column B1 goes to row 2, column A2 goes to row 3, column B2 goes to row 4, and so on). I have over 1000 records in column A and column B to make that happen. Any idea how to use Excel to achieve that? Thanks.
|
|
|
Software Expert
|
|
Apr 1, 2011, 03:53 PM
|
|
1) In C1 put the number 1, in C2 put the number 2.
2) Doubleclick the lower right corner of C2 and the number sequence will be continued to the bottom of the data set.
3) Copy the complete set of numbers, paste them at the bottom of the first set of C numbers, giving you two complete sets of numbers
4) Cut column B values and paste below column A values
5) Delete column B
6) Now Sort A:B by the numbers in column B
7) Erase the column B numbers
|
|
|
Computer Expert and Renaissance Man
|
|
Apr 1, 2011, 03:59 PM
|
|
You can also copy the range and then use Paste Special to transpose the range from a columns to rows.
|
|
|
New Member
|
|
Apr 2, 2011, 12:40 AM
|
|
Thanks for the suggestions. I tried it however my data set is not sequential numbers or alphabet. It's a randomly generated alpha-numerics. So in Column A, I have 1000 rows of alpha-numerics and in column B, I also have 1000 rows of alpha-numerics. I need to re-arrange these rows in Zic-zac way if I can call it, because it has to be cell A1 into row C1, cell B2 into row C2, cell A2 into row C3, cell B2 into row C4, cell A3 into row C5 and so on you get drift... zic-zac, zic-zac... please throw in suggestion if VB macro can do this or Access can. Thanks.
|
|
|
Software Expert
|
|
Apr 2, 2011, 06:55 PM
|
|
My suggestion in post #3 does exactly what you've described. Here's another version of the same method, but it creates the final list in column C like you want.
1) Copy the column A values into column C, pasting at C1, downward
2) In D1, place the number 1, in D2, place the number 2
3) Double-click the lower right corner of D2 to copy the new set of numbers down column D.
4) Copy the column D sequential numbers you just created, and paste a second set of the numbers below the first, paste into the first empty cell in column D
5) Copy the column B values into column C, pasting at the next empty
6) Highlight columns C:D together, sort them ascending by column D (THIS IS THE STEP THAT PUTS YOUR VALUES IN YOUR DESIRED ORDER).
7) Delete the numbers you added in column D.
Total time... 15 seconds. Don't analyze this, just do it exactly as told, this is an OLD tried and true trick to zig-zag/copy values into a single column. It works and it's easy. It doesn't matter what your column A:B values are, this will zig-zag them.
|
|
|
New Member
|
|
Apr 3, 2011, 02:00 AM
|
|
Comment on JBeaucaire's post
It was my mistake I mis-read your instruction in post#3. Re-tried it again and it works like magic as you've describe! You're a legend!
|
|
|
Software Expert
|
|
Apr 4, 2011, 02:48 PM
|
|
Not my trick, I learned the "number each group of data the same, then sort" trick from someone else on another forum. We are a village. ;)
|
|
Question Tools |
Search this Question |
|
|
Add your answer here.
Check out some similar questions!
Free software to transpose data from rows to columns
[ 1 Answers ]
Asifabad Boath Chennur
Asifabad Boath Chennur
Asifabad Boath Chennur
Asifabad Boath Chennur
Asifabad Boath Chennur
Asifabad Chennur
Asifabad Chennur
Asifabad Chennur
Asifabad Chennur
Asifabad Chennur
Transpose rows into columns in a excel spreadsheet
[ 3 Answers ]
Can anyone help
1) how to transpose the data in rows into columns and columns into rows in a Excel spread sheet?
2) how to change the numerical order of data in rows/ columns in a Excel spread sheet? i.e.. from the first row to last row and last row to first row ?
Thank "U'
View more questions
Search
|