Excel spreadsheet help

[jayploc]

Hello,

I have a spreadsheet that is kind of messy despite many "paste special" attempts I tried. The first column lists names but last and first names are in different columns and the grade info in following columns is in a row that consists of the first to rows (last, first), merged. (Sorry that's the best I can describe).

I have attached the workbook (only 1 sheet), and its as cleaned-up as I could get it. If someone could help me I would greatly appreciate it...

I want the rows to be in the format:

STUDENT | Resp | Comp | Task | ...
last, first | 43 | 52 | 32 | ... (one row)
.... etc...



Hopefully I have explained this well enough.

Thanks,
Jay

[Bob Phillips]

You forgot to say what the question was.

[jayploc]

Sorry....

I want the rows to be in the format:

STUDENT | Resp | Comp | Task | ...
last, first | 43 | 52 | 32 | ... (one row)
.... etc...


As of now, the last and first name are in different rows. My question is can you help me with a code/macro that will let me condense the name into one cell in the format (last,first)?

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim LastRow As Long
Dim i As Long, j As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -2

.Cells(i - 1, "A").Value = .Cells(i - 1, "A").Value & " " & .Cells(i, "A").Value
For j = 2 To 11: .Cells(i - 1, j).UnMerge: Next j
.Cells(i - 1, "A").WrapText = False
.Rows(i).Delete
Next i

.Columns(1).AutoFit
End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub
[/vba]

[jayploc]

Awesome. thank you once again XLD