Search code examples
excelexcel-formulaworksheet-functionunpivotnormalize

Combining multiple columns into a single column

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.
Solution

  • 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.