Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- :
- Excel
- :
- General Discussion
- :
- Re: extract unique values matching a text string

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-03-2019 12:36 PM

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.

Labels:

30 Replies

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-03-2019 03:12 PM - edited 08-03-2019 03:15 PM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-05-2019 04:42 PM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-06-2019 05:47 AM

If by Power Query some simple coding is required, not everything could be done from user interface. For such sample (I sorted names alphabetically)

the generated script is

```
let
Source = Excel.CurrentWorkbook()
{[Name="Table1"]}[Content],
NamesToLists = Table.TransformColumns(
Source,
{{"Persons required", Splitter.SplitTextByDelimiter(",")}}
),
ExpandNames = Table.ExpandListColumn(
NamesToLists,
"Persons required"
),
TrimmNames = Table.TransformColumns(
ExpandNames,
{{"Persons required", Text.Trim, type text}}
),
FormatAsDate = Table.TransformColumnTypes(
TrimmNames,
{{"Date", type date}}
),
GroupNames = Table.Group(
FormatAsDate,
{"Persons required"},
{{"Count", each _, type table [Date=date, Persons required=text]}}
),
AddCustom = Table.AddColumn(
GroupNames,
"Custom", each [Count][Date]
),
SortByNames = Table.Sort(
AddCustom,
{{"Persons required", Order.Ascending}}
),
CreateTable = Table.FromColumns(
SortByNames[Custom],
SortByNames[Persons required]
)
in
CreateTable
```

You may check step by step in the attached file.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-06-2019 05:55 AM

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-06-2019 05:58 AM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-06-2019 06:04 AM

Eagerly waiting for it

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-06-2019 08:00 AM

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

In N3 for that

```
=SORT(
UNIQUE(
TRIM(
MID(
SUBSTITUTE(
TEXTJOIN(",",TRUE,Table1[[Persons required]:[Persons required]]),
",",
REPT(" ",LEN(
TEXTJOIN(",",TRUE,Table1[[Persons required]:[Persons required]])
))
),
(COLUMN(N1:Z1)-COLUMN($N$1))*
LEN(TEXTJOIN(",",TRUE,Table1[[Persons required]:[Persons required]]))+1,
LEN(TEXTJOIN(",",TRUE,Table1[[Persons required]:[Persons required]]))
)
),TRUE),,1,TRUE)
```

and pull dates in more traditional way

```
=IFERROR(
INDEX(Table1[Date],
AGGREGATE(15,6,
1/ISNUMBER(SEARCH(N$3,Table1[Persons required]))*(ROW(Table1[Persons required])-ROW(Table1[[#Headers], [Persons required]])),ROW()-ROW(N$3))
),
"")
```

Without dynamic arrays that's most probably with helper column/row to extract all names and after that select unique from them. Or VBA.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-06-2019 08:11 AM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-06-2019 09:12 AM

It appears to me that your desired results can be returned through a formula! Please attach your sample Excel file to facilitate testing.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-06-2019 01:11 PM

Nabil, at work I'm on Monthly (Targeted) channel, it's with DA about a month or so. Hope will be soon on Monthly channel as well.

If use the same pattern from MrExcel to split separated text on columns/rows, first in mind is to generate row with all names from the table

by

```
=TRIM(
MID(
SUBSTITUTE(
TEXTJOIN(",",TRUE,Table1[[Persons required]:[Persons required]]),
",",
REPT(" ",LEN(
TEXTJOIN(",",TRUE,Table1[[Persons required]:[Persons required]])
))
),
(COLUMN()-COLUMN($W$1))*
LEN(TEXTJOIN(",",TRUE,Table1[[Persons required]:[Persons required]]))+1,
LEN(TEXTJOIN(",",TRUE,Table1[[Persons required]:[Persons required]]))
))
```

when extract from here unique names only

`=IFERROR(INDEX($W$2:$AJ$2,AGGREGATE(15,6,1/(COUNTIF($V$3:V$3,$W$2:$AJ$2)=0)*(COLUMN($W$2:$AJ$2)-COLUMN($V$1)),1)),"")`

and add dates as in previous sample

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-06-2019 10:14 PM

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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-07-2019 12:12 AM

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),"")**

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-07-2019 03:01 AM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-07-2019 03:32 AM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-08-2019 02:36 PM

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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-08-2019 03:38 PM

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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-08-2019 04:34 PM

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 )**

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-09-2019 12:30 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-09-2019 02:06 AM

It was getting late! All that was needed was a TRIM so that the Names row is now given by

**= UNIQUE( TRIM(split), 1 )**

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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-09-2019 02:09 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-09-2019 03:59 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-09-2019 06:40 AM

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?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-09-2019 07:38 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-09-2019 10:12 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-09-2019 10:54 AM

Hi

What does the "Evaluate" function do?

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

Thanks

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-09-2019 11:08 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-09-2019 11:16 AM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-09-2019 12:43 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-18-2019 01:30 AM

@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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-18-2019 02:07 AM

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

by balaram51 on August 03, 2019

Microsoft Store

Education

Developer