Forum Discussion
Help with a process / formula(s) to look up variable values and return the data
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
16 Replies
In the attached version, I have also added a pivot table as an alternative.
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- Thank you Hans, appreciate it.
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
