Home

Multiple conditions involving other cells- complicated

%3CLINGO-SUB%20id%3D%22lingo-sub-861093%22%20slang%3D%22en-US%22%3EMultiple%20conditions%20involving%20other%20cells-%20complicated%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-861093%22%20slang%3D%22en-US%22%3EI%20have%20been%20given%20a%20spreadsheet%20into%20which%20survey%20data%20is%20dumped.%20It%20involves%20volunteers%20submitting%20their%20names%20and%20number%20of%20volunteer%20hours%20for%20specific%20categories.%20The%20same%20spreadsheet%20is%20used%20on%20a%20weekly%20basis.%20The%20goal%20is%20to%20total%20hours%20for%20each%20volunteer%20over%20the%20entire%20year.%20My%20questions%20are%20as%20follows%3A%3CBR%20%2F%3E1-%20if%20I%20sort%20columns%20based%20on%20name%2C%20can%20I%20also%20setup%20an%20accumulator%20for%20other%20columns%20in%20the%20same%20row-%20(in%20a%20different%20cell%20).%3CBR%20%2F%3E2-%20Continuing%2C%20if%20the%20value%20of%20the%20name%20in%20the%20next%20row%20is%20the%20same%2C%20can%20I%20subtotal%20the%20accumulated%20values%20previously%20attained%20until%20the%20name%20changes%3F%3CBR%20%2F%3E3-%20Will%20the%20formula%20be%20able%20to%20do%20the%20same%20for%20the%20next%2C%20different%20name%20in%20the%20column%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI%20understand%20this%20is%20asking%20for%20a%20good%20deal%20of%20analysis.%20If%20there%20are%20any%20suggestions%20on%20how%20to%20achieve%20my%20goal%20with%20little%20or%20no%20manual%20changes%2C%20I%20would%20be%20most%20grateful.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-861093%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-861339%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20conditions%20involving%20other%20cells-%20complicated%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-861339%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F411059%22%20target%3D%22_blank%22%3E%40Joegua1949%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20yes%2C%20it%20looks%20like%20it%20could%20be%20SUMIFS%20or%20even%20better%20Pivot%20Table.%20To%20be%20more%20concrete%20better%20if%20you%20submit%20small%20sample%20file%20removing%20from%20it%20all%20sensitive%20information.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-861813%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20conditions%20involving%20other%20cells-%20complicated%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-861813%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20spreadsheet.%26nbsp%3B%20Although%20there%20are%20columns%20for%20first%20name%20and%20last%20name%2C%20they%20do%20not%20appear%20on%20the%20survey-%20which%20is%20unfortunate.%2C%20since%20people%20do%20not%20always%20enter%20their%20name%20exactly%20the%20same.%26nbsp%3B%20I%20left%20the%20time-date%20stamp%20columns%20in%20in%20case%20they%20provide%20any%20useful%20info.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-862156%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20conditions%20involving%20other%20cells-%20complicated%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-862156%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F411059%22%20target%3D%22_blank%22%3E%40Joegua1949%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20see%2C%20at%20that's%20the%20main%20issue%20no%20exact%20identification%20for%20the%20people.%20Excel%20has%20no%20idea%20that%20differently%20spelled%20names%20are%20belong%20the%20same%20person.%3C%2FP%3E%0A%3CP%3EIn%20attache%20file%20some%20quick%20mockup%20of%20how%20it%20could%20be.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFirst%2C%20it's%20better%20to%20collect%20source%20data%20in%20Excel%20table%2C%20it'll%20be%20easier%20to%20summarize%20and%20filter%2Fsort%20data.%3C%2FP%3E%0A%3CP%3EOne%20solution%20could%20be%20to%20aggregate%20the%20hours%20using%20Pivot%20Table.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20way%20to%20transform%20a%20bit%20using%20Power%20Query%20and%20return%20aggregated%20data%20from%20it.%20Here%20we%20may%20could%20some%20preliminary%20transformation%20like%20remove%20extra%20spaces%2C%20capitalize%20names%20and%20like%2C%20thus%20we%20will%20have%20more%20matches.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBut%20in%20any%20case%20we%20will%20have%20not%20matched%20names%20for%20same%20persons.%20Here%20is%20only%20to%20check%20the%20result%2C%20based%20on%20it%20correct%20source%20data%20manually%20and%20refresh%20the%20summary%20after%20that.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20way%20use%20kind%20if%20ID%3As%20for%20people%20they%20have%20to%20fill.%20That%20could%20be%20just%20sequential%203%20or%204%20digits%20numbers.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-864112%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20conditions%20involving%20other%20cells-%20complicated%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-864112%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20appreciate%20your%20information.%26nbsp%3B%20I%20do%20not%20know%20why%20the%20survey%20was%20chosen%20as%20the%20means%20to%20enter%20data%2C%20but%20I%20believe%20the%20administration%20of%20the%20organization%20is%20intent%20on%20keeping%20it.%20Pivot%20Table%20and%20Power%20Query%20are%20beyond%20my%20working%20knowledge%20of%20Excel.%26nbsp%3B%20Thank%20you%20for%20your%20time!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Joegua1949
New Contributor
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.
4 Replies

@Joegua1949 

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.

@Sergei Baklan 

 

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.  

@Joegua1949 

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.

@Sergei Baklan 

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!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies