Need help with moving data from col to col and rows

Need col a from this worksheet put in a second worksheet with 1 row between each
i need each item type in column b of this worksheet place in the second worksheet in a row next to the item name
i need each item id from col c of this worksheet placed in the second worksheet in a row right below the item type it corresponds to

In cell A2 of Sheet2, try this array formula and copy down:

Formula:

=IFERROR(IF(ISEVEN(ROW()),INDEX(Sheet1!$A$2:$A$100,MATCH(1,(COUNTIF(A$1:A1,Sheet1!$A$2:$A$100)=0)*(Sheet1!$A$2:$A$100<>""),0))," "),"")

And this other array formula in cell B2 and copy across and down:

Formula:

=IFERROR(INDEX(INDEX(Sheet1!$B$2:$C$100,AGGREGATE(15,6,(ROW($A$2:$A$100)-ROWS($A$1:$A$2)+1)/(Sheet1!$A$2:$A$100<>""),COUNTIF($A$2:$A2,"<> ")),MOD(ROWS($A$1:$A1)-1,2)+1):INDEX(Sheet1!$B$2:$C$100,IFERROR(AGGREGATE(15,6,(ROW($A$2:$A$100)-ROWS($A$1:$A$2)+1)/(Sheet1!$A$2:$A$100<>""),COUNTIF($A$2:$A2,"<> ")+1),MAX(IF(Sheet1!$B$2:$B$100<>"",ROW(Sheet1!$B$2:$B$100))))-1,MOD(ROWS($A$1:$A1)-1,2)+1),COLUMNS($A$1:A$1)),"")

Array formulas must be confirmed by pressing Ctrl, Shift and Enter keys simultaneously. Then drag down and across.

When you do the array-entering right, you'll see curly brackets { } wrapping the formula in the formula bar.

I hope this helps.

Good luck!

I'll try this. Thank you. I'll let you know if it works.

Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ITEM NAME", type text}, {"ITEM TYPE", type text}, {"ITEM ID", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"ITEM ID"}),
    #"Filled Down" = Table.FillDown(#"Removed Columns",{"ITEM NAME"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"ITEM NAME"}, {{"Count", each _, type table [ITEM NAME=text, ITEM TYPE=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"ITEM NAME", "ITEM TYPE", "Index"}, {"Custom.ITEM NAME", "Custom.ITEM TYPE", "Custom.Index"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Count", "Custom.ITEM NAME"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns1", {{"Custom.Index", type text}}, "lt-LT"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns1", {{"Custom.Index", type text}}, "lt-LT")[Custom.Index]), "Custom.Index", "Custom.ITEM TYPE")
in
    #"Pivoted Column"