Table transformation

Elad_Magazanik 

i hope it help you in create complete solution

Hi Elad_Magazanik 

With Get & Transform aka Power Query in attached file. Done with the UI/Menus only

Alternative:

let
    Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
    RemovedBlankLocation = Table.SelectRows(Source, each
        not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))
    ),
    FilledDownItem = Table.FillDown(RemovedBlankLocation, {"Item Number"}),
    TranposedLocations = Table.Group(FilledDownItem, {"Item Number"},
        {
            {"LOCATIONS", each Table.Transpose(Table.SelectColumns(_, "Location")), type table},
            {"LOC_COUNT", Table.RowCount, Int64.Type}
        }
    ),
    ColumnsToExpand = List.Transform({1..List.Max(TranposedLocations[LOC_COUNT])},
        each "Column" & Text.From(_)
    ),
    RemovedLocCount = Table.RemoveColumns(TranposedLocations, {"LOC_COUNT"}),
    ExpandedLocations = Table.ExpandTableColumn(RemovedLocCount, "LOCATIONS",
        ColumnsToExpand, List.ReplaceValue(ColumnsToExpand, "Column", "Location ", Replacer.ReplaceText)
    )
in
    ExpandedLocations

Elad_Magazanik 

This uses basic dynamic array formulas to generate a table of indices

= LET(
    n,     ROWS(Table),
    k,     SEQUENCE(n),
    k₀,    FILTER(k, Table[Item Number]<>""),
    k₁,    VSTACK(DROP(k₀, 1) - 1, n),
    idx,   k₀ + {0,1,2,3,4},
    items, INDEX(Table[Item Number], k₀),
    pivot, IF(idx<=k₁, INDEX(Table[Location], idx), ""),
    HSTACK(items, pivot)
  )

Elad_Magazanik 

=LET(filldown,

SCAN("",A2:A15,LAMBDA(ini,arr,IF(ISTEXT(arr),arr,ini))),

HSTACK(

UNIQUE(filldown),

IFNA(DROP(REDUCE("",SEQUENCE(ROWS(UNIQUE(filldown))),LAMBDA(x,y,VSTACK(x,TOROW(FILTER(B2:B15,filldown=INDEX(UNIQUE(filldown),y)))))),1),"")

)

)

With Office 365 or Excel for the web you can apply this formula.