extract unique values matching a text string

%3CLINGO-SUB%20id%3D%22lingo-sub-786892%22%20slang%3D%22en-US%22%3Eextract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786892%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20columns%20(A%20and%20B).%20Column%20A%20contains%20the%20dates%20and%20Column%20B%20contains%20the%20names%20of%20people%20who%20are%20required%20to%20attend%20the%20duty.%20Now%20based%20on%20this%20can%20i%20have%20names%20of%20people%20and%20just%20below%20their%20names%2C%20I%20want%20the%20all%20the%20dates%20that%20they%20are%20required%20to%20report%20to%20duty.%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20700px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125512iD7396F93684E7963%2Fimage-dimensions%2F700x171%3Fv%3D1.0%22%20width%3D%22700%22%20height%3D%22171%22%20alt%3D%22Screen%20Shot%202019-08-04%20at%201.05.39%20AM.png%22%20title%3D%22Screen%20Shot%202019-08-04%20at%201.05.39%20AM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-786892%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-786923%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786923%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F386833%22%20target%3D%22_blank%22%3E%40balaram51%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20format%20would%20be%20slightly%20different%20but%20this%20formula%20should%20get%20you%20close%20to%20what%20you%20are%20looking%20for%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20cell%20D2%3A%26nbsp%3B%3DIF(ISNUMBER(SEARCH(D%241%2C%24B2))%2C%24A2%2C%22%22)%3C%2FP%3E%3CP%3Eand%20copy%20this%20over%20and%20down%20to%20K7.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E**EDIT%3A%20Of%20course%20this%20is%20assuming%20that%20the%20data%20that%20you%20have%20given%20starts%20at%20cell%20A1.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps!%3C%2FP%3E%3CP%3EPReagan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-789314%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-789314%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F386833%22%20target%3D%22_blank%22%3E%40balaram51%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EThe%20problem%20with%20using%20the%20Search%20%26amp%3B%20Isnumber%20is%2C%20The%20extracted%20dates%20from%20the%20left%20column%20of%20the%20source%20data%2C%20will%20have%20gaps%20between%20the%20extracted%20values.%20So%20if%20there%20are%20three%20dates%20to%20extract%20for%20one%20onf%20the%20names%2C%20they%20won't%20be%20consecutive.%3C%2FP%3E%3CP%3EI%20think%20I%20can%20do%20it%20in%20Power%20Query%20but%20let's%20get%20a%20consultation%20on%20solving%20this%20situation%20with%20functions%20from%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-790063%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790063%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F386833%22%20target%3D%22_blank%22%3E%40balaram51%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20by%20Power%20Query%20some%20simple%20coding%20is%20required%2C%20not%20everything%20could%20be%20done%20from%20user%20interface.%20For%20such%20sample%20(I%20sorted%20names%20alphabetically)%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125750i9D6C2408D1F5222D%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ethe%20generated%20script%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%0A%20%20%20%20%20%20%20%20%7B%5BName%3D%22Table1%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20NamesToLists%20%3D%20Table.TransformColumns(%0A%20%20%20%20%20%20%20%20Source%2C%0A%20%20%20%20%20%20%20%20%7B%7B%22Persons%20required%22%2C%20Splitter.SplitTextByDelimiter(%22%2C%22)%7D%7D%0A%20%20%20%20)%2C%0A%20%20%20%20ExpandNames%20%3D%20Table.ExpandListColumn(%0A%20%20%20%20%20%20%20%20NamesToLists%2C%0A%20%20%20%20%20%20%20%20%22Persons%20required%22%0A%20%20%20%20)%2C%0A%20%20%20%20TrimmNames%20%3D%20Table.TransformColumns(%0A%20%20%20%20%20%20%20%20ExpandNames%2C%0A%20%20%20%20%20%20%20%20%7B%7B%22Persons%20required%22%2C%20Text.Trim%2C%20type%20text%7D%7D%0A%20%20%20%20)%2C%0A%20%20%20%20FormatAsDate%20%3D%20Table.TransformColumnTypes(%0A%20%20%20%20%20%20%20%20TrimmNames%2C%0A%20%20%20%20%20%20%20%20%7B%7B%22Date%22%2C%20type%20date%7D%7D%0A%20%20%20%20)%2C%0A%20%20%20%20GroupNames%20%3D%20Table.Group(%0A%20%20%20%20%20%20%20%20FormatAsDate%2C%0A%20%20%20%20%20%20%20%20%7B%22Persons%20required%22%7D%2C%0A%20%20%20%20%20%20%20%20%7B%7B%22Count%22%2C%20each%20_%2C%20type%20table%20%5BDate%3Ddate%2C%20Persons%20required%3Dtext%5D%7D%7D%0A%20%20%20%20)%2C%0A%20%20%20%20AddCustom%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20GroupNames%2C%0A%20%20%20%20%20%20%20%20%22Custom%22%2C%20each%20%5BCount%5D%5BDate%5D%0A%20%20%20%20)%2C%0A%20%20%20%20SortByNames%20%3D%20Table.Sort(%0A%20%20%20%20%20%20%20%20AddCustom%2C%0A%20%20%20%20%20%20%20%20%7B%7B%22Persons%20required%22%2C%20Order.Ascending%7D%7D%0A%20%20%20%20)%2C%0A%20%20%20%20CreateTable%20%3D%20Table.FromColumns(%0A%20%20%20%20%20%20%20%20SortByNames%5BCustom%5D%2C%0A%20%20%20%20%20%20%20%20SortByNames%5BPersons%20required%5D%0A%20%20%20%20)%0Ain%0A%20%20%20%20CreateTable%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EYou%20may%20check%20step%20by%20step%20in%20the%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-790081%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790081%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Sergei%3C%2FP%3E%3CP%3ECould%20it%20be%20achieved%20with%20a%20cocktail%20of%20functions%20without%20Power%20Query%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-790082%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790082%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Nabil%20-%20didn't%20think%20in%20this%20direction%2C%20will%20check%20some%20later%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-790087%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790087%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEagerly%20waiting%20for%20it%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-790359%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790359%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOkay%2C%20next%20iteration%20is%20with%20Dynamic%20Arrays%20and%20TEXTJOIN%20to%20select%20and%20sort%20names%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125757i9D6AD6D25197330B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIn%20N3%20for%20that%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3E%3DSORT(%0A%20%20UNIQUE(%0A%20%20%20%20TRIM(%0A%20%20%20%20%20%20MID(%0A%20%20%20%20%20%20%20%20SUBSTITUTE(%0A%20%20%20%20%20%20%20%20%20%20TEXTJOIN(%22%2C%22%2CTRUE%2CTable1%5B%5BPersons%20required%5D%3A%5BPersons%20required%5D%5D)%2C%0A%20%20%20%20%20%20%20%20%20%20%22%2C%22%2C%0A%20%20%20%20%20%20%20%20%20%20REPT(%22%20%22%2CLEN(%0A%20%20%20%20%20%20%20%20%20%20%20%20TEXTJOIN(%22%2C%22%2CTRUE%2CTable1%5B%5BPersons%20required%5D%3A%5BPersons%20required%5D%5D)%0A%20%20%20%20%20%20%20%20%20%20))%0A%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20(COLUMN(N1%3AZ1)-COLUMN(%24N%241))*%0A%20%20%20%20%20%20%20%20LEN(TEXTJOIN(%22%2C%22%2CTRUE%2CTable1%5B%5BPersons%20required%5D%3A%5BPersons%20required%5D%5D))%2B1%2C%0A%20%20%20%20%20%20%20%20LEN(TEXTJOIN(%22%2C%22%2CTRUE%2CTable1%5B%5BPersons%20required%5D%3A%5BPersons%20required%5D%5D))%0A%20%20%20%20%20%20)%0A%20%20%20)%2CTRUE)%2C%2C1%2CTRUE)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20pull%20dates%20in%20more%20traditional%20way%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3E%3DIFERROR(%0A%20%20%20INDEX(Table1%5BDate%5D%2C%0A%20%20%20%20%20%20AGGREGATE(15%2C6%2C%0A%20%20%20%20%20%20%20%20%201%2FISNUMBER(SEARCH(N%243%2CTable1%5BPersons%20required%5D))*(ROW(Table1%5BPersons%20required%5D)-ROW(Table1%5B%5B%23Headers%5D%2C%20%5BPersons%20required%5D%5D))%2CROW()-ROW(N%243))%0A%20%20%20)%2C%0A%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EWithout%20dynamic%20arrays%20that's%20most%20probably%20with%20helper%20column%2Frow%20to%20extract%20all%20names%20and%20after%20that%20select%20unique%20from%20them.%20Or%20VBA.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-790378%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790378%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOMG%3C%2FP%3E%3CP%3EYou%20are%20the%20Excel%20Super%20Star%20!!%3CBR%20%2F%3EI%20don't%20have%20Office%20365%20(with%20insider)%20at%20work%20so%20will%20have%20to%20wait%20until%20I%20go%20back%20home%20and%20check%20your%20beautiful%20formulas%20meticulously...%3C%2FP%3E%3CP%3EThat's%20too%20much%20fun%3C%2FP%3E%3CP%3EThank%20you%20Sergei%3C%2FP%3E%3CP%3EIf%20you%20come%20up%20with%20a%20solution%20using%20regular%20functions%20let%20me%20know%3C%2FP%3E%3CP%3Emeantime%20I%20will%20take%20care%20of%20the%20VBA%20option%20which%20will%20be%20(for%20the%20first%20time%20ever)%20easier%20than%20other%20options.%3C%2FP%3E%3CP%3EHave%20a%20great%20day%3C%2FP%3E%3CP%3ENabil%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-790483%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790483%22%20slang%3D%22en-US%22%3EIt%20appears%20to%20me%20that%20your%20desired%20results%20can%20be%20returned%20through%20a%20formula!%20Please%20attach%20your%20sample%20Excel%20file%20to%20facilitate%20testing.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-790826%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790826%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENabil%2C%20at%20work%20I'm%20on%20Monthly%20(Targeted)%20channel%2C%20it's%20with%20DA%20about%20a%20month%20or%20so.%20Hope%20will%20be%20soon%20on%20Monthly%20channel%20as%20well.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20use%20the%20same%20pattern%20from%20MrExcel%20to%20split%20separated%20text%20on%20columns%2Frows%2C%20first%20in%20mind%20is%20to%20generate%20row%20with%20all%20names%20from%20the%20table%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125781iA7031367D9D38F76%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eby%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3E%3DTRIM(%0A%20%20%20%20%20%20MID(%0A%20%20%20%20%20%20%20%20SUBSTITUTE(%0A%20%20%20%20%20%20%20%20%20%20TEXTJOIN(%22%2C%22%2CTRUE%2CTable1%5B%5BPersons%20required%5D%3A%5BPersons%20required%5D%5D)%2C%0A%20%20%20%20%20%20%20%20%20%20%22%2C%22%2C%0A%20%20%20%20%20%20%20%20%20%20REPT(%22%20%22%2CLEN(%0A%20%20%20%20%20%20%20%20%20%20%20%20TEXTJOIN(%22%2C%22%2CTRUE%2CTable1%5B%5BPersons%20required%5D%3A%5BPersons%20required%5D%5D)%0A%20%20%20%20%20%20%20%20%20%20))%0A%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20(COLUMN()-COLUMN(%24W%241))*%0A%20%20%20%20%20%20%20%20LEN(TEXTJOIN(%22%2C%22%2CTRUE%2CTable1%5B%5BPersons%20required%5D%3A%5BPersons%20required%5D%5D))%2B1%2C%0A%20%20%20%20%20%20%20%20LEN(TEXTJOIN(%22%2C%22%2CTRUE%2CTable1%5B%5BPersons%20required%5D%3A%5BPersons%20required%5D%5D))%0A%20%20%20%20%20%20))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ewhen%20extract%20from%20here%20unique%20names%20only%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3E%3DIFERROR(INDEX(%24W%242%3A%24AJ%242%2CAGGREGATE(15%2C6%2C1%2F(COUNTIF(%24V%243%3AV%243%2C%24W%242%3A%24AJ%242)%3D0)*(COLUMN(%24W%242%3A%24AJ%242)-COLUMN(%24V%241))%2C1))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20add%20dates%20as%20in%20previous%20sample%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-792373%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-792373%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F386833%22%20target%3D%22_blank%22%3E%40balaram51%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20copied%20your%20data%20to%20the%20attached%20file.%20The%20formula%20in%20D2%2C%20copied%20down%20rows%20and%20across%20columns%2C%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(ROW()-1%26gt%3BSUMPRODUCT(--ISNUMBER(FIND(D%241%2C%24B%242%3A%24B%247)))%2C%22%22%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ELOOKUP(2%2C1%2F(FREQUENCY(0%2C1%2F(1%2B(%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E1%2F%24A%242%3A%24A%247*ISNUMBER(FIND(D%241%2C%24B%242%3A%24B%247))*(COUNTIF(D%241%3AD1%2C%24A%242%3A%24A%247)%3D0)))))%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%24A%242%3A%24A%247))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ENote%20that%20the%20results%20for%20Ronny%20and%20Max%20in%20your%20screenshot%20are%20incorrect.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-792505%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-792505%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F386833%22%20target%3D%22_blank%22%3E%40balaram51%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20fathomed%20that%20the%20repetition%20of%20ISNUMBER-FIND%20in%20my%20previous%20formula%20makes%20it%20patently%20less%20elegant.%20Thus%2C%20I%20was%20compelled%20to%20upgrade%20the%20formula%20in%20D2%20of%20the%20attached%20file%20to%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFERROR(1%2F(1%2F(%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ELOOKUP(2%2C1%2F(FREQUENCY(0%2C1%2F(1%2B(%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E1%2F%24A%242%3A%24A%247*ISNUMBER(FIND(D%241%2C%24B%242%3A%24B%247))*(COUNTIF(D%241%3AD1%2C%24A%242%3A%24A%247)%3D0)))))%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%24A%242%3A%24A%247)-%24A%242*(ROW()%26gt%3B2)))%2B%24A%242*(ROW()%26gt%3B2)%2C%22%22)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-792713%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-792713%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F386833%22%20target%3D%22_blank%22%3E%40balaram51%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%20Sergei%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%20and%20Hi%20Twifoo%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBy%20all%20means%20this%20is%20not%20a%20regular%20project%20and%20I%20am%20really%20excited%20to%20see%20this%20%22%3CSTRONG%3EALL%20You%20Can%20Eat%20Excel%20Buffet%3C%2FSTRONG%3E%22...%20You%20guys%20went%20above%20and%20beyond%20the%20MVP%20award.%20So%20I'll%20be%20nominating%20you%20for%20an%20imaginary%20award%20as%20follows%3A%3C%2FP%3E%3CP%3EExcel%20%3CSTRONG%3ENobel%3C%2FSTRONG%3EPrize%202019%20goes%20to%20Sergei%20Baklan%3C%2FP%3E%3CP%3EExcel%20%3CSTRONG%3EOscar%3C%2FSTRONG%3E2019%20goes%20to%20Twifoo%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20came%20out%20with%20a%20VBA%20solution%20to%20add%20to%20the%20series.%20But%2C%20anyway%20that%20was%20too%20much%20fun.%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-792759%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-792759%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F386833%22%20target%3D%22_blank%22%3E%40balaram51%3C%2FA%3E%26nbsp%3Bsought%2C%20and%20you%20eagerly%20waited%2C%20for%20a%20formula%20solution%20so%20I%20gave%20one.%20My%20later%20formula%20is%20inevitably%20an%20upgrade%20of%20the%20previous%20one.%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%2C%26nbsp%3B%3C%2FP%3E%3CP%3ETwifoo%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-796166%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-796166%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMost%20challenging%20part%20here%20is%20to%20extract%20list%20of%20names%20for%20the%20headers%20of%20the%20resulting%20table.%20If%20names%20are%20predefined%20any%20variant%20of%20regular%20formula%20is%20more%20preferable.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-796258%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-796258%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F386833%22%20target%3D%22_blank%22%3E%40balaram51%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20approach%20is%20basically%20that%20outlined%20by%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B%20and%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3Ebut%20I%20have%20set%20out%20to%20determine%20whether%20dynamic%20arrays%20have%20something%20to%20offer%20here.%3C%2FP%3E%3CP%3EThe%20condition%20that%20each%20name%20is%20present%20in%20the%20'persons%20required'%20list%20is%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20ISNUMBER(%20SEARCH(%20Names%2C%20PersonsRequired%20)%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3EThis%20spills%20to%20gives%20a%206x8%20Boolean%20array.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3ETo%20filter%20out%20the%20unwanted%20dates%20for%20each%20person%2C%20I%20need%20to%20change%20this%20formula%20to%20apply%20column%20by%20column%20by%20using%20the%20intersection%20operator%20on%20the%20names%20row%2C%20giving%20a%20final%20form%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20FILTER(%20date%2C%20ISNUMBER(%20SEARCH(%20%40Names%2C%20PersonsRequired%20)%20)%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3EEach%20instance%20of%20the%20formula%20gives%20a%20single%20dynamically-sized%20column%20of%20dates.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-796271%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-796271%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F386833%22%20target%3D%22_blank%22%3E%40balaram51%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20I%20have%20just%20about%20achieved%20the%20other%20part%20of%20deriving%20a%20list%20of%20unique%20names!%3C%2FP%3E%3CP%3EI%20started%20by%20defining%20a%20named%20formula%20'string'%20that%20runs%20all%20of%20the%20individual%20lists%20of%20names%20together%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20%22%7B%22%22%22%20%26amp%3B%20SUBSTITUTE(%20TEXTJOIN(%20%22%2C%20%22%2C%201%2C%20PersonsRequired%20)%2C%20%22%2C%22%2C%20%22%22%22%2C%22%22%22%20)%20%26amp%3B%20%22%22%22%7D%22%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3Eand%20then%20wraps%20them%20in%20double%20quotes%20and%20braces%20to%20create%20a%20string%20version%20of%20an%20array%20constant.%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EA%20further%20named%20formula%20'split'%20uses%20the%20old%20Macro4%20function%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3DEVALUATE(string)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3Eto%20create%20an%20array%20of%20individual%20person%20names.%3C%2FFONT%3E%3C%2FP%3E%3CP%3EI%20am%20not%20quite%20home%20and%20dry%20because%20one%20of%20the%20'Sam's%20has%204%20characters%20whereas%20the%20other%20two%20have%20the%20correct%20value.%26nbsp%3B%20This%20messes%20up%20the%20identification%20of%20unique%20values%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20UNIQUE(%20split%2C%20TRUE%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-796911%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-796911%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI%20am%20missing%20the%20second%20step%20in%20the%20applied%20steps%20(NameToList)%20I%20see%20the%20M%20code%20but%20how%20did%20you%20do%20it%20from%20the%20interface%20of%20the%20Query%20Editor%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-797050%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797050%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F386833%22%20target%3D%22_blank%22%3E%40balaram51%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20was%20getting%20late!%26nbsp%3B%20All%20that%20was%20needed%20was%20a%20TRIM%20so%20that%20the%20Names%20row%20is%20now%20given%20by%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20UNIQUE(%20TRIM(split)%2C%201%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EPlease%20accept%20may%20apologies%2C%20I%20came%20across%20this%20discussion%20and%20started%20answering%20without%20reading%20the%20prior%20posts%20with%20sufficient%20care.%26nbsp%3B%20I%20saw%20your%20first%20post%20used%20PQ%20and%20assumed%20the%20following%20posts%20were%20refinements%20of%20the%20original.%26nbsp%3B%20I%20now%20see%20that%20you%20followed%20up%20by%20exploring%20formula%20solutions%20and%20had%20already%20adopted%20the%20use%20of%20TEXTJOIN%3B%20I%20will%20examine%20your%20subsequent%20steps%20later.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-797051%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797051%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Nabil%2C%3C%2FP%3E%0A%3CP%3EThere%20is%20no%20such%20exact%20action%20in%20user%20interface.%20You%20may%20select%20column%2C%20from%20right%20click%20menu%20select%20Transform%20with%20any%20function%2C%20e.g.%20Trim%2C%20and%20after%20that%20in%20formula%20bar%20change%20Text.Trim%20on%20any%20other%20suitable%20function%2C%20in%20our%20case%26nbsp%3BSplitter.SplitTextByDelimiter(%22%2C%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-797216%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797216%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOut%20of%20curiosity%2C%20why%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%3E%3CSTRONG%3E%3CSPAN%20class%3D%22token%20punctuation%22%3E%3D%20(%3C%2FSPAN%3E%3CSPAN%20class%3D%22token%20function%22%3ECOLUMN%3C%2FSPAN%3E%3CSPAN%20class%3D%22token%20punctuation%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22token%20constant%22%3EN1%3C%2FSPAN%3E%3CSPAN%20class%3D%22token%20symbol%22%3E%3AZ1%3C%2FSPAN%3E%3CSPAN%20class%3D%22token%20punctuation%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22token%20operator%22%3E-%3C%2FSPAN%3E%3CSPAN%20class%3D%22token%20function%22%3ECOLUMN%3C%2FSPAN%3E%3CSPAN%20class%3D%22token%20punctuation%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22token%20variable%22%3E%24N%3C%2FSPAN%3E%3CSPAN%3E%24%3C%2FSPAN%3E%3CSPAN%20class%3D%22token%20number%22%3E1%3C%2FSPAN%3E%3CSPAN%20class%3D%22token%20punctuation%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22token%20punctuation%22%3E)%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Erather%20than%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CSPAN%3E%3CFONT%3E%3D%20SEQUENCE(%201%2C%2013%2C%200%20)%3C%2FFONT%3E%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EDo%20these%20columns%20have%20any%26nbsp%3B%20particular%20significance%20or%20is%20it%20a%20case%20of%20old%20habits%20dying%20hard%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Ep.s.%20You%20seem%20to%20be%20able%20to%20work%20magic%20with%20this%20user%20interface%20where%20I%20struggle%20to%20produce%20anything%20but%20basic%20text!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-797442%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797442%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Peter%2C%3C%2FP%3E%0A%3CP%3EYou%20are%20right%2C%20SEQUENCE%20is%20much%20better.%20I%20took%20basic%20pattern%20with%20regular%20formula%20and%20just%20wrapped%20it%20by%20UNIQUE%2C%20didn't%20think%20about%20farther%20improvement.%20Constant%20switching%20between%20DA%20and%20pre-DA%20Excel%20doesn't%20help...%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhich%20interface%20do%20you%20mean%2C%20this%20editor%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-797516%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797516%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20was%20your%20indented%20code%20with%20the%20pretty%20colours%20that%20I%20noticed.%3C%2FP%3E%3CP%3EI%20also%20have%20problems%20with%20warnings%20of%20invalid%20HTML%2C%20especially%20following%20cut-and%20-paste.%26nbsp%3B%20Submit%20the%20post%20a%20second%20time%20and%20it%20all%20comes%20good.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%22Constant%20switching%20between%20DA%20and%20pre-DA%20Excel%20doesn't%20help...%22%3C%2FP%3E%3CP%3EThat%20will%20only%20get%20worse%20as%20one's%20ways%20of%20thinking%20change%20to%20build%20on%20DAs.%20I%20tend%20to%20think%20in%20terms%20of%20creating%20entire%20arrays%20and%20it%20will%20come%20as%20a%20shock%20working%20for%20clients%20with%20old%20versions%20of%20Excel%20(including%202019)!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-797745%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797745%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIntended%20code%20that's%20manual%20work%20with%20Notepad%2B%2B%20which%20I%20usually%20use%20for%20long%20formulas%3B%20or%20Alt%2BEnter%20in%20formula%20bar.%20There%20are%20some%20tools%20for%20formulas%20formatting%20but%20I%20still%20prefer%20manual%20one.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-797775%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797775%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EWhat%20does%20the%20%22Evaluate%22%20function%20do%3F%3C%2FP%3E%3CP%3Ewould%20it%20still%20work%20if%20we%20have%20lots%20of%20names%20(%26gt%3B%20255%20Character)%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-797784%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797784%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEVALUATE%20evaluates%20formula%20defined%20as%20text%2C%20it%20works%20if%20you%20add%20entire%20expression%20to%20name%20manager%2C%20e.g%3C%2FP%3E%0A%3CP%3E%3DEVALUATE(%22A1%2BB1%22)%20named%20as%20MyFormula%20and%20called%20as%20%3DMyFormula.%20You%20shall%20save%20your%20workbook%20in%20macro-enabled%20format.%3C%2FP%3E%0A%3CP%3EHave%20no%20idea%20how%20long%20the%20text%20could%20be.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-797803%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797803%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EForgot%20to%20say%2C%20EVALUATE%20is%20not%20recalculated.%20The%20workaround%20is%20to%20wrap%20it%20with%20some%20volatile%20function.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-797957%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797957%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EEVALUATE%20%3C%2FFONT%3E%3C%2FP%3E%3CP%3EThis%20is%20a%20'left%20over'%20from%20the%20old%20Macro%20language%20that%20preceded%20VBA.%26nbsp%3B%20In%20modern%20Excel%20it%20is%20only%20recognised%20if%20it%20is%20used%20within%20a%20Named%20Formula.%26nbsp%3B%20It%20is%20used%20to%20e%3CFONT%3Evaluate%20a%20formula%20or%20expression%20that%20is%20in%20the%20form%20of%20text%20and%20return%20the%20result.%26nbsp%3B%20%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3ESyntax%20%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3E%3CSTRONG%3E%3D%20EVALUATE(formula_text)%3C%2FSTRONG%3E%20%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3E%3CEM%3EFormula_text%20is%20the%20expression%20in%20the%20form%20of%20text%20that%20you%20want%20to%20evaluate%3C%2FEM%3E.%20%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3ERemarks%3A%20%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EUsing%20EVALUATE%20is%20similar%20to%20selecting%20an%20expression%20within%20a%20formula%20in%20the%20formula%20bar%20and%20pressing%20the%20Recalculate%20key%20(F9%20in%20Microsoft%20Excel%20for%20Windows).%20EVALUATE%20replaces%20an%20expression%20with%20a%20value.%20%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%3A%20A%20named%20formula%20is%20just%20a%20snippet%20of%20a%20formula%20held%20as%20a%20text%20string.%26nbsp%3B%20It%20is%20only%20processed%20when%20a%20formula%20placed%20within%20a%20cell%20refers%20to%20it%2C%20in%20which%20case%20the%20snippet%20is%20calculated%20as%20if%20it%20were%20an%20inner%20element%20of%20the%20cell%20formula%2C%20nested%20within%20it.%26nbsp%3B%20I%20used%20named%20formulas%20to%20give%20meaning%20to%20the%20values%20calculated%20by%20the%20inner%20elements%20of%20a%20nested%20formula%20and%20to%20make%20the%20cell%20formula%20shorter%20and%20more%20easily%20understood.%26nbsp%3B%20Other%20plusses%20are%20that%20a%20named%20formula%20is%20always%20evaluated%20as%20an%20array%20formula%20and%2C%20in%20this%20case%2C%20it%20is%20Excel's%20most%20comprehensive%20calculation%20environment.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-809752%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-809752%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3Bcant%20thank%20you%20enough%20for%20this.I%20was%20unwell%20for%20a%20while%20so%20could%20not%20thank%20you%20earlier.%20There%20were%20wonderful%20suggestions%20but%20for%20me%20this%20solution%20works%20best.%20You%20have%20saved%20so%20much%20of%20my%20time.%20You%20people%20are%20simply%20awesome.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-809775%22%20slang%3D%22en-US%22%3ERe%3A%20extract%20unique%20values%20matching%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-809775%22%20slang%3D%22en-US%22%3EYour%20gratitude%20delights%20me%20and%20I%20wish%20you%20all%20the%20best!%3C%2FLINGO-BODY%3E
Highlighted
New 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
Highlighted

@nabilmourad 

Hi Nabil,

There is no such exact action in user interface. You may select column, from right click menu select Transform with any function, e.g. Trim, and after that in formula bar change Text.Trim on any other suitable function, in our case Splitter.SplitTextByDelimiter(",")

 

Highlighted

@Sergei Baklan 

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!

Highlighted

@Peter Bartholomew 

 

Hi Peter,

You are right, SEQUENCE is much better. I took basic pattern with regular formula and just wrapped it by UNIQUE, didn't think about farther improvement. Constant switching between DA and pre-DA Excel doesn't help...

 

Which interface do you mean, this editor?

@Sergei Baklan 

It was your indented code with the pretty colours that I noticed.

I also have problems with warnings of invalid HTML, especially following cut-and -paste.  Submit the post a second time and it all comes good.

 

"Constant switching between DA and pre-DA Excel doesn't help..."

That will only get worse as one's ways of thinking change to build on DAs. I tend to think in terms of creating entire arrays and it will come as a shock working for clients with old versions of Excel (including 2019)!

Highlighted

@Peter Bartholomew 

Intended code that's manual work with Notepad++ which I usually use for long formulas; or Alt+Enter in formula bar. There are some tools for formulas formatting but I still prefer manual one.

Highlighted

@Peter Bartholomew 

Hi

What does the "Evaluate" function do?

would it still work if we have lots of names (> 255 Character)

Thanks

Highlighted

@nabilmourad 

EVALUATE evaluates formula defined as text, it works if you add entire expression to name manager, e.g

=EVALUATE("A1+B1") named as MyFormula and called as =MyFormula. You shall save your workbook in macro-enabled format.

Have no idea how long the text could be.

 

Highlighted

@nabilmourad 

Forgot to say, EVALUATE is not recalculated. The workaround is to wrap it with some volatile function.

Highlighted

@nabilmourad 

EVALUATE

This is a 'left over' from the old Macro language that preceded VBA.  In modern Excel it is only recognised if it is used within a Named Formula.  It is used to evaluate a formula or expression that is in the form of text and return the result. 

Syntax

= EVALUATE(formula_text)

Formula_text is the expression in the form of text that you want to evaluate.

 

Remarks:

Using EVALUATE is similar to selecting an expression within a formula in the formula bar and pressing the Recalculate key (F9 in Microsoft Excel for Windows). EVALUATE replaces an expression with a value.

 

Note: A named formula is just a snippet of a formula held as a text string.  It is only processed when a formula placed within a cell refers to it, in which case the snippet is calculated as if it were an inner element of the cell formula, nested within it.  I used named formulas to give meaning to the values calculated by the inner elements of a nested formula and to make the cell formula shorter and more easily understood.  Other plusses are that a named formula is always evaluated as an array formula and, in this case, it is Excel's most comprehensive calculation environment.

Highlighted

@Twifoo cant thank you enough for this.I was unwell for a while so could not thank you earlier. There were wonderful suggestions but for me this solution works best. You have saved so much of my time. You people are simply awesome.

Highlighted
Your gratitude delights me and I wish you all the best!