Helper columns and perhaps unique(filter functions required

[ollyhughes1982]

Hi all,

I have a workbook (link below) of all of my parkrun volunteering stints to date. I am trying to work out the following four scenarios (red highlighted worksheets):

1. # of Unique Events (Venues) at which x Unique Roles have been completed. e.g. Completing 5 Unique Roles at 10 Unique Events (Venues), gives a result of 5.
2. # of Unique Roles that have been completed at the same # of Unique Events (Venues). e.g. Completing 5 Unique Roles at 5 Unique Events (Venues), gives a result of 5.
3. # of particular Unique Roles that have been completed at the same # of particular Unique Events (Venues). e.g. Completing all three Unique Roles A, B & C at all three Unique Events (Venues) X, Y & Z, gives a result of 3.
4. # of particular Unique Roles that have been completed at the same # of particular Unique Events (Venues). e.g. Completing all three Unique Roles A, B & C at all three Unique Events (Venues) X, Y & Z, at least 3 times at each, gives a result of 3.

I am finding this extremely hard to do. I am thinking I will at least need some helper columns in the ‘All Completed VS’ worksheet (where all stints are recorded) before being able to work these out. I have included some worksheets (highlighted in orange), where I have attempted to achieve these manually.

I am completely stumped with these. I wonder if any of you Excel wizards may be able to help me out! Appreciate this is a difficult one.

Link to file: parkrun - Volunteering Example.xlsx
(Not a large file, so should open straight away).

Thanks in advance.

Olly.

 

[ollyhughes1982]

etaf has now kindly helped in a different post (Possibly countifs for two helper columns) with two helper columns to start this off, which I have now added into the file. Hopefully this will help illustrate what i am trying to achieve. These two helper columns identify each unique role completed at each event (venue), as well as the amount of times they have been completed at each particular venue.​

 

[etaf]

Given i replied to your other thread and we have now changed the spreadsheet and added another helper column
Then it maybe worth , just asking the additional questions , so as not to duplicate the columns already created.
Also if you could provide the new spreadsheet , with the additional heading and manual results expected for the sample sheet , and perhaps a note as to why you came up with that number

lets take this request as an example

1. # of particular Unique Roles that have been completed at the same # of particular Unique Events (Venues). e.g. Completing all three Unique Roles A, B & C at all three Unique Events (Venues) X, Y & Z, at least 3 times at each, gives a result of 3.

We would need a list of all the possible events and all the possible roles for excel to know

Completing all three Unique Roles A, B & C

How does excel know that its only 3 A,B,C. and not 12 or 40 or 1 ?
How does excel know how many events should have those roles , 1 event may have 10 another event only 2
and if thats the case then you can never complete ALL 10 roles at those 2 events because they dont exist.

 

[ollyhughes1982]

Given i replied to your other thread and we have now changed the spreadsheet and added another helper column
Then it maybe worth , just asking the additional questions , so as not to duplicate the columns already created.
Also if you could provide the new spreadsheet , with the additional heading and manual results expected for the sample sheet , and perhaps a note as to why you came up with that number

lets take this request as an example

1. # of particular Unique Roles that have been completed at the same # of particular Unique Events (Venues). e.g. Completing all three Unique Roles A, B & C at all three Unique Events (Venues) X, Y & Z, at least 3 times at each, gives a result of 3.

We would need a list of all the possible events and all the possible roles for excel to know

How does excel know that its only 3 A,B,C. and not 12 or 40 or 1 ?
How does excel know how many events should have those roles , 1 event may have 10 another event only 2
and if thats the case then you can never complete ALL 10 roles at those 2 events because they dont exist.

Yes, apologies, I shouldn't have tried to lump this all into one thread. I will do them each in separate threads. Thanks. Is there a way I can close this thread, to mitigate possible confusion? Thanks again.

 

[etaf]

you can marked solved i think
OR use the REPORT button to ask a moderator to close/delete the thread.

maybe dont put them all in separate threads , that may cause more confusion, as you may be getting lots of different answers for essential the same question and different ways to set things up - which maybe common to all 4 of the requests.
For example, some people may use a lookup table , others an array , when in fact for ALL for requests a lookup table is needed anyway.

i would just open one thread with ALL the questions based on where you are now.

BUT be as clear as you can and in the sample give examples of expected results , thats always helpful to see and then any formulas can be compared against what you required, also any notes as to why the results are as you have manually input can also be useful.

A manual set of results , added to the previous post at the beginning may have saved a lot of time.

 

[ollyhughes1982]

Yes, thanks. I just find this one set of calculations extremely confusing. Will do my best to explain as clearly as possible. Thanks.

 

[etaf]

ok, added some more info on last post, dont try an explain the calc more the outcome and why perhaps
Quoted here again

maybe dont put them all in separate threads , that may cause more confusion, as you may be getting lots of different answers for essential the same question and different ways to set things up - which maybe common to all 4 of the requests.
For example, some people may use a lookup table , others an array , when in fact for ALL for requests a lookup table is needed anyway.

i would just open one thread with ALL the questions based on where you are now.

BUT be as clear as you can and in the sample give examples of expected results , thats always helpful to see and then any formulas can be compared against what you required, also any notes as to why the results are as you have manually input can also be useful.

A manual set of results , added to the previous post at the beginning may have saved a lot of time.

 

[ollyhughes1982]

Thanks, working on the file now and have added in a list of all possible roles in a hidden worksheet (worksheet name: 'Volunteer Roles'). The list of all possible events was already there in a hidden worksheet (worksheet name: 'parkrun Reader Dump'). I've unhidden these and highlighted them in blue.

Thanks to all of the forum's help, points 1 and 2 are now solved. I am trying to figure out how to even manually work out points 3 and 4 - they seem pretty complicated. The logic for them is in cell A2 on both worksheets.

 

[etaf]

isnt this thread from today , the same as we have already answered ?
seen quite a few threads that appear to be all about the same thing

Find values according to criteria and count instances

Hi all, I have a worksheet (‘All Completed VS’) with all of the parkruns at which I have completed volunteering stints and which role I undertook at each one. What I am trying to do is populate columns AA to CB in the ‘Completed Unique VS E(V)’ worksheet (below). This worksheet shows all...

www.mrexcel.com

I have two columns for each type of the 27 possible volunteering roles available. In the first column for each I want to show a ‘1’ if I have completed that role at that particular event (conditional formatting is set to fill the cell with colour). In the second column for each type of role, I want to show the number of times it has been completed at that venue.

I thought answered here

Possibly countifs for two helper columns

Hi all, I have the ‘All Completed VS’ worksheet (screenshot below and link attached). The worksheet shows all of the parkruns at which I have volunteered and the volunteering role that I completed at each. I am looking to do some calculations on this worksheet, but cannot figure out how to...

www.mrexcel.com

But I see quite a few threads now that appear to be on the same subject and confused on whats been answered and not

 

[ollyhughes1982]

I have now uploaded a new version of this workbook, with the following included:

Two new columns for each possible volunteering role to the worksheet that lists all of the unique events (venues) at which I have completed volunteering stints. Worksheet name is: ‘Completed Unique VS E(V)’. For each possible volunteering role there is a column that has either a 1 or a blank (“”): 1 indicates that the role has been completed at that unique event (venue) “” where it hasn’t. The second column for each role then counts how many times that role has been completed at that unique event (partial screenshot below).

I believe this grid-like structure should now provide what is required to solve points 3 and 4.

I will now manually work out from these fields what I expect to see and provide an updated version with these manual calculations in soon. The community will then hopefully be able to come up with a solution that automates this process.

Thanks for all of the help so far.