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

Anglais428v2

New Member
Joined
Jun 19, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,590
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

Anglais428v2

New Member
Joined
Jun 19, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,590
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,281
Messages
5,635,295
Members
416,851
Latest member
zeldadav

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top