Making two rows into one in Excel

June 22, 2007 2:21 PM

AnotherExcelQuestion: Data that is in two rows needs to be in 1 row. I know it can be done manually, but I have data for 998 individuals to deal with.

Here's the data I have in Excel:

Rater Rating Student
Mr. B 6.00 Amy
Mr. K 5.50 Amy
Ms. M 5.75 Todd
Mr. R 5.25 Todd
Ms L 6.00 Jim
Mr. B 6.00 Jim

I need to end up with a spread sheet that looks like this:

Amy Mr. B 6.00 Mr. K 5.50 (0.50) (the diff between the two ratings)
Todd Ms. M 5.75 Mr. R 5.25 (0.50)
Jim Ms. L 6.00 Mr. B 6.00 (0.00)

I can figure out how to calculate the difference between the scores, but I can't figure out how to get the data that is currently in two rows for each person, into a single row for each person.

I have 998 people's ratings to calculate and I also have several other columns of data I need to preserve, so it would be really labor intensive to simply choose the second row and drag it up to the end of the first row.

Another glitch. about 20 of the 998 have only one rating, so I can't simply do any kind of "every other one" calculation across the board.

Any ideas? I'm stumped.

Thanks!

posted by SheIsMighty to Computers & Internet (10 answers total)

I can think of a couple of ways to approach this, but none that I can succinctly articulate here. My email's in my profile if you want to discuss further.
posted by treepour at 2:36 PM on June 22, 2007

You probably want to use a pivot table. Put the name of the student in the left hand "Row" area. Put the teachers in the top "Column" area. Put the ratings in the data area
posted by willnot at 2:49 PM on June 22, 2007

Best answer: This is how I would do it (so it may not be the most efficient way, but it works). For the first row (cell D1, assuming the data starts in row 1), sort out the data formatting by hand. Then in D2 put the following and fill down to the end of the data:

=if(C2=C1,"",if(C2=C3,concatenate(C2," ",A1," ",B1," ",A2," ",B2," (",B1-B2,")"),concatenate(C2," ",A2," ",B2)))

This will result in column D alternating between blank cells and cells with the data in the format:
student rater1 rating1 rater2 rating2 (difference)
...except when there is a student who only has one result, in which case it will have:
student rater rating

Then to trim off the data you don't want:
- Copy column D and Paste Special as Values.
- Autofilter column D, select Blanks, select all rows and delete.
- Remove Autofilter, Delete columns A, B, C.
- Done!

note: pivot tables might work too, but I generally don't bother with them as it makes the formatted data difficult to copy & paste somewhere else.
posted by EndsOfInvention at 2:59 PM on June 22, 2007

Select and copy data in the pivot table, Go where you want it. Edit > Paste Special: Values.

You now have the information from the pivot table without any references to the pivot table.
posted by willnot at 3:37 PM on June 22, 2007

Seconding a pivot table, it's definitely the easy way to do it. It can be kind of intimidating at first, but by playing around with it for a bit it becomes pretty self-explanatory.
posted by gemmy at 4:20 PM on June 22, 2007

I thought the pivot table would be good, but looking at the data it seems that there are a bunch of different people rating. This would end up having a lot of columns instead of just the 6 that SheIs posted.

Ends of has a solution of sorts, but I don't think we want it concatenated into one cell. You could use those if statements to get each cell, but it seems like there has to be an easier way.

If you want, the if formulas would be as follows:

column D: =IF($A1=$A2,"",IF($A2=$A3,B2,B2))
column E: =IF($A1=$A2,"",IF($A2=$A3,C2,C2))
column F: =IF($A1=$A2,"",IF($A2=$A3,B3,""))
column G: =IF($A1=$A2,"",IF($A2=$A3,C3,""))
column H: =IF($A1=$A2,"",IF($A2=$A3,ABS(E2-G2),""))

Copy, paste as values, delete columns b and c, sort by column b, then delete the rows that have no info.
posted by hue at 7:21 PM on June 22, 2007

Try the addin ASAP-Utilities It has a lot of useful stuff that can solve a lot of these type of problems.
posted by mss at 7:24 PM on June 22, 2007

oops.

column D: =IF($A1=$A2,"",B2)
column E: =IF($A1=$A2,"",C2)
posted by hue at 7:28 PM on June 22, 2007

Response by poster: I modified the answer that EndsofInvention gave and it worked with a bit of tweaking! Thanks!
posted by SheIsMighty at 9:14 PM on June 22, 2007

willnot wrote "Select and copy data in the pivot table, Go where you want it. Edit > Paste Special: Values."

Huh, not sure how I didn't think to try that when I was trying and failing to copy some pivot table data the other day. D'oh.
posted by EndsOfInvention at 1:12 PM on June 24, 2007