Apr 20 2023 02:01 AM
Dear Excel Community,
I was wondering if you could advise or give some guidance with a problem i have and what the best solution would be ?
Please see attached Workbook. In the Data sheet, we have the master data. In the Analysis sheet, i would like to collate the information
What i am looking to achieve is in the Analysis sheet, list all the names of the requesters (no duplicates), the number of requests they have made and then the total cost of those requests.
There are a couple of caveats though
1) If there are multiple requests by the same requester, on the analysis sheet, i only want the name listed once.
2) The list of requesters can change as there can be multiple requesters in a large organisation so the list can not be fixed.
3) The analysis tab should only fetch data for requests which are authorised.
I'm not sure where to start with the above. I can use a countif or sumif to fetch the data but the bit i'm struggling with is how to retrieve the requester name and list it only once without duplicating.
Hopefully someone can assist and provide a solution.
As always, thanking you in advance !
Raj
Apr 20 2023 02:51 AM
SolutionApr 20 2023 04:22 AM
Apr 20 2023 05:02 AM
In the attached version, I have also added a pivot table as an alternative.
Apr 20 2023 06:17 AM
May 14 2023 02:46 AM
Thank you for previously supplying me with a solution to my problem. I'm wondering if you could help me tweak the formula based on the attached workbook.
Essentially i want to solve the same issue as previously highlighted although this time i want to be able to group by teams and the array needs to cover multiple months.
For example, In the analysis sheet, there are 4 teams. Essentially i want the formula to look across the 3 months, and populate a list of unique requesters, the no of requests and total amount spent against their team in the analysis tab.
Hope that makes sense ?
Thanking you in advance for you help.
Raj
May 14 2023 02:46 AM
Thank you for previously supplying me with a solution to my problem. I'm wondering if you could help me tweak the formula based on the attached workbook.
Essentially i want to solve the same issue as previously highlighted although this time i want to be able to group by teams and the array needs to cover multiple months.
For example, In the analysis sheet, there are 4 teams. Essentially i want the formula to look across the 3 months, and populate a list of unique requesters, the no of requests and total amount spent against their team in the analysis tab.
Hope that makes sense ?
Thanking you in advance for you help.
Raj
May 14 2023 06:40 AM
I think this will do the trick; please check manually once that the answers here are correct. Don't hesitate if you need any explanation.
Bye!
May 15 2023 03:22 AM
May 15 2023 03:53 AM
i'm sorry to be a pain. I took the formula you provided and tried to transpose on to another spreadsheet but it's not accept it. See attached.
The problem i have is i can't share the actual spreadsheet i need to apply this to as it contains sensitive informaiton and i would be breaking Daat protection rules etc.
So i mocked up something similar and was going to copy the formula over to the real spreadsheet. But it's not working as it should :(
Any help / advice would be welcomed.
Thanks
Raj
May 15 2023 10:26 AM
Your tabs seems to be names differently on your screenshot. The "April:December!$F$2:$F$100000" part of the formula says to take the $F$2:$F$100000 cells from the tab April to the tab December, with every tabs in between. Maybe the problem comes from this?
The VSTACK formula stacks the data vertically. I'm not so sure how to help you more than this without a lookalike file.
Bye
May 16 2023 01:34 AM
Totally understand and thank you for your assistance so far. I've managed to remove some of the sensitive information from the attached Workbook, which is the one i wanted to use.
I would really appreciate if you could help by applying the same principals to this workbook.
Essentially, the months run from April to May and the analysis tab is call "Requester Summary".
Within the requester summary are tables which is where i need the data retrieved to. The data for each table is listed below,
I hope the above makes sense ? Its in essence very similar to what you provided previously. Thanking you in advance for your time, help and patience on this !
Thanks
Raj
May 16 2023 03:42 AM
I think there was some kind of format problem with your sheet. I thought the show formula mode was turned on but it wasn't it. With your original file, if the problem is still there, you can copy the format (format painter) of the attached file. Once again, double check those attached formulas.
Bye
Jun 21 2023 04:10 AM
@durendal Sorry to come back to you so late on this. I've been on leave !.
I've managed to overcome the formatting issue but i now have a Spill error :(. When i copy the formula from the spreadsheet you attached, the formula points to that file rather than the workbook i'm using. Even when i try and remove the previous file name, it doesn't work and gives me the spill error.
Attached is the spreadsheet i want to use and also shows the "Spill" error. I would be grateful if you could take a final look for me and help sort it out :(
Thanks Raj
Jun 21 2023 04:22 AM
Jun 21 2023 06:54 AM
@durendal Thank you, I've sorted that out now.
There is one more thing which is not working as expected which is the total number of authorised requests in cell F9. It seems to have counted for the first person but not for the other 2 underneath. I've tried to look at the formula logically and can't figure out why it's not working.
Apologies for bugging you like this. I promise this will be the last question !
Thanking you for your help so far and in advance.
Jun 22 2023 03:09 AM
Hello again. The formulas are the same in the cells F9:F19 than in F8. Drag down again from F8 and it will be fine. Check the formulas differencies before.
The one that works includes:
VSTACK(April:December!$I$2:$I$100000)
and the one that doesn't is
VSTACK('C:\Users':December!$I$2:$I$100000)
So something happend with the tabs names
Bye
Apr 20 2023 02:51 AM
Solution