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 

Hi Sergei

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

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 06, 2019

nabilmourad 

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

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Aug 06, 2019

      nabilmourad 

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

      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.

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Aug 09, 2019

        SergeiBaklan 

        Out of curiosity, why

        = (COLUMN(N1:Z1)-COLUMN($N$1))

        rather than

        = SEQUENCE( 1, 13, 0 )

        Do these columns have any  particular significance or is it a case of old habits dying hard?

         

        p.s. You seem to be able to work magic with this user interface where I struggle to produce anything but basic text!

Resources