How to transpose a specific way

[crouille]

HI,

i need to take data from a client excel file and make it into a load excel file for work

A	B	C	D
123​	abc-10	abc-az	abc-20-def
456​	ghi-20	ghi-cd	ghi-30-jkl
789​	mno-30	mno-ef	mno-30-pqr

i need to then have the data look tike this for the load file:

A	B	C
123​	abc-10	1​
123​	abc-az	2​
123​	abc-20-def	3​
456​	ghi-20	1​
456​	ghi-cd	2​
456​	ghi-30-jkl	3​
789​	mno-30	1​
789​	mno-ef	2​
789​	mno-30-pqr	3​

then repeated like 100 times

Thanks!

 

[lrobbo314]

Could use power query.

Book1.xlsb
	A	B	C	D	E	F	G	H
1	A	B	C	D		A	Value	Index
2	123	abc-10	abc-az	abc-20-def		123	abc-10	1
3	456	ghi-20	ghi-cd	ghi-30-jkl		123	abc-az	2
4	789	mno-30	mno-ef	mno-30-pqr		123	abc-20-def	3
5						456	ghi-20	1
6						456	ghi-cd	2
7						456	ghi-30-jkl	3
8						789	mno-30	1
9						789	mno-ef	2
10						789	mno-30-pqr	3
Sheet4

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"A"}, "Attribute", "Value"),
    Group = Table.Group(Unpivot, {"A"}, {{"Count", each _, type table [A=text, Attribute=text, Value=text]}}),
    Index = Table.TransformColumns(Group,{{"Count", each Table.AddIndexColumn(_,"Index",1)}}),
    Expand = Table.ExpandTableColumn(Index, "Count", {"Value", "Index"}, {"Value", "Index"})
in
    Expand

 

[lrobbo314]

Or you could do it with VBA.

Book1.xlsb
	A	B	C	D	E	F	G	H	I
1	A	B	C	D
2	123	abc-10	abc-az	abc-20-def			123	abc-10	1
3	456	ghi-20	ghi-cd	ghi-30-jkl			123	abc-az	2
4	789	mno-30	mno-ef	mno-30-pqr			123	abc-20-def	3
5							456	ghi-20	1
6							456	ghi-cd	2
7							456	ghi-30-jkl	3
8							789	mno-30	1
9							789	mno-ef	2
10							789	mno-30-pqr	3
Sheet4

Sub UNP()
Dim r As Range:     Set r = Range("A2:D" & Range("A" & Rows.Count).End(xlUp).Row)
Dim ar() As Variant: ar = r.Value2

With CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(ar)
        For j = 2 To UBound(ar, 2)
            .Add Join(Array(ar(i, 1), ar(i, j), j - 1), ";"), 1
        Next j
    Next i
    
    Set r = Range("G2").Resize(.Count)
    r.Value2 = Application.Transpose(.keys)
    r.TextToColumns DataType:=xlDelimited, Semicolon:=True
End With
End Sub

 

[crouille]

hi,

im not good with vba. how do i use the power query? or i guess i could google that?

is there a formula you can write or is that a pain?

i appreciate the quick response!

 

[lrobbo314]

Power query is an add-in on the 'Get and Transform' section of the data tab.
Here is a link to some info in Power Query. Create, load, or edit a query in Excel (Power Query)

Here is a set of formulas that should do what you want as well.

Book1.xlsb
	H	I	J
2	123	abc-10	1
3	123	abc-az	2
4	123	abc-20-def	3
5	456	ghi-20	1
6	456	ghi-cd	2
7	456	ghi-30-jkl	3
8	789	mno-30	1
9	789	mno-ef	2
10	789	mno-30-pqr	3
Sheet4

Cell Formulas
Range	Formula
H2:H10	H2	=INDEX($A$2:$A$4,(ROW()-2)/ROWS($A$2:$A$4)+1)
I2:I10	I2	=INDEX($B$2:$D$4,(ROW()-2)/ROWS($A$2:$A$4)+1,MOD(ROW()-2,ROWS($A$2:$A$4))+1)
J2:J10	J2	=COUNTIF($H$2:H2,H2)

 

[crouille]

Power query is an add-in on the 'Get and Transform' section of the data tab.
Here is a link to some info in Power Query. Create, load, or edit a query in Excel (Power Query)

Here is a set of formulas that should do what you want as well.

Book1.xlsb
	H	I	J
2	123	abc-10	1
3	123	abc-az	2
4	123	abc-20-def	3
5	456	ghi-20	1
6	456	ghi-cd	2
7	456	ghi-30-jkl	3
8	789	mno-30	1
9	789	mno-ef	2
10	789	mno-30-pqr	3
Sheet4

Cell Formulas
Range	Formula
H2:H10	H2	=INDEX($A$2:$A$4,(ROW()-2)/ROWS($A$2:$A$4)+1)
I2:I10	I2	=INDEX($B$2:$D$4,(ROW()-2)/ROWS($A$2:$A$4)+1,MOD(ROW()-2,ROWS($A$2:$A$4))+1)
J2:J10	J2	=COUNTIF($H$2:H2,H2)

thank you so much, those formulas worked. appreciate the help!