Transpose Data

[Ruban S]

Dear All,

Please help me.

I have data like this

SHORT	LONG	NOUN	MODIFIER	ATT N1	ATT V1
BELT,A56	BELT,DESIGNATION:A56	BELT	--	DESIGNATION	A56
BELT,A57	BELT,DESIGNATION:A57	BELT	--	DESIGNATION	A57

And I want it like this

BELT,A56	BELT,DESIGNATION:A56
BELT	--
DESIGNATION	A56
BELT,A57	BELT,DESIGNATION:A57
BELT	--
DESIGNATION	A57

 

[sandy666]

with Power Query (Get&Transform)

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index1 = Table.AddIndexColumn(Table.SelectColumns(Table.Unpivot(Source, {"SHORT", "NOUN", "ATT N1"}, "Attribute", "Value"),{"Value"}), "Index", 1, 1),
    Index2 = Table.AddIndexColumn(Table.SelectColumns(Table.Unpivot(Source, {"LONG", "MODIFIER", "ATT V1"}, "Attribute", "Value"),{"Value"}), "Index", 1, 1),
    Result = Table.RemoveColumns(Table.ExpandTableColumn(Table.NestedJoin(Index1,{"Index"},Index2,{"Index"},"Index2",JoinKind.LeftOuter), "Index2", {"Value"}, {"Value.1"}),{"Index"})
in
    Result

 

[Ruban S]

Dear Power Query is restricted here. Can you please suggest any VBA Program

 

[Fluff]

How about

Sub RubanS()
    Dim Oary As Variant, Nary As Variant
    Dim r As Long, c As Long, nr As Long
    
    Oary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
    ReDim Nary(1 To UBound(Oary) * UBound(Oary, 2) / 2, 1 To 2)
    For r = 2 To UBound(Oary)
        For c = 1 To UBound(Oary, 2) Step 2
            nr = nr + 1
            Nary(nr, 1) = Oary(r, c)
            Nary(nr, 2) = Oary(r, c + 1)
        Next c
    Next r
    Sheets("Sheet2").Range("A1").Resize(nr, 2).Value = Nary
End Sub

change sheet names to suit

 

[sandy666]

Dear Power Query is restricted here. Can you please suggest any VBA Program

I don't believe! Really?
You've Excel 2016 so you've Power Query (Get&Transform) built-in, so it cannot be prohibited.

 

[sandy666]

 

[Ruban S]

How about

Sub RubanS()
    Dim Oary As Variant, Nary As Variant
    Dim r As Long, c As Long, nr As Long
   
    Oary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
    ReDim Nary(1 To UBound(Oary) * UBound(Oary, 2) / 2, 1 To 2)
    For r = 2 To UBound(Oary)
        For c = 1 To UBound(Oary, 2) Step 2
            nr = nr + 1
            Nary(nr, 1) = Oary(r, c)
            Nary(nr, 2) = Oary(r, c + 1)
        Next c
    Next r
    Sheets("Sheet2").Range("A1").Resize(nr, 2).Value = Nary
End Sub

change sheet names to suit

It is coming like this

 

[Ruban S]

View attachment 2973

I don't know how to proceed with is.

 

[sandy666]

I don't know how to proceed with is.

see example

more info

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in
• General: Excel Help

 

[Fluff]

You need to change the sheet names to match your sheet names