Forum Discussion
Multiple conditions involving other cells- complicated
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.
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.
In general yes, it looks like it could be SUMIFS or even better Pivot Table. To be more concrete better if you submit small sample file removing from it all sensitive information.
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.
