Transpose & Repeat multiple columns & rows-CodePudding

I have the following data that im attempting to duplicate and transpose but im having issues with doing that. Transpose doesnt seem to quite get me there.

ColumnA	ColumnB	ColumnC	ColumnD	ColumnE	Column..BM
FruitOne	FruitTwo	FruitThree	2000	2001	...2022
Apples	Bananas	Grapes	RandomValueOne	RandomValueTwo	...RandomValueN

But I want the data to look like to following.

ColumnA	ColumnB	ColumnC	ColumnD	ColumnE
FruitOne	FruitTwo	FruitThree	Year	Value
Apples	Bananas	Grapes	2000	RandomValueOne
Apples	Bananas	Grapes	2001	RandomValueTwo
Apples	Bananas	Grapes	...2022	... RandomValueN

Thanks for any assistance in this. I dont have access to R, MatLab, SPSS or anything like that. Hoping to achieve this in excel.

Thanks.

CodePudding user response：

This is what you can do using Power Query:

To accomplish this task using Power Query please follow the steps,

• Select some cell in your Data Table,

• Data Tab => Get&Transform => From Table/Range,

• When the PQ Editor opens: Home => Advanced Editor,

• Make note of the 2 Tables Names,

• Paste the M Code below in place of what you see.

• And refer the notes

let

//Source Data Table1
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

//Changed The Data Type
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ColumnA", type text}, {"ColumnB", type text}, {"ColumnC", type text}, {"ColumnD", type any}, {"ColumnE", type any}, {"Column..BM", type text}}),

//Promoted Headers, this you can exclude. Since I copied from StackOverflow, the Columns A To BM were copied
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),

//Unpivoted Columns
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"FruitOne", "FruitTwo", "FruitThree"}, "Attribute", "Value"),

//Renamed the Attribute Column as Year
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Year"}})

in
    #"Renamed Columns"

• Importing it back into Excel press Home => Close => Close & Load To, when importing, you can either select Existing Sheet with the cell reference you want to place the table or you can simply click on NewSheet

Refer Query Settings Panel on right from where you can change the Table Name for the output, also the steps;

Final Output

EDIT

@MayukhBhattacharya Im running excel on a Mac, not a PC. Apparently Power Query hasn't been released to its fullest extent on Mac yet.

Using Formulas

• Formula used in cell A6

=INDEX($A$2:$C$3,INT((ROW(A1)-1)/COLUMNS($D$2:$F$3)) 1,COLUMN($A$2:$C$3))

And Fill Down.

• Formula used in cell D6

=INDEX($D$1:$F$1,MOD(ROW(A1)-1,3) 1)

And Fill Down.

• Formula used in cell E6

=INDEX($D$2:$F$3,MATCH(1,(A6=$A$2:$A$3)*(B6=$B$2:$B$3)*(C6=$C$2:$C$3),0),MATCH(D6,$D$1:$F$1,0))