Advice/best practice needed maybe some complex VB aswell

[ArcticWolf]

Hi,

I have a table which lists employee name, their exam name and grade (table
1).

Table 1
Row 1 Holmer Simpson,Exam 1,Pass
Row 2 Holmer Simpson,Exam 2,Pass
Row 3 Holmer Simpson,Exam 3,Fail
Row 4 Holmer Simpson,Exam 4,Distinction
Row 5 Marge Simpson,Exam1,Pass
Row 6 Marge Simpson,Exam 2,Pass
Row 7 Bart Simpson,Exam 1,Distinction
Row 8 Bart Simpson,Exam 2,Pass
Row 9 Bart Simpson,Exam 3,Pass

I need to get all the data for each person onto one row (table 2).

Table 2
Row 1 Holmer Simpson,Exam 1,Pass,Exam 2,Pass,Exam 3,Fail,Exam 4,Distinction
Row 2 Marge Simpson,Exam1,Pass,Exam 2,Pass,,,,
Row 3 Bart Simpson,Exam 1,Distinction,Exam 2,Pass,Exam 3,Pass,,

I started copying and pasting the 2nd, 3rd & 4th exam etc into the 1st row
of each employee (using the next empty column within the row) and I then
deleted the empty rows. My original table contains over 5000 rows so it's
going to take an age plus any human errors in the copy paste.

Is there a way in which I can get to table 2 maybe with some wizzy VB
please, or maybe there is another solution? I tried putting it into a pivot
table but I can't get exam names to list properly.

Any advice would be appreciated.

TIA,

AW

 

[Bob Phillips]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim LastCol As Long

With ActiveSheet

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

If .Cells(i, "A").Value = .Cells(i - 1, "A").Value Then

LastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
.Cells(i, "B").Resize(, LastCol - 1).Copy .Cells(i - 1, "D")
.Rows(i).Delete
End If
Next i
End With

End Sub

 

[ArcticWolf]

Thank you Bob this is absolutely brilliant and exactly what I was after. You
have saved me so much time and I would therefore like to make a contribution
to your favorite charity. Please let me know what it is and I will send them
a small donation.

Thank you very much for this, I am very very grateful.

ATB,

AW

 

[Bob Phillips]

Excellent, I am glad it helped, and many thanks for that generous riposte. I
have a lot of time for Médecins Sans Frontières, so if you want to make a
donation there I am sure that they will make good use of it.

As an aside, if you have a lot of data, and I presume that you do if it
saves lots of time, I should have turned Screenupdating off, so as to avoid
the screen flicker.

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim LastCol As Long

Application.ScreenUpdating = False

With ActiveSheet

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

If .Cells(i, "A").Value = .Cells(i - 1, "A").Value Then

LastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
.Cells(i, "B").Resize(, LastCol - 1).Copy .Cells(i - 1, "D")
.Rows(i).Delete
End If
Next i
End With

Application.ScreenUpdating = True

End Sub


Regards

Bob

 

[ArcticWolf]

No probem, consider it done

I have also been able to mod it slightly to increase the number of columns I
use from the original table.

I have address details of where the course was run, dates and times etc but
doing the copy paste with that extra data was going to be too much (I was
flgging under the strain of 5000+ rows with only 3 columns to do lol!).

I changed the "D" to "AF" and it merged ALL the records perfectly.
Well, when I say perfect...I did get an error first time round "copy paste
area not same size..." I soon realised that 2003 didn't have enough columns
to fit it all on lol. I have a few people who have completed every course
under the sun so their records are in excess 20 rows, added to that the extra
course details and '03 was having none of it!
Put it on my LT which has 2007 and POW - completed in less than 30 seconds.
Got to have saved me at least a whole weeks worth of (labourious) time, and
it's given me ideas for other stuff as well which is gonna be a sinch now...

Many thanks again for your help )

ATB,

AW