Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Do you know how to generate the following?

Excel table:

+-----------------------+
| A | 1 | 2 | 3 | 4 | 5 |
+-----------------------+
| B | 1 | 2 | 3 | 4 | 5 |
+-----------------------+

I want to turn it into:

+----+
| A1 |
+----+
| A2 |
+----+
| A3 |
+----+
| A4 |
+----+
| A5 |
+----+
| B1 |
+----+
| B2 |
+----+
| B3 |
+----+
| B4 |
+----+
| B5 |
+----+

Can you do that in Excel?

share|improve this question

migrated from stackoverflow.com Aug 11 '11 at 5:42

This question came from our site for professional and enthusiast programmers.

add comment

5 Answers

Here's a method that can produce the combinations that you need; although, this formula won't list them down in one column.

Let's say the data was in A1:F2, like so:

enter image description here

Select a range that's 2 columns by 5 rows below it, like so:

enter image description here

Type this into the Formula box, then press Ctrl + Shift + Enter:

=TRANSPOSE(A1:A2)&TRANSPOSE($B1:$F1)

You'll get this:

enter image description here

You can then just Copy-Paste the data. You'll get virtually the same result by using the array formula

={"A","B"}&{1;2;3;4;5}

Hopefully, someone else can provide a better solution.

share|improve this answer
add comment

It looks like none of these answers are giving exactly what you asked for. You can use this formula to get the "transposed" table in another location.

=OFFSET(K$1,INT((ROW()-6)/5),0) & INDIRECT("R" &  INT((ROW()-6)/5)+1 & "C" & COLUMN()+MOD(ROW()-6,5)+1,FALSE)

To use this formula in your workbook, replace all "6"s with the row number of your target location, replace all "5"s with the width of your table (not counting the header), and replace "K$1" with the address of the top-leftmost cell in your original table (the "A" in your example). Then fill down.

share|improve this answer
add comment

Sure you can. If you highlight the A cells and hit Ctrl+C to copy the test, then in the area you want to transpose it, right click the mouse and select "paste special" then at the bottom right of the option box that appears is "transpose" which will change the direction of the cells from horizontal to vertical.

You can actually do both the A cells and B cells at once, but it will transpose them in adjacent columns, so you'll need to do one final copy paste to put the B under the A values.

share|improve this answer
    
The transpose would only create a column of A,1,2, ... it will not create a column of A1, A2, A3 etc. –  Dheer Aug 11 '11 at 5:29
add comment

First copy 1 to 5 and then use paste special and transpose them to column. Similarly for the vlaues on the second row. Then in the next column write a CONCATNATE formulae to join the fixed value A with the numbers.

share|improve this answer
add comment

Issun and Dheer answers are the best solutions. For completeness, you can also use the TRANSPOSE() formula. See here for more information

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.