Search code examples
excelexcel-formulaexcel-2010

Create a dynamic table based on occurrences in another table

I have a problem i cannot solve.

I'm trying to create a table that creates multiple lines per order based on the number of shipments. so for example the data i download has 2 columns, order no. and shipments.

if the shipments is more than 1 then i need a sperate line for each shipment in my new table.

I will attach a picture of what I'm trying to achieve. it has stumped me!

I've search hi and low but can't find an answer, perhaps its my clumsy wording when searching? :)

ideally i want to achieve this using formulas so data can just be copy and pasted in, thought i guess i can use vba if i must.

enter image description here

Solution

  • Assuming there is no Excel Constraints as per the tags posted, then one could use the following formula and for the given sample this should clearly work:

    enter image description here

    =LET(α, SEQUENCE(,MAX(B3:B6)), MID(TOCOL(IF(α>B3:B6,0/0,A3:A6&" "&α),3),{1,3},{1,1}))

    Or,

    enter image description here

    =LET(
     α, SEQUENCE(,MAX(B3:B6)),
     δ, TOCOL(IF(α>B3:B6,0/0,A3:A6&" "&α),3),
     HSTACK(TEXTBEFORE(δ," "),--TEXTAFTER(δ," ")))

    This can also be accomplished using Power Query, available in Windows Excel 2010+ and Excel 365 (Windows or Mac) and what I see it will be easier & simpler to achieve using the said tool.

    enter image description here

    To use Power Query follow the steps: (Read the steps annotations to follow)

    • First convert the source ranges into a table and name it accordingly, for this example I have named it as Table_ONE
    • Next, open a blank query from Data Tab --> Get & Transform Data --> Get Data --> From Other Sources --> Blank Query
    • The above lets the Power Query window opens, now from Home Tab --> Advanced Editor --> And paste the following M-Code by removing whatever you see, and press Done
    let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Ship-ments", each {1..[shipments]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Ship-ments"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"shipments"})
in
    #"Removed Columns"

    enter image description here

    • Lastly, to import it back to Excel --> Click on Close & Load or Close & Load To --> The first one which clicked shall create a New Sheet with the required output while the latter will prompt a window asking you where to place the result.

    Edit Per tags it shows Excel 2010 then can use POWER QUERY or using helper columns with VLOOKUP() & COUNTIF()

    enter image description here

    • In A3 enter 1
    • In A4 enter =A3+C3 and copy down till the one extra last row of your data
    • Next use VLOOKUP() in cell E3 & COUNTIF() in cell F3
    =VLOOKUP(ROW(A1),$A$3:$B$7,2,1)

    =COUNTIF(E$3:E3,E3)

    and drag down as far as it needs to go.