Search code examples
excelfunctionexcel-formulamergemultiple-columns

Merge columns and generate new column based on the number in original column using EXCEL

I got an issue when trying to merge columns in Excel which should be common in real life but superisingly not found on internet(May be that's just because I didn't find it). The problem is here below:

Assume you have a table in sheet which have two columns, column A is the name for some items such as parks like Disney, Water Park, Universal Studio etc. and column B is the number that assigned to each item representing the serial number of this item. A sample table is like:

Column A Column B
Disney 2
Water Park 4

What i intend to do is create a new column, says Column C, where Disney 1 and Disney 2 are listed. And the new table is like:

Column C
Disney 1
Disney 2
Water Park 1
Water Park 2
Water Park 3
Water Park 4

Does anyone know how to realize this conversion using Excel function?

Thank you very much!

Solution

  • Assuming there is no Excel Constraints as per the tags posted then the following formula should work which uses a LAMBDA() helper function called MAKEARRAY()

    enter image description here

    =LET(
     α, A1:A2,
     φ, B1:B2,
     TOCOL(MAKEARRAY(ROWS(α),MAX(φ),LAMBDA(δ,ε,
     IF(ε<=INDEX(φ,δ),INDEX(α,δ)&" "&ε,p))),3))

    Here is another alternative method without using any LAMBDA() helper functions:

    enter image description here

    =LET(α, SEQUENCE(,MAX(B1:B2)), TOCOL(IF(α>B1:B2,0/0,A1:A2&" "&α),3))

    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="Table_ONE"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each {1..[Column2]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom", type text}}, "en-US"),{"Column1", "Custom"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Output"),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Column2"})
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.