Count lots of values in a list, within a string.

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
577
Office Version
  1. 2010
Platform
  1. Windows
Hi all,

Been looking at this excellent little formula by Rick:

Count number of occurrences of list of characters in a single string

This is pretty much what I want to do, but I have over 73 values in a list and I want to check them against another list, which contains a comma-separated string of values.


Column J is the list of strings where the values can look like this "Hull, York, Harrogate"

I need to check how many times my range of values appears in each string. So if my range of values is:

Hull
Doncaster
Halifax
York
Newcastle

Then the expected result is "2"


Thanks all.
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this is it

20 09 08.xlsm
JKLMN
1
2Hull, York, Harrogate2Hull
3Hull, Doncaster, Halifax, York, Newcastle5Doncaster
40Halifax
5London0York
6York1Newcastle
7
Count Strings
Cell Formulas
RangeFormula
K2:K6K2=COUNT(SEARCH(" "&N$2:N$6&","," "&J2&","))
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Actually, that could give false results, try this slight modification

20 09 08.xlsm
JKLMN
1
2Hull, York, Harrogate2Hull
3Hull, Doncaster, Halifax, York, Newcastle5Doncaster
40Halifax
5London0York
6York1Newcastle
7New York0
8
Count Strings
Cell Formulas
RangeFormula
K2:K7K2=COUNT(SEARCH(", "&N$2:N$6&",",", "&J2&","))
 

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
577
Office Version
  1. 2010
Platform
  1. Windows
Hi, thanks I've changed that, hopefully it updates soon (Excel 2010, Windows)

I've got the formula set up like this:

VBA Code:
=COUNT(SEARCH(", "&[Book2]Sheet2!$A$2:$A$73&",",", "&J3&","))

Where Book 2 Sheet 2 has the list of placenames.

The first string is:

Blackpool, Flying from Manchester (MAN), (RS) Preston

And Blackpool is in the list of values (A2:A73) - "Blackpool"

Cheers.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks for updating your profile. (y)


I've got the formula set up like this:

VBA Code:
=COUNT(SEARCH(", "&[Book2]Sheet2!$A$2:$A$73&",",", "&J3&","))
Where Book 2 Sheet 2 has the list of placenames.

The first string is:

Blackpool, Flying from Manchester (MAN), (RS) Preston
And Blackpool is in the list of values (A2:A73) - "Blackpool"
So, is that working for you?
 

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
577
Office Version
  1. 2010
Platform
  1. Windows
The result of the formula is "0" for Blackpool when it should be a lot more. When I copy the formula down they are all 0's, so something is amiss.

Cheers.
 

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
577
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

In fact if I emulate the example given above, word for word, it shows only one count for "Hull, York, Harrogate" even though there are 2 results in the list "Hull" and "York"

Cheers.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
In fact if I emulate the example given above, word for word, it shows only one count for "Hull, York, Harrogate" even though there are 2 results in the list "Hull" and "York"
Does it behave differently if you go back in to edit that formula then confirm it with Ctrl+Shift+Enter, not just Enter?

If you still get the same behaviour then it would indicate there must be something different about your sheet compared to mine since you can see that mine worked. It could be that the space characters in your data are not normal space characters.
  • Can you check that?
  • Can you post some small sample data (from both worksheets) with XL2BB then I can be more sure that I am testing with realistic data?
 

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
577
Office Version
  1. 2010
Platform
  1. Windows
Does it behave differently if you go back in to edit that formula then confirm it with Ctrl+Shift+Enter, not just Enter?

If you still get the same behaviour then it would indicate there must be something different about your sheet compared to mine since you can see that mine worked. It could be that the space characters in your data are not normal space characters.
  • Can you check that?
  • Can you post some small sample data (from both worksheets) with XL2BB then I can be more sure that I am testing with realistic data?

Lol, it does work with CTRL SHIFT ENTER and tbh I have no idea why I didn't try it before. Thanks!!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Good news. Excel 365 doesn't require the CSE entry any more and so I'm afraid I easily tend to forget it already. :cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,127,405
Messages
5,624,574
Members
416,036
Latest member
eloisa manzanarez

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