How to transfer muti columns values to just 2 columns values in excel.

Which version of Excel do you have?

If it's a later version then you could easily do this using Power Query/Get and Transform, otherwise you would probably be best with code.

PS I'm assuming A1, B1, C1 etc are all in separate cells.

Yes , A1, B1 , C1 are all in separate cell value.
I am using excel 2013, I do not know the Power Query or ..
Anyone can help me do this code?

Thanks

I think if you are using 2013 you would need to install Power Query as an add-in, it's built-in in recent versions.

Anyway, If you could attach a sample workbook with some data I'll post some code.

PS I can do it without the sample workbook but seeing some 'real' date would be handy.:)

PPS It doesn't actually need to be 'real' data, just a mockup of what you are actually dealing with.

Hi Norie:
Thank you very much. I upload the sampledata, the regular is very big.
I would like transfer sheet1 to sheet2.

Thank you again
sampledata.xlsx

Hi Norie:

Thank you very much. It is work for me.

Tim,  

If you get a chance to learn Power Query on your own, may I suggest you try "M is for Data Monkey" by Ken Puls - it's is a very good place to start.  I have no financial interest with Mr. Puls - this is just a personal recommendation.  The amazing thing is after a light week with Ken's book (almost fun to read), you'll know how to solve problems like this in 5 minutes or less.  There are other books on Power Query, that tackle Data mash-up problems more broadly and deeply.  
Here, the thing to know is that Power Query User Interface has a button for "Un-pivot" data; the result automatically generates M language shown below.  If you plug this in Advance Query Editor, you'll get your expected result.  

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
    #"Removed Columns"

Select allOpen in new window

Hi Dan:
Thank you very much. I will check Power Query.