Hi,
I have a large dataset detailing attendance over time at a leisure centre. At the moment, my data is organised as the calendar week in the top row, with each member’s number of visits in the rows below. Now I would like to restructure my data and organise it as visits in the first month, second month etc. I want to get this output in a separate sheet that is slightly different, and may contain only a sub-sample of member IDs from this table.
This is an example of how this data looks:
<tbody>
</tbody>
To now convert this data into visits during the 1st month of their membership, 2nd month, and so on, I need to come up with a formula to first match the ID in both sheets (if there is a match...), and then match the membership start week with the top row, to then find the value where both variables intersect, and summarise the following 4 cells.
This is what I want the new table to look like:
<tbody>
</tbody>
To make this even more complicated, I need to be careful about not returning 0’s into the new dataset, when there is no entry due to the termination of the membership. In this case the target cell needs to be blank.
As I have a very large dataset, I am trying to do all this in one go (or in as little steps as possible). I tried different combinations of IF functions and VLOOKUPS and HLOOKUPs, but am overwhelmed by the complexity of the question and nesting the criteria into each other, and was hoping somebody has an idea how to solve this issue?
Any help would be appreciated!
Thanks!
I have a large dataset detailing attendance over time at a leisure centre. At the moment, my data is organised as the calendar week in the top row, with each member’s number of visits in the rows below. Now I would like to restructure my data and organise it as visits in the first month, second month etc. I want to get this output in a separate sheet that is slightly different, and may contain only a sub-sample of member IDs from this table.
This is an example of how this data looks:
| ID | Membership Start | Membership END | 1 2018 | 2 2018 | 3 2018 | 4 2018 | 5 2018 | 6 2018 |
| 1 | 2 2018 | 2 2019 | 2 | 3 | 2 | 4 | 3 | |
| 2 | 3 2018 | 1 | 2 | 1 | 2 | |||
| 3 | 1 2018 | 5 2018 | 1 | 1 | 1 | 1 |
<tbody>
</tbody>
To now convert this data into visits during the 1st month of their membership, 2nd month, and so on, I need to come up with a formula to first match the ID in both sheets (if there is a match...), and then match the membership start week with the top row, to then find the value where both variables intersect, and summarise the following 4 cells.
This is what I want the new table to look like:
| ID | Visits 1st month | Visits 2nd month | Visits 3rd month | Visits 4th month |
| 1 | 11 | 8 | 12 | 11 |
| 2 | 6 | 4 | 5 | 3 |
| 3 | 4 |
<tbody>
</tbody>
To make this even more complicated, I need to be careful about not returning 0’s into the new dataset, when there is no entry due to the termination of the membership. In this case the target cell needs to be blank.
As I have a very large dataset, I am trying to do all this in one go (or in as little steps as possible). I tried different combinations of IF functions and VLOOKUPS and HLOOKUPs, but am overwhelmed by the complexity of the question and nesting the criteria into each other, and was hoping somebody has an idea how to solve this issue?
Any help would be appreciated!
Thanks!
