Manipulate Survey Data

[gtg689a]

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!

[DKenny]

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...

[gtg689a]

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.

[DKenny]

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.

[DKenny]

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

[gtg689a]

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

[gtg689a]

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