Need help fast!! Creating a data table

[Flamikey]

VP of HR just walked in and needs a report flipped. The data is an
excel file sent to us from an outsourced company that handles employee
surveys. I can't get the outsourcer to send the data in any other
format than that below.

Column A is a session ID
Column B is survey time/date (all in one value)
Column C through AU are survey questions

I would love to do a pivot to analyze this data, the problem is that
each row is really a summary of each individual survey, i.e, the person
in col A, the time in Col B, and their score for each answer in col
C-AT. what I need to do is create a unique record for each Person,
i.e. flip the table so that I have 4 columns (session ID, time,
Question #, and score)

Any ideas on how to do this fast?

Thanks!!

 

[Mark Graesser]

Flamikey,
Have you tried to use Copy and then Paste_Special>Transpose? When transposing, you can't paste over the existing data, so just paste it below the current data and then delete the original set.

One thing I don't understand. It sound like you already have each row as a unique record for each ID number. You wrote that:

"Column A is a session ID
Column B is survey time/date (all in one value)
Column C through AU are survey questions"

and then:

"so that I have 4 columns (session ID, time, Question #, and score)"

Isn't that what you already have?

Good Luck,
Mark Graesser
(e-mail address removed)


----- Flamikey > wrote: -----

VP of HR just walked in and needs a report flipped. The data is an
excel file sent to us from an outsourced company that handles employee
surveys. I can't get the outsourcer to send the data in any other
format than that below.

Column A is a session ID
Column B is survey time/date (all in one value)
Column C through AU are survey questions

I would love to do a pivot to analyze this data, the problem is that
each row is really a summary of each individual survey, i.e, the person
in col A, the time in Col B, and their score for each answer in col
C-AT. what I need to do is create a unique record for each Person,
i.e. flip the table so that I have 4 columns (session ID, time,
Question #, and score)

Any ideas on how to do this fast?

Thanks!!

 

[Flamikey]

Transpose won't work. I want a uniqur record for each row, i.e. each
row will give me the session ID, time, question, and score. Currently
each row has session ID, time, and each of the next 45 columns has the
score of the question that is the header of the column. Instead of
having a summary by session ID, I want to create a true database.
right now I have 75 rows by 47 colums (Each row represents a person,
col one is the person, col2 is the time, col 3-47 are scores).
Basically I need to "Transpose" this 75RX47C table into a 4CX3375R
database

 

[Flamikey]

I had to crank this out so I did it in a manual way using VLOOKUPS an
copying an array 45 times to build the database. Still curious to th
best approac

 

[Mark Graesser]

I assumed the table didn't have a header row based on the array size you specified.

1) Insert column between B and C for your question labels.

2) In A76 enter =A1

3) In B76 enter =B1

4) in D76 enter =INDIRECT("R"&ROW()-(75*(ROUNDUP(ROW()/75,0)-1))&"C"&ROUNDUP(ROW()/75,0)+4,0)

5) Copy all formulas down to row 3375

The indirect function returns a cell references in R1C1 style. It probably took me as long to come up with this formula as it did for you to update the sheet manually.

Glad to here you got what you needed.

Regards,
Mark Graesser
(e-mail address removed)

----- Flamikey > wrote: -----

I had to crank this out so I did it in a manual way using VLOOKUPS and
copying an array 45 times to build the database. Still curious to the
best approach

 

[Flamikey]

Mark,
This is the worst extract Ive ever seen. You would think a company
that outsources survey reports would know better! Anyhow, the columns
have headers. The headers are:

Col A - Session ID (Person)
Col B - Time
Col C - Question 1
Col D - Question 2
Col E - Question 3
etc out to question 45

To make matters worse, Question 1 is asking the role of the person
(Admin, Acg, Sales,etc). This should be a primary key!!! Also, the
surve allows answers to the questions to be both numerical and text
(which makes it difficult to create calculated fields such as AVG in a
pivot table). For instance question 1 which asks for Role is a text
response, while question 2 may ask the participant to rate 1-4 their
reponse to a statement. Ugh!!! I should have pulled all of this into
Access, I think it would have been a lot easier!!

 

[Flamikey]

Very clever formula. I have filed this away for future use. Thanks.