Home

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
balaram51
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

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?

Highlighted

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

@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(",")

 

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

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

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

@Peter Bartholomew 

Hi

What does the "Evaluate" function do?

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

Thanks

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

 

@nabilmourad 

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

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

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

Your gratitude delights me and I wish you all the best!
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies