Search code examples
excelvbavlookuplookup

How to split a cell text by delimiter, do a lookup into given data set using the split cell text, and print in another sheet

Below is a small sample of the data set I am working with.

enter image description here

I figured out I can use TEXTSPLIT(D2,,",") to split a comma delimited cell and get the individual AHNs in a vertical format but I want to create a formula that I can apply to the entire data set and get something like below in a separate sheet.

enter image description here

The issue as you've probably figured out is for a cell with multiple comma separated values, the split cell spills down multiple rows. I could manually go through the list and copy and paste vertically into a new sheet but I am curious if there is a better way using excel formulas/macros.

Solution

  • Here is one way using Excel Formulas if using MS365 then the following should work, also it can be accomplished using Power Query

    enter image description here

    =LET(
     α, A2:B7,
     DROP(REDUCE(0,SEQUENCE(ROWS(α)),LAMBDA(φ,δ,
     VSTACK(φ,IF({1,0},INDEX(α,δ,0),TEXTSPLIT(INDEX(α,δ,2),,", "))))),1))

    However, using POWER QUERY will be the easiest and neat approach.

    enter image description here

    To perform the above task, follow the steps:

    • First convert the source range into a table and name it accordingly, for this example I have named it as Table1
    • 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="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Agreement AHN", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Agreement AHN")
in
    #"Split Column by Delimiter"
    • 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.