Yogi Anand, D.Eng, P.E. Google Spreadsheet Post #532 May 6, 2012 www.energyefficientbuild.com.
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: https://docs.google.com/spreadsheet/ccc?key=0Ah7NmG121zTFdHRQNHo5TWkyekRUaS16bkcyYkRaUWc 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,
Mimicre
-------------------------------------------------------------------------------------
following is a solution to the problem for the 3 names for color combinations only -- I have not addressed the question of side selections.
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: https://docs.google.com/spreadsheet/ccc?key=0Ah7NmG121zTFdHRQNHo5TWkyekRUaS16bkcyYkRaUWc 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,
Mimicre
-------------------------------------------------------------------------------------
following is a solution to the problem for the 3 names for color combinations only -- I have not addressed the question of side selections.
No comments:
Post a Comment