Friday, November 11, 2011

yogi_Create A Table Of Filtered Entries Based On Criteria From Two Sheets

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user joshnekrep said:
I would like to create a worksheet that counts the number of entries in another sheet that match any of the entries in a third sheet.
For example:
Sheet 1:
USERNAME, DATA, DATA
Jim, whateve, whatever
Frank, whatever, whatever
Sally, whatever, whatever
Bill, whatever, whatever
Jim, whatever, whatever
Sheet 2:
USERNAME, TEAM NAME
Jim, Team 1
Frank, Team 2
Sally, Team 1
I would like to create a sheet that considers Sheet 1, and looks for all the instances of any username IF it is listed on Sheet 2 (ignoring those not listed) and then counts the number of occurrences for each Team Name (not each user).
The spreadsheet I'm working with polls Twitter for a specific hashtag, but I'd like to consider only those usernames that have been registered, and apply points to the team that the user is on.
Here is the spreadsheet I'm working on: https://docs.google.com/spreadsheet/ccc?key=0Ak_gj2-lxusydEI5Q012SXp6c055REVuTVlYc2NsNmc
I'd like the results to show up on the Team Counts sheet.
Hope this all makes sense and someone can help.
Thanks!
Update:
For clarification, in my above example the results I'm looking for would be as follows:
Team Counts:
Team 1, 3
Team 2, 1
Team 3, 0
etc...
------------------------------
following is one solution to the problem

No comments:

Post a Comment