extract unique values matching a text string

balaram51 

It was getting late!  All that was needed was a TRIM so that the Names row is now given by

= UNIQUE( TRIM(split), 1 )

SergeiBaklan 

Please accept may apologies, I came across this discussion and started answering without reading the prior posts with sufficient care.  I saw your first post used PQ and assumed the following posts were refinements of the original.  I now see that you followed up by exploring formula solutions and had already adopted the use of TEXTJOIN; I will examine your subsequent steps later.

balaram51 

I think I have just about achieved the other part of deriving a list of unique names!

I started by defining a named formula 'string' that runs all of the individual lists of names together

= "{""" & SUBSTITUTE( TEXTJOIN( ", ", 1, PersonsRequired ), ",", """,""" ) & """}"

and then wraps them in double quotes and braces to create a string version of an array constant. 

A further named formula 'split' uses the old Macro4 function

=EVALUATE(string)

to create an array of individual person names.

I am not quite home and dry because one of the 'Sam's has 4 characters whereas the other two have the correct value.  This messes up the identification of unique values

= UNIQUE( split, TRUE )

balaram51 

The approach is basically that outlined by PReagan  and Twifoo but I have set out to determine whether dynamic arrays have something to offer here.

The condition that each name is present in the 'persons required' list is

= ISNUMBER( SEARCH( Names, PersonsRequired ) )

This spills to gives a 6x8 Boolean array.

To filter out the unwanted dates for each person, I need to change this formula to apply column by column by using the intersection operator on the names row, giving a final form

= FILTER( date, ISNUMBER( SEARCH( @Names, PersonsRequired ) ) )

Each instance of the formula gives a single dynamically-sized column of dates.

balaram51 

Hello Sergei SergeiBaklan  and Hi Twifoo Twifoo 

By all means this is not a regular project and I am really excited to see this "ALL You Can Eat Excel Buffet"... You guys went above and beyond the MVP award. So I'll be nominating you for an imaginary award as follows:

Excel Nobel Prize 2019 goes to Sergei Baklan

Excel Oscar 2019 goes to Twifoo

I also came out with a VBA solution to add to the series. But, anyway that was too much fun.

Nabil Mourad

balaram51 

I fathomed that the repetition of ISNUMBER-FIND in my previous formula makes it patently less elegant. Thus, I was compelled to upgrade the formula in D2 of the attached file to this: 

=IFERROR(1/(1/(
LOOKUP(2,1/(FREQUENCY(0,1/(1+(
1/$A$2:$A$7*ISNUMBER(FIND(D$1,$B$2:$B$7))*(COUNTIF(D$1:D1,$A$2:$A$7)=0))))),
$A$2:$A$7)-$A$2*(ROW()>2)))+$A$2*(ROW()>2),"")

balaram51 

I copied your data to the attached file. The formula in D2, copied down rows and across columns, is: 

=IF(ROW()-1>SUMPRODUCT(--ISNUMBER(FIND(D$1,$B$2:$B$7))),"",
LOOKUP(2,1/(FREQUENCY(0,1/(1+(
1/$A$2:$A$7*ISNUMBER(FIND(D$1,$B$2:$B$7))*(COUNTIF(D$1:D1,$A$2:$A$7)=0))))),
$A$2:$A$7))

Note that the results for Ronny and Max in your screenshot are incorrect. 

balaram51 

If by Power Query some simple coding is required, not everything could be done from user interface. For such sample (I sorted names alphabetically)

the generated script is

let
    Source = Excel.CurrentWorkbook()
        {[Name="Table1"]}[Content],
    NamesToLists = Table.TransformColumns(
        Source,
        {{"Persons required", Splitter.SplitTextByDelimiter(",")}}
    ),
    ExpandNames = Table.ExpandListColumn(
        NamesToLists,
        "Persons required"
    ),
    TrimmNames = Table.TransformColumns(
        ExpandNames,
        {{"Persons required", Text.Trim, type text}}
    ),
    FormatAsDate = Table.TransformColumnTypes(
        TrimmNames,
        {{"Date", type date}}
    ),
    GroupNames = Table.Group(
        FormatAsDate,
        {"Persons required"},
        {{"Count", each _, type table [Date=date, Persons required=text]}}
    ),
    AddCustom = Table.AddColumn(
        GroupNames,
        "Custom", each [Count][Date]
    ),
    SortByNames = Table.Sort(
        AddCustom,
        {{"Persons required", Order.Ascending}}
    ),
    CreateTable = Table.FromColumns(
        SortByNames[Custom],
        SortByNames[Persons required]
    )
in
    CreateTable

You may check step by step in the attached file.

balaram51 

Hi

The problem with using the Search & Isnumber is, The extracted dates from the left column of the source data, will have gaps between the extracted values. So if there are three dates to extract for one onf the names, they won't be consecutive.

I think I can do it in Power Query but let's get a consultation on solving this situation with functions from SergeiBaklan 

Hello balaram51,

The format would be slightly different but this formula should get you close to what you are looking for:

In cell D2: =IF(ISNUMBER(SEARCH(D$1,$B2)),$A2,"")

and copy this over and down to K7.

**EDIT: Of course this is assuming that the data that you have given starts at cell A1.

Hope this helps!

PReagan