Combining multiple columns into a single column

Question

Using Excel, how can I create a single column using multiple columns?

For example: I might need to eliminate columns 2012-2014 and create a column for year. At the same time, I need to keep the information in 2012-2014 and create a single column for these to align to each row (Name, Country, Grade, Year, data):

Name        Country   Grade 2012        2013        2014
Smith       France    A     0.333194233 0.64347979  0.72795544
Adams       Germany   D     0.307532421 0.580823282 0.851274645
Franklin    Americas  C     4.597219783 7.983514354 10.79688839
Roosevelt   Americas  B     0.615064841 1.089043653 1.502249374
Washington  Australia A     0.261792462 0.456573955 0.620206846

So it should look like this:

Name        Country   Grade Year Data
Smith       France    A     2012 0.333194233
Smith       France    A     2013 0.64347979
Smith       France    A     2014 0.72795544 etc.

score 0 · Accepted Answer · edited May 23 '17 at 12:30

0

Assuming Name is in A1, insert a new ColumnD and in D1 copied down to suit:

=A1&"#"&B1&C1

then unpivot (details with images here selecting D1:Gn (n to suit)).

After step 8. insert three new columns in the Table (to become B:D) and in B2:

 =LEFT(A2,FIND("#",A2)-1)

in C2:

=MID(A2,FIND("#",A2)+1,LEN(A2)-LEN(B2)-2)

in D2:

=RIGHT(A2)

Tidy up to suit.

edited May 23 '17 at 12:30

Community

1
1

answered Oct 31 '15 at 10:40

pnuts

58,317
11
87
139

score -1 · Answer 2 · edited Oct 31 '15 at 10:42

-1

Have you tried TRANSPOSE in 'Paste Special'? or do you want it dynamic?

Link

Found solution in: LINK It works!

Create this module and run the Macro:

Public Sub tranpose()
Dim i   As Long, _
    LR1 As Long, _
    LR2 As Long, _
    LC  As Long
Application.ScreenUpdating = False
Columns(1).Insert Shift:=xlToRight
LR2 = Range("B" & Rows.Count).End(xlUp).row
Range("A1:A" & LR2).Value = Chr(65)
LC = Cells(1, Columns.Count).End(xlToLeft).Column
For i = 3 To LC
    LR1 = Range("A" & Rows.Count).End(xlUp).row + 1
    LR2 = Cells(Rows.Count, i).End(xlUp).row
    Range(Cells(1, i), Cells(LR2, i)).Cut Destination:=Cells(LR1, 2)
    Range(Cells(LR1, 1), Cells(LR1 + LR2 - 1, 1)).Value = Chr(63 + i)
Next i
Application.ScreenUpdating = True
End Sub

edited Oct 31 '15 at 10:42

pnuts

58,317
11
87
139

answered Oct 30 '15 at 17:30

Tiago Matos

59
10

The TRANSPOSE function returns a vertical range of cells as a horizontal range, or vice versa. I need to create more rows of information. As you can see, there is only one row with "Smith" and his information. I need three rows of Smith, same information, however with additional columns, years and data. This needs to be done for about 3000 rows of information. – Starbucks Oct 30 '15 at 18:39
[link](http://www.mrexcel.com/forum/excel-questions/532957-transpose-rowsg-keeping-heading-column-data-column-b.html) – Tiago Matos Oct 31 '15 at 01:02

Combining multiple columns into a single column

2 Answers2