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
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 06, 2019

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.

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.

Resources