Stacking data from first sheet to second sheet

[royc19]

Does anyone know how i can have the data that is listed on the the first page shown on the second page stacked onto of each other. The data that is entered can vary in size so the formular would need a way of working this out. Could this be done with a formular? Thanks

[Pepe Le Mokko]

Hi
I think you'll have to adapt your thread title as per forum rules, like " Stack data from first sheet to second sheet"?

[royc19]

Hi
I think you'll have to adapt your thread title as per forum rules, like " Stack data from first sheet to second sheet"?

Sorry new here....done

[JeteMc]

I suggest converting the range on the Paste into Format sheet into a table to allow for variation of data size.
The formula for A2 and down is: =IFERROR(IF('Paste into Format'!K2<>"",'Paste into Format'!K2,INDEX(Table1[10 SERIES PART '#],ROW()-COUNTA(Table1[20 SERIES PART '#]))),"")
The formula for C2:G2 and down is:

Please Login or Register  to view this content.

The formula for J2 and down is: =IF(A1="","",CONCATENATE(C1," ", D1," ", G1))
The formula for K2 and down is: =IF(A1="","",IFERROR(INDEX(Table1[20 SERIES PRICE],MATCH(A1,Table1[20 SERIES PART '#],0)),INDEX(Table1[10 SERIES PRICE],MATCH(A1,Table1[10 SERIES PART '#],0))))
Let us know if you have any questions.

[bluesky63]

Hi Royc19

it is a transformation to the format your desire, using power query will be easy since. any subsequent update to your source
just need to right click the output and refresh

I eliminate all your blank columns and just take the following fields

Part # MAKE MODEL ENGINE DESCRIPTION COMBINED STD PRICE


bring the table to Power Query Editor and use the User Interface tools and some simple M Code to transform to what you want

let
Source = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Part #", Int64.Type}, {"MAKE", type text}, {"MODEL", type text}, {"ENGINE", type any}, {"DESCRIPTION", type text}, {"COMBINED", type text}, {"STD PRICE", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "10 SERIES PRICE", each [STD PRICE]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "20 SERIES PRICE", each [STD PRICE]*1.1),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Custom1", {"Part #", "MAKE", "MODEL", "ENGINE", "DESCRIPTION", "COMBINED", "STD PRICE"}, "Attribute", "Value"),
#"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Part #", type text}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type1", "Custom", each [#"Part #"]&"-"&Text.Start([Attribute],2)),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom2",{"Custom", "Part #", "MAKE", "MODEL", "ENGINE", "DESCRIPTION", "COMBINED", "STD PRICE", "Attribute", "Value"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Part #"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Part Type"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"STD PRICE"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns1",{{"Attribute", Order.Descending}, {"Value", Order.Descending}}),
#"Removed Columns2" = Table.RemoveColumns(#"Sorted Rows",{"Attribute"})
in
#"Removed Columns2"

[royc19]

Thanks for the help

[JeteMc]

Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.