Count the number of unique records based on multiple column criteria including partial string match

Anglais428v2

New Member
I have a dataset whereby I would like to count the number of unique records. I am doing this with the formula =SUM(--(FREQUENCY(IF(List=A2,ID),ID)>0))
Where List is a range, A2 is a piece of text (which is looked up against the List range) and where the IDs (record IDs) are unique. This works fine when A2 is matched exactly in the list. What I also want to do is include a count for the partial match.

E.g. if cell A2 is "United Kingdom", the formula counts all "united kingdom" in the list and subtracts any where the IDs are the same (i.e. deduping).
I would like to modify the formula so that it would count in the same manner but if cell A2 contained "united kingdom; united states". It should be able to get the partial match of "united kingdom" from "united kingdom; united states" and dedupe again based on the IDs.

Any ideas would be appreciated. If feel it is the highlighted part of the formula that needs fixing: =SUM(--(FREQUENCY(IF(List=A2,ID),ID)>0)) to incorporate the partial match, rather than full cell match.

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

jasonb75

Well-known Member
There are a few ways you could do it, starting off with the simple methods, the first 2 that come to mind.
Excel Formula:
``=SUM(--(FREQUENCY(IF(ISNUMBER(SEARCH(A2,list)),ID),ID)>0))``
Excel Formula:
``=COUNT(UNIQUE(FILTER(ID,ISNUMBER(SEARCH(A2,List))))``
Note that you could get problems with partial search terms in the list, for example 'apple' in A2 would also count rows with 'pineapple' in the list range. Excluding such entries if it could be a potential problem with your data would need some not so simple methods.

Anglais428v2

New Member
Great, thanks Jason. I didn't think of incorporating SEARCH. This is working a treat. And noted regarding your second point.

jasonb75

Well-known Member
You're welcome, thanks for the feedback

There are ways around the point that I mentioned, but I would only use it if you think it will be needed. If the list covers a large range then it could impact calculation time quite significantly.

Using your example of "united kingdom; united states" to set the format of the formula, you would need to change the search part to
Excel Formula:
``ISNUMBER(SEARCH("; "&A2&";","; "&list&";"))``
As with many things though, consistent entry is required for it to work.
For example, if you use a comma instead of a semicolon, or miss the space after the semicolon then it will not work correctly.

Replies
2
Views
101
Replies
5
Views
178
Replies
10
Views
404
Replies
2
Views
373
Replies
1
Views
559

1,129,528
Messages
5,636,852
Members
416,945
Latest member
Himu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back