Multiple rows of data that needs merging

I think I'm going to need some VBA or a Macro for this query.

I have a file that exports data to a CSV, depending upon the data, either two rows could be returned for one record (this tends to be the norm) but we could also have data returned that has upto 5 or more rows returned.

I'll try to demonstrate what I need.
See attached image as my attempt to create a spreadsheet at bottom of thread failed miserably
03-01-2013 16-35-39.jpg

Is somebody able to write me a script so I can work adapt it, there are more columns than this, but hopefully this explains my dilemma.

This is what I tried to recreate i the thread but the spaces were removed
ID Name Address Staff No Department Course ID Course Name Pass Mark
1 Mr A 12 High Street 12345 Accounts 1234 Excel C
1 Mr A 12 High Street 12345 Accounts 1299 Access B
2 Mr B 22 Low Close 34567 IT 1234 Excel A
3 Mr C 96 Middle Road 86745 Accounts 1234 Excel E
3 Mr C 96 Middle Road 86745 Accounts 1299 Access B
3 Mr C 96 Middle Road 86745 Accounts 1494 Powerpoint A

This is what I have to work with and sometimes the data could be 1000+ rows.

ID Name Address Staff No Department Course ID Course Name Pass Mark
1 Mr A 12 High Street 12345 Accounts
1234 Excel C
1299 Access B
2 Mr B 22 Low Close 34567 IT
1234 Excel A
3 Mr C 96 Middle Road 86745 Accounts
1234 Excel E
1299 Access B
1494 Powerpoint A

MyDilemma.xls

Thank you for that, I have attached a file to help with any replies.

regards

Mike, code is awesome, made some adjustments to take in additional columns, I found one tweak that will probably be easy for you if you knocked this up quickly.

On the example I gave, all the data was present, some of my fields my have blank data. The code then shifts data up to the incorrect cell.

If you take out Accounts from Mr A row, you'll see what I mean.

For full data its smashing and if it cannot be achieved I'm so happy anyway, I'll find and replace blank cells with something and remove them again after (maybe a macro! haha)

thanks again, and if you can tweak it that would be mint!

MyDilemma (2).xls

Sure, see attached, you'll notice that Mr A has not department associated with him, if you run your code against the attached you'll see what happens.

My longer spreadsheet has gaps in data as employees are not always the best at filling in forms.

thanks

different option, press Run button or run code test

Thanks Mike, on my larger spreadsheet it does not delete the blank rows, everything else works upto that point but the delete rows are not deleting.

I just read something about that cells might not truly be blank if data is cut and pasted, could that be it?

Sorted, thanks for all your help Mike, this is going to save me lots of time. After testing out lots of scripts that didn't work, this one did the trick http://pastebin.com/i1HwekA1

Probably a bit longwinded, but nevertheless, it compliments yours magnificently and i really appreciate you taking the time to help out.

regards