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

[RockandGrohl]

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.

 

[Peter_SSs]

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
	J	K	L	M	N
1
2	Hull, York, Harrogate	2			Hull
3	Hull, Doncaster, Halifax, York, Newcastle	5			Doncaster
4		0			Halifax
5	London	0			York
6	York	1			Newcastle
7
Count Strings

Cell Formulas
Range	Formula
K2:K6	K2	=COUNT(SEARCH(" "&N$2:N$6&","," "&J2&","))

 

[Peter_SSs]

Actually, that could give false results, try this slight modification

20 09 08.xlsm
	J	K	L	M	N
1
2	Hull, York, Harrogate	2			Hull
3	Hull, Doncaster, Halifax, York, Newcastle	5			Doncaster
4		0			Halifax
5	London	0			York
6	York	1			Newcastle
7	New York	0
8
Count Strings

Cell Formulas
Range	Formula
K2:K7	K2	=COUNT(SEARCH(", "&N$2:N$6&",",", "&J2&","))

 

[RockandGrohl]

Hi, thanks I've changed that, hopefully it updates soon (Excel 2010, Windows)

I've got the formula set up like this:

=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]

Thanks for updating your profile.

I've got the formula set up like this:

=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]

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]

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]

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]

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]

Good news. Excel 365 doesn't require the CSE entry any more and so I'm afraid I easily tend to forget it already.