Forum Discussion

balaram51's avatar
balaram51
Copper Contributor
Aug 03, 2019

extract unique values matching a text string

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 th...
excel
Formulas and Functions
nabilmourad's avatar
nabilmourad
MVP
Aug 06, 2019

SergeiBaklan 

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

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 06, 2019

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

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

Resources