RockandGrohl
Well-known Member
- Joined
- Aug 1, 2018
- Messages
- 788
- Office Version
- 2010
- Platform
- Windows
Hello all.
Let's say I'm trying to count how many products are applicable to papers, the data I have will look like this:
And the products look like this
So what I'm trying to do is match up and count papers and products that have applicable pickups. The trouble is that they are both within a list, so a countif with wildcards will only work one way.
The expected result would be like this:
I guess it's about matching arrays? I have a similar process in another sheet, but it does this:
And from there it tells me how many times either Saltash or Plymouth appear in the pickup list for the product in I2
My question then is there an easy way to do this for my task way above, having a list of papers and their pickups, and wanting to find out how many products can fit in that group, AND THEN AS A WILDCARD, HOW MANY PRODUCTS WITH A CRITERIA, SO I CAN SEE PRODUCTS PER MONTH TOO!
Thanks.
Let's say I'm trying to count how many products are applicable to papers, the data I have will look like this:
Paper Name | Pickups |
Bridlington Press | Bridlington, Scarborough, Filey |
Ross Gazette | Ross-on-Wye, Cinderford, Hereford, Newent |
And the products look like this
Product | Pickups |
Isle of Wight Party | Whitby, Scarborough, Pickering, Malton, York, Selby, Pontefract, Doncaster, Worksop, Retford, Bridlington |
Somerset Sunsets | Scarborough, Cinderford, Worcester |
So what I'm trying to do is match up and count papers and products that have applicable pickups. The trouble is that they are both within a list, so a countif with wildcards will only work one way.
The expected result would be like this:
Paper Name | Pickups | Products |
Bridlington Press | Bridlington, Scarborough, Filey | 2 |
Ross Gazette | Ross-on-Wye, Cinderford, Hereford, Newent | 0 |
I guess it's about matching arrays? I have a similar process in another sheet, but it does this:
- Takes paper pickups
- Separates them out into individual cells
- Determines which pickups are coach pickups
- Discards non-coach pickups
- Applies a variable to each one (Pickup 1, Pickup 2, Pickup 3) etc
- Loads the product list and applies a countif array wildcard formula
Excel Formula:
=COUNT(SEARCH({", Saltash,",", Plymouth,",", Blank,",", Blank,",", Blank,",", Blank,",", Blank,"},", "&I2&","))
And from there it tells me how many times either Saltash or Plymouth appear in the pickup list for the product in I2
My question then is there an easy way to do this for my task way above, having a list of papers and their pickups, and wanting to find out how many products can fit in that group, AND THEN AS A WILDCARD, HOW MANY PRODUCTS WITH A CRITERIA, SO I CAN SEE PRODUCTS PER MONTH TOO!
Thanks.