extract unique values matching a text string

Copper Contributor

I have two columns (A and B). Column A contains the dates and Column B contains the names of people who are required to attend the duty. Now based on this can i have names of people and just below their names, I want the all the dates that they are required to report to duty. Screen Shot 2019-08-04 at 1.05.39 AM.png

30 Replies

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

@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 @Sergei Baklan 

@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)

clipboard_image_0.png

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.

@Sergei Baklan 

Hi Sergei

Could it be achieved with a cocktail of functions without Power Query?

@nabilmourad 

Hi Nabil - didn't think in this direction, will check some later

@Sergei Baklan 

Eagerly waiting for it

@nabilmourad 

Okay, next iteration is with Dynamic Arrays and TEXTJOIN to select and sort names

clipboard_image_0.png

In N3 for that

=SORT(
  UNIQUE(
    TRIM(
      MID(
        SUBSTITUTE(
          TEXTJOIN(",",TRUE,Table1[[Persons required]:[Persons required]]),
          ",",
          REPT(" ",LEN(
            TEXTJOIN(",",TRUE,Table1[[Persons required]:[Persons required]])
          ))
        ),
        (COLUMN(N1:Z1)-COLUMN($N$1))*
        LEN(TEXTJOIN(",",TRUE,Table1[[Persons required]:[Persons required]]))+1,
        LEN(TEXTJOIN(",",TRUE,Table1[[Persons required]:[Persons required]]))
      )
   ),TRUE),,1,TRUE)

and pull dates in more traditional way

=IFERROR(
   INDEX(Table1[Date],
      AGGREGATE(15,6,
         1/ISNUMBER(SEARCH(N$3,Table1[Persons required]))*(ROW(Table1[Persons required])-ROW(Table1[[#Headers], [Persons required]])),ROW()-ROW(N$3))
   ),
"")

Without dynamic arrays that's most probably with helper column/row to extract all names and after that select unique from them. Or VBA.

@Sergei Baklan 

OMG

You are the Excel Super Star !!
I don't have Office 365 (with insider) at work so will have to wait until I go back home and check your beautiful formulas meticulously...

That's too much fun

Thank you Sergei

If you come up with a solution using regular functions let me know

meantime I will take care of the VBA option which will be (for the first time ever) easier than other options.

Have a great day

Nabil

It appears to me that your desired results can be returned through a formula! Please attach your sample Excel file to facilitate testing.

@nabilmourad 

Nabil, at work I'm on Monthly (Targeted) channel, it's with DA about a month or so. Hope will be soon on Monthly channel as well.

 

If use the same pattern from MrExcel to split separated text on columns/rows, first in mind is to generate row with all names from the table

clipboard_image_0.png

by

=TRIM(
      MID(
        SUBSTITUTE(
          TEXTJOIN(",",TRUE,Table1[[Persons required]:[Persons required]]),
          ",",
          REPT(" ",LEN(
            TEXTJOIN(",",TRUE,Table1[[Persons required]:[Persons required]])
          ))
        ),
        (COLUMN()-COLUMN($W$1))*
        LEN(TEXTJOIN(",",TRUE,Table1[[Persons required]:[Persons required]]))+1,
        LEN(TEXTJOIN(",",TRUE,Table1[[Persons required]:[Persons required]]))
      ))

when extract from here unique names only

=IFERROR(INDEX($W$2:$AJ$2,AGGREGATE(15,6,1/(COUNTIF($V$3:V$3,$W$2:$AJ$2)=0)*(COLUMN($W$2:$AJ$2)-COLUMN($V$1)),1)),"")

and add dates as in previous sample

@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 

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 

Hello Sergei @Sergei Baklan  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

Hello @nabilmourad

@balaram51 sought, and you eagerly waited, for a formula solution so I gave one. My later formula is inevitably an upgrade of the previous one. 

Cheers, 

Twifoo

 

@nabilmourad 

Most challenging part here is to extract list of names for the headers of the resulting table. If names are predefined any variant of regular formula is more preferable.

@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 

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 )

@Sergei Baklan 

Hi

I am missing the second step in the applied steps (NameToList) I see the M code but how did you do it from the interface of the Query Editor?

@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 )

 

@Sergei Baklan 

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.