What is the quick way to transfer data from row to column

[Sian1]

Request ID	101	102	104	105	116	134	135	143	147	148	149
R-042A04GC700000											149
R-023A0DAB800000											149
R-042A059Q100000								143			149
R-042A059Q100000
R-042A059Q100000
R-005A0HFX800001		102		105							149
R-042A059Y400000			104			134					149
R-042A059Y400000											149
R-023A0BE0200000											149
R-001A0Y3T700000						134	135
R-001A0Y3T700000					116			143	147		149
R-023A0BE0200000	101

 

[Sian1]

So, just this then?

Book1 (version 2).xlsb
	A	B
1	Request ID	Code
2	R-042A04GC700000	149
3	R-023A0DAB800000	149
4	R-042A059Q100000	143
5	R-042A059Q100000	149
6	R-005A0HFX800001	102
7	R-005A0HFX800001	105
8	R-005A0HFX800001	149
9	R-042A059Y400000	104
10	R-042A059Y400000	134
11	R-042A059Y400000	149
12	R-042A059Y400000	149
13	R-023A0BE0200000	149
14	R-001A0Y3T700000	134
15	R-001A0Y3T700000	135
16	R-001A0Y3T700000	116
17	R-001A0Y3T700000	143
18	R-001A0Y3T700000	147
19	R-001A0Y3T700000	149
20	R-023A0BE0200000	101
Sheet12

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    unpivot = Table.UnpivotOtherColumns(Source, {"Request ID"}, "Code", "Value"),
    selectColumns = Table.SelectColumns(unpivot,{"Request ID", "Code"}),
    Type = Table.TransformColumnTypes(selectColumns,{{"Request ID", type text}, {"Code", Int64.Type}})
in
    Type

that is it. let me try. thanks a bunch

 

[Sian1]

So, just this then?

Book1 (version 2).xlsb
	A	B
1	Request ID	Code
2	R-042A04GC700000	149
3	R-023A0DAB800000	149
4	R-042A059Q100000	143
5	R-042A059Q100000	149
6	R-005A0HFX800001	102
7	R-005A0HFX800001	105
8	R-005A0HFX800001	149
9	R-042A059Y400000	104
10	R-042A059Y400000	134
11	R-042A059Y400000	149
12	R-042A059Y400000	149
13	R-023A0BE0200000	149
14	R-001A0Y3T700000	134
15	R-001A0Y3T700000	135
16	R-001A0Y3T700000	116
17	R-001A0Y3T700000	143
18	R-001A0Y3T700000	147
19	R-001A0Y3T700000	149
20	R-023A0BE0200000	101
Sheet12

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    unpivot = Table.UnpivotOtherColumns(Source, {"Request ID"}, "Code", "Value"),
    selectColumns = Table.SelectColumns(unpivot,{"Request ID", "Code"}),
    Type = Table.TransformColumnTypes(selectColumns,{{"Request ID", type text}, {"Code", Int64.Type}})
in
    Type

one more question is this Query code?

 

[MARK858]

one more question is this Query code?

Yes it is

 

[Sian1]

Yes it is

Thanks Mark, unfortunate i am not very familiar with it. is it possible to do it in VBA?

 

[MARK858]

It is but Power Query/Get and Transform is relatively easy to use and is suited for the task.
What version of Excel are you using? btw you can add these details to your profile.

 

[Sian1]

It is but Power Query/Get and Transform is relatively easy to use and is suited for the task.
What version of Excel are you using? btw you can add these details to your profile.

we are using Excel 365

 

[MARK858]

Select your table
On the Data Tab click From Table/Range
Click Advanced Editor then delete any code there.
Copy/Paste the code lrobbo314 posted and click done
Click Close and Load dropdown then click Close and Load to
Choose where you want the data and click OK.

 

[Sian1]

So, just this then?

Book1 (version 2).xlsb
	A	B
1	Request ID	Code
2	R-042A04GC700000	149
3	R-023A0DAB800000	149
4	R-042A059Q100000	143
5	R-042A059Q100000	149
6	R-005A0HFX800001	102
7	R-005A0HFX800001	105
8	R-005A0HFX800001	149
9	R-042A059Y400000	104
10	R-042A059Y400000	134
11	R-042A059Y400000	149
12	R-042A059Y400000	149
13	R-023A0BE0200000	149
14	R-001A0Y3T700000	134
15	R-001A0Y3T700000	135
16	R-001A0Y3T700000	116
17	R-001A0Y3T700000	143
18	R-001A0Y3T700000	147
19	R-001A0Y3T700000	149
20	R-023A0BE0200000	101
Sheet12

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    unpivot = Table.UnpivotOtherColumns(Source, {"Request ID"}, "Code", "Value"),
    selectColumns = Table.SelectColumns(unpivot,{"Request ID", "Code"}),
    Type = Table.TransformColumnTypes(selectColumns,{{"Request ID", type text}, {"Code", Int64.Type}})
in
    Type

yea it works

now, different request as this is not one time thing, how could i automate it :P

 

[MARK858]

You don't need to automate it, you just refresh it.

 

[Sian1]

You don't need to automate it, you just refresh it.

thanks

While i have you here, i am having the problem now. I thought the results come in correctly but it just repeat all the code for all request ID. it should only return one value the sample result
like this

Request ID	Code
R-042A04GC700000	149

instead it returns with all code from the header, did i do something wrong?
sample results:

Request ID	Code
R-042A04GC700000	101
R-042A04GC700000	102
R-042A04GC700000	104
R-042A04GC700000	105
R-042A04GC700000	116
R-042A04GC700000	134
R-042A04GC700000	135
R-042A04GC700000	143
R-042A04GC700000	147
R-042A04GC700000	148
R-042A04GC700000	149
R-023A0DAB800000	101
R-023A0DAB800000	102
R-023A0DAB800000	104
R-023A0DAB800000	105
R-023A0DAB800000	116
R-023A0DAB800000	134
R-023A0DAB800000	135
R-023A0DAB800000	143
R-023A0DAB800000	147
R-023A0DAB800000	148
R-023A0DAB800000	149

Sample:

Request ID	101	102	104	105	116	134	135	143	147	148	149
R-042A04GC700000											149
R-023A0DAB800000											149
R-042A059Q100000								143			149
R-042A059Q100000