I have been given a spreadsheet into which survey data is dumped. It involves volunteers submitting their names and number of volunteer hours for specific categories. The same spreadsheet is used on a weekly basis. The goal is to total hours for each volunteer over the entire year. My questions are as follows: 1- if I sort columns based on name, can I also setup an accumulator for other columns in the same row- (in a different cell ). 2- Continuing, if the value of the name in the next row is the same, can I subtotal the accumulated values previously attained until the name changes? 3- Will the formula be able to do the same for the next, different name in the column?
I understand this is asking for a good deal of analysis. If there are any suggestions on how to achieve my goal with little or no manual changes, I would be most grateful.
Here is the spreadsheet. Although there are columns for first name and last name, they do not appear on the survey- which is unfortunate., since people do not always enter their name exactly the same. I left the time-date stamp columns in in case they provide any useful info.
I see, at that's the main issue no exact identification for the people. Excel has no idea that differently spelled names are belong the same person.
In attache file some quick mockup of how it could be.
First, it's better to collect source data in Excel table, it'll be easier to summarize and filter/sort data.
One solution could be to aggregate the hours using Pivot Table.
Another way to transform a bit using Power Query and return aggregated data from it. Here we may could some preliminary transformation like remove extra spaces, capitalize names and like, thus we will have more matches.
But in any case we will have not matched names for same persons. Here is only to check the result, based on it correct source data manually and refresh the summary after that.
Another way use kind if ID:s for people they have to fill. That could be just sequential 3 or 4 digits numbers.
I appreciate your information. I do not know why the survey was chosen as the means to enter data, but I believe the administration of the organization is intent on keeping it. Pivot Table and Power Query are beyond my working knowledge of Excel. Thank you for your time!