Sub Maybe()
Dim i As Long, sh2 As Worksheet, lc As Long
Set sh2 = Worksheets("Sheet2")
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(i, Columns.Count).End(xlToLeft).Column
sh2.Cells(Rows.Count, 2).End(xlUp).Offset(1, -1).Resize(, 2).Value = Cells(i, 1).Resize(, 2).Value
If lc > 2 Then sh2.Cells(Rows.Count, 2).End(xlUp).Offset(1).Resize(lc - 2).Value = Application.Transpose(Cells(i, 3).Resize(, lc - 2).Value)
Next i
End Sub
=LET(
_a,A1:D3,
_b,TOCOL(EXPAND(TAKE(_a,,1),,COLUMNS(_a)-1,"")),
_c,TOCOL(DROP(_a,,1)&""),
FILTER(HSTACK(_b,_c),_c<>""))
Sub Maybe()
Dim i As Long, sh2 As Worksheet
Set sh2 = Worksheets("Sheet2")
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
With sh2.Cells(Rows.Count, 2).End(xlUp).Offset(1)
.Offset(, -1).Value = Cells(i, 1).Value
.Resize(Cells(i, Columns.Count).End(xlToLeft).Column - 1).Value = _
Application.Transpose(Cells(i, 1).Offset(, 1).Resize(, Cells(i, Columns.Count).End(xlToLeft).Column - 1).Value)
End With
Next i
End Sub
23 08 26.xlsm | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Name1 | arun | rob | sarah | ||
2 | Name2 | danny | ||||
3 | Name3 | nick | david | |||
4 | ||||||
5 | ||||||
6 | Name1 | arun | ||||
7 | rob | |||||
8 | sarah | |||||
9 | Name2 | danny | ||||
10 | Name3 | nick | ||||
11 | david | |||||
12 | ||||||
Arunchira |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A6:B11 | A6 | =HSTACK(TOCOL(IF(COLUMN(B1:D3)>COLUMN(B1),IF(B1:D3="",x,""),A1:A3),2),TOCOL(B1:D3,1)) |
Dynamic array formulas. |
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Column1"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in
#"Removed Columns"