Move data to new columns when cell in Column A is blank

Dear all,

(FYI, I am currently using Excel 2003)

I have been sent a rather rubbish spreadsheet and I’m not sure how to sort/filter without losing data. Please can someone help?

A slimmed-down example of the sort of data I have to deal with is attached.

The actual file I have been sent consists of nearly 15,000 rows (although those rows only relate to approx 5,000 individuals).

A little bit of background:
The data are for junior doctors in training. Drs can have multiple accreditation (meaning that they can train in more than one specialty).

What has happened is that for each Dr with multiple accreditation, their name (column A), code (column B) and main specialty (column C) are on one row, whereas their subsequent specialties appear on the row(s) below their record.

As you can see the data are not uniform – some Drs will have 1 specialty, others 2, still others 3, etc…

If I try to sort it I will jumble the data, so I want to move the additional specialty data into new columns (called Specialty_2, Specialty_3, on my example sheet).

What I need is some code that tells my sheet, for example:

“If there cell A3 is blank, then move data from cell C3 into cell D2; and if there is still no data in the next record in column A (in this case, cell A4) then move data from cell C4 into E2, etc...”

Please can someone help?

Thanks and best wishes,

Chris