Tuesday, July 3, 2012

yogi_Display Each Employee And Hours Worked From A Table Consisting Of Multiple Names And Their Hours

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #607   Jul 3, 2012     www.energyefficientbuild.com.


user benomatic42 said:
UNIQUE failure, plus conditional sums
Hi hi,
I am trying to work a fairly simple spreadsheet into simple, per-person counting.  I have data like this:
Basically, 2 columns.  1st column is of comma separated strings, 2nd is a count.
[ Ben           | 3 ]
[ Ben, Tim      | 5 ]
[ Tim, Fred     | 2 ]
[ FRED, GEORGE  | 7 ]
What I seek is a summary of the count (hours), per unique person.  It could (but need not) look like:
[ ben    | 8 ]
[ fred   | 9 ]
[ george | 7 ]
[ tim    | 7 ]
My first function, to separate and uniquify the names, is my first failure:
  =UNIQUE(SPLIT(LOWER(JOIN(" ";F2:F20)); " ,;"))
The unique seems to do nothing. I also tried to use SORT on it, but that also failed to change the result. Suggestions?
Thanks,
ben
-------------------------------------------------------------------------------------------------------
following is a solution to the problem