How to transpose a specific way

crouille

New Member
Joined
Feb 23, 2022
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
HI,

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

ABCD
123​
abc-10abc-azabc-20-def
456​
ghi-20ghi-cdghi-30-jkl
789​
mno-30mno-efmno-30-pqr


i need to then have the data look tike this for the load file:
ABC
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!
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,554
Office Version
  1. 365
Platform
  1. Windows
Could use power query.

Book1.xlsb
ABCDEFGH
1ABCDAValueIndex
2123abc-10abc-azabc-20-def123abc-101
3456ghi-20ghi-cdghi-30-jkl123abc-az2
4789mno-30mno-efmno-30-pqr123abc-20-def3
5456ghi-201
6456ghi-cd2
7456ghi-30-jkl3
8789mno-301
9789mno-ef2
10789mno-30-pqr3
Sheet4


Power Query:
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

Well-known Member
Joined
Jul 14, 2008
Messages
3,554
Office Version
  1. 365
Platform
  1. Windows
Or you could do it with VBA.

Book1.xlsb
ABCDEFGHI
1ABCD
2123abc-10abc-azabc-20-def123abc-101
3456ghi-20ghi-cdghi-30-jkl123abc-az2
4789mno-30mno-efmno-30-pqr123abc-20-def3
5456ghi-201
6456ghi-cd2
7456ghi-30-jkl3
8789mno-301
9789mno-ef2
10789mno-30-pqr3
Sheet4


VBA Code:
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

New Member
Joined
Feb 23, 2022
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Jul 14, 2008
Messages
3,554
Office Version
  1. 365
Platform
  1. Windows
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.

Cell Formulas
RangeFormula
H2:H10H2=INDEX($A$2:$A$4,(ROW()-2)/ROWS($A$2:$A$4)+1)
I2:I10I2=INDEX($B$2:$D$4,(ROW()-2)/ROWS($A$2:$A$4)+1,MOD(ROW()-2,ROWS($A$2:$A$4))+1)
J2:J10J2=COUNTIF($H$2:H2,H2)
 

crouille

New Member
Joined
Feb 23, 2022
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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.

Cell Formulas
RangeFormula
H2:H10H2=INDEX($A$2:$A$4,(ROW()-2)/ROWS($A$2:$A$4)+1)
I2:I10I2=INDEX($B$2:$D$4,(ROW()-2)/ROWS($A$2:$A$4)+1,MOD(ROW()-2,ROWS($A$2:$A$4))+1)
J2:J10J2=COUNTIF($H$2:H2,H2)
thank you so much, those formulas worked. appreciate the help!
 

Forum statistics

Threads
1,175,531
Messages
5,897,955
Members
434,688
Latest member
vi28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top