Combining rows

[LOST]

I need help with an Excel formula I am trying to create. I have a
spreadsheet with 1514 rows of data, which represents 757 students twice (two
rows per student). Except for 3 columns (name, ID #, and School Code), the
two rows contain different data about each student. I would like to
integrate the two rows into one row for each student. Can you tell me a
quick way to do this?

 

[Max]

Try this easy play using a helper col & autofilter

Assume your table is in Sheet1, data from row2 down
Use an adjacent empty col to the right, say col K?
Put in K2: =MOD(ROWS($1:1)-1,2)
Copy K2 down to the last row of source data

Put a label in K1, apply autofilter on col K
Choose: 0 (this filters all the 1st instances for every student)
Copy the filtered rows, inclusive the top header row, then paste into a new
sheet

Then back to Sheet1, col K
Choose: 1 (this filters all the 2nd instances for every student)
Copy the desired range (exclude the 3 common cols) in the filtered rows,
then go paste in that new sheet: To align, select the cell in row2 just to
the right of the last col of data that you pasted earlier, right-click >
paste. Done.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik

 

[Sheeloo]

Copy your entire sheet from Sheet 1 to Sheet 2
On Sheet 1
Pick a col which is NOT blank on first row and blank on second...
Filter on that col for blanks
Select visible cells
Copy
Go to Sheet 2 Click on the first empty Col on Row 1
Click Paste
Again Filter on the col in Sheet 2 which you had used to filter
Select visible cells
Delete

 

[Bernie Deitrick]

Lost,

If you are ABSOLUTELY certain that you have two rows per student, do this:

Sort your data by the 3 columns so that each student's information is on
consecutive rows.
Then, in the first blank cell at the end of the first row of data, put the
formula (assuming that it is on row two, and your first column of data is
column D)

=D3

And copy that cell across for as many columns of data as you have.

Then select those formulas, and the blank row just below it, so that you
have a two-row selection. Copy, then paste down to match your data set. Copy
all the columns of formulas, and paste special values. Sort the entire data
table by one of you new columns (to put the blanks at the bottom) and then
delete all the rows in the new columns where the cells are blank. And then
you are done.

HTH,
Bernie
MS Excel MVP

 

[Herbert Seidenberg]

Using Excel 2007 with Tables
and structured references.
http://www.savefile.com/files/1865061