Sunday, May 6, 2012

yogi_Extract First Three Unique Names For Specified Color Combinations From A Given Table Of Values

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #532    May 6, 2012

user Mimicre said: 
Tiered Form Response Sorting Hi, I need to sort some form responses in a particular way, and I'm entirely at a loss--I've tried everything I can think of! I have collected form responses as shown in the first page of this example: What I want to do is generate a list of names based on prioritized options chosen, with an upper limit. So in the example, I have set the upper limit to 3--that is, only three people can be listed under a particular color combination. If a color combination is full, I want the person to be listed under the second color combination; if that's full, the third. Unfortunately, the color combination names are not in a standard order; Blue-Red should be the same as Red-Blue. I've hand-sorted the names in the correct order on the second sheet (Desired Output). However, I have about a thousand responses at this point on the original form, which obviously I can't deal with through hand-sorting. Also--though this would be a bonus and is not a priority--I would like the side selections to be displayed as shown on the Desired Output sheet. If that's not possible, I am totally okay with it. Are there any formulas that can make sorting easier for me? Any help at all would be highly appreciated! Best, 
following is a solution to the problem for the 3 names for color combinations only -- I have not addressed the question of side selections.