Conditional completion of blank cells

[bradfordicu]

Hello

I am not sure how to ask this question.

Using the example in the attached file. I want to go from the example in BICU SAMPLE to BICU SAMPLE-1

I am working with a large dataset from a pathology database which has been exported to CSV then imported into Excel. There is no other way to format this data and I have to work with what I have.

It is a microbiology database. Some patients will grow one organism from a particular site. I have called this Type in the example. But some patients with grow more than one organism, for example there are three Types in some of the example patients in the attached files.

The database starts by putting each patient on a new row, then puts each type on a new row if there is more than one type per patient but only provides the demographic data once. So if a patient grows three organisms they will have a three row entry in the database, one for each organism, but only the first row will contain the demographic data.

I need to copy the demographic data to each of the remaining rows but keep it identical to the original entry for that patient. So, for example, when I have tried to do this by dragging the bottom right corner it adds one to the unique number (which cannot change) and increases the date (again which cannot change).

Usefully, every time there is a patient with more than one "Type" there is a blank row before the next patient. Most patients have grown only one "type" but some have grown two, three, four, five and even six organisms.

I need an automated way to complete the missing demographic data. There is over 6000 results so it is impractical to do it by hand.

Any suggestions would be very gratefully received.

With my sincere thanks

Ronan

[abousetta]

Hi Ronan,

This should not be as difficult as you think, but just before I post a proposed response I do have a question: Why is there an empty row between George and John, but not Mary and John? Are you adding these empty rows yourself?

abousetta

[bradfordicu]

Thank you.

Where there is just one row per patient there is no blank row before the next patient. Where there is more than one row the dataset, for some reason, includes a blank row before the next patient.

[abousetta]

Hi,

This should do it.

Please Login or Register  to view this content.

Did you also want to delete those other rows that creep in the middle of the worksheet?

abousetta

[bradfordicu]

Yes, that would help, or at least make the data look neater.

Sorry, I am a bit of a newbie with this. Can I just paste that code into Visual Basic Editor in Excel for Mac 2011?

Thank you

[abousetta]

Here's an updated version of the code:

Please Login or Register  to view this content.

It should delete the first row above the headers and the empty rows in between patients.

About the mac issue, I am not sure as I don't use macs. I'm sure if you google it, the answer will not be hard to find.

Let me know if you have any additional questions or concerns.

abousetta

[bradfordicu]

Thank you

I will have a play with it.