Results 1 to 7 of 7

Thread: Manipulate Survey Data

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Mar 2006
    Posts
    74

    Manipulate Survey Data

    I just need someone to get me kicked off on my next project.

    I have an input sheet with 25 column headers. The 1st one gives the person's name, the second says what team you were on, and the rest are answers to a survey. The survey tells you to rank yourself, and then rank the different members of your team(The names can be in any order).


    So an input could look like this:

    Dave Brown Team A 1 2 3 4 5 Molly Jones 3 4 2 1 4 Tom Snyder 3 4 5 2 2 5

    I have an output sheet that needs to list the rankings you gave yourself, and what everyone else in your team gave you.

    Can someone point me in the right direction?

    Thanks!

  2. #2
    Frenzied Member DKenny's Avatar
    Join Date
    Sep 2005
    Location
    on the good ship oblivion..
    Posts
    1,171

    Re: Manipulate Survey Data

    Southeast....

    1/ Create a table that is 11 cols wide (Name, 5 SelfRank, 5 OtherRank)
    2/ Add a record for each name in Inputsheet Col1
    3/ Rows 2-6 (SelfRank) are just straight lookups. That is assuming each person only has one self-assessment, otherwise use SUMIF.
    4/ Rows 7-11 (OtherRank) needs multiple SUMIF's (one for each set of possible other ranking column (ie. My Name in Col F, Col K, Col P etc) with the results of these added together. This then needs to be divided by the sum of a series of COUNTIF's (to see how many times I was ranked).

    Step 4 is the only tricky part, but you may be able to build a function to achieve it...
    Declan

    Don't forget to mark your Thread as resolved.
    Take a moment to rate posts that you think are helpful

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Mar 2006
    Posts
    74

    Re: Manipulate Survey Data

    Part 4 is tricky indeed.

    The problem is getting something that will say, ok, my team name is in column 2. now go through all the records, find any row with Team "Myteam in it", then find my name on that row, and return the next 7 cells.

  4. #4
    Frenzied Member DKenny's Avatar
    Join Date
    Sep 2005
    Location
    on the good ship oblivion..
    Posts
    1,171

    Re: Manipulate Survey Data

    I don't think you need to worry about the team name at all. You can just do a SUMIF, based on the employee name for each of the "Other" column sets and divide that by a COUNTIF for each of those sets.
    Declan

    Don't forget to mark your Thread as resolved.
    Take a moment to rate posts that you think are helpful

  5. #5
    Frenzied Member DKenny's Avatar
    Join Date
    Sep 2005
    Location
    on the good ship oblivion..
    Posts
    1,171

    Re: Manipulate Survey Data

    Here's an example of what I'm talking about.
    Attached Files Attached Files
    Declan

    Don't forget to mark your Thread as resolved.
    Take a moment to rate posts that you think are helpful

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Mar 2006
    Posts
    74

    Re: Manipulate Survey Data

    Two Issues:

    1. The number of people in your team can change
    2. The output shouuld show your team mates individual rankings of you, and a sum, not just the sum itself.

    I didn't think it could be done unless I worked with some macro. I don;t know, this is giving me a headache and I am going home for the day!! HAH

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Mar 2006
    Posts
    74

    Re: Manipulate Survey Data

    Is there a way to say

    ...loop through all the rows, and anytime you see this name, pick up the next 7 cells?

    I am thinking that if you can, there might be a way to do this

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width