Combine data from multiple sheets with conditions

**luckyk** · 12-26-2012, 07:36 PM

Hello
I am new to this forum, as well as to VBA (Userform) programming.With a lot of help from this forum (and couple of others) I managed to create a Userform in Excel, which records patient data for every visit. What I would like to do is combine all data (from sheet: Page1,Page2, and Page3) into one sheet named 'Consolidated'. Unfortunately, as you will see from the attached file, my programming and understanding of VBA programming is pretty naive.
What I am after is, when I combine the data, from all sheets into the one, the data should be
1. Automatically Sorted by Patient Name
2. Group, results of the same patient from all sheets one below the other in seperate rows
3. Delete any other rows that may have a patient name and ID, but rest of the rows (New Mole,Clinically Suspicious......Benign Naevus) are blank. (This happens because of the way I have designed my userform. For first visit the details are entered in Page 1, then for second Visit Details are entered in Page2, and for third visit in Page 3. So when I enter the first visit details, the Patient Name and Patient Number are copied into Page 2 and Page 3 even though the other details remain blank. I have done it this way because, if say the Patient comes for visit the second time, the clinician may or may not be aware which visit this would be for the patient. So as soon as they enter patient number, it will give them a message that Patient already exists. So when they click Open Existing Patient Record, the form autopopulates with any notes from their previous visit into the respective page (i.e Page 1,Page 2 or Page 3). So I wonder if someone can help me
Hope I have tried to make sense.

**luckyk** · 12-27-2012, 10:03 AM

77 Views, No replies

**stnkynts** · 12-27-2012, 03:55 PM

NewDermascopyForm(1).xlsm

The reason no one has replied is probably due to the amount of work that is required to solve your problem. I have attached an updated copy of your workbook. Rather than trying to figure out what you had going on with the command buttons in your userform I just created a new button called test. I think I have it doing what you want it to. If it does then you can add it to where you need it.

**luckyk** · 12-27-2012, 08:27 PM

Thanks a lot stnkynts!

I did realise that, I may have not presented this well enough. Your reply gives me some chance to explain more. The solution given is what I was looking for. However, (please excuse my naivity) I am not sure if its deleting the unwanted row. There is also something another error I found out after I posted my initial comment. I will try and explain this and hopefully you can also advise me on this. Apologies again for the long explanation. But I thought this would make it more clear

In the userform, the situation is, that the patient would come for three seperate visits, depending on the interval the clinician has diagnosed for that patient. Let's consider Patient 'James' with ID: 123456,
1. Patient comes on 15/05/2012, the clinician would enter all details on Userform Page 1, and ask patient to come back after three months. Once the form on Userform Page 1 is complete, the clinician hits 'Submit'
2. This patient comes back on 14/08/2012 (three months from first visit). The clinician (maybe a new one this time) enters the Patient ID: 123456, on the Userform (not knowing this is their second visit) and the message pops up saying patient already existing. The clinician would then click on 'Open Existing Patient Record' and the 'Userform Page 1' would then populate with the information from previous visit.
3. This clinician would after their diagnoses enter the details of the second visit on 'Userform Page2' and set the interval for patients next visit (say 3 months again). The clincian will hit 'Submit' after her notes on Userform Page2.
4. The patient comes back on 15/11/2012 (three months from second visit) and the steps two and three are repeated. Again the clinician here may not be aware of the patient detail existence, and will enter patient ID on userform, which returns the 'patient already existing message'. After reviewing the previous visits when she clicks on 'Open Existing Patient Record', she will place details of this visit on Userform Page3.

Finally, the doctor can review all the details on the consolidated Excel sheet, where all the details from Excel sheets:Page 1, Page 2 and Page 3 are now in rows one below the other.

(It would be useful if you can try the steps 1 to 4 above. Randomly putting details and notes to identify each visit)

Unfortunately, what is also happening now is that you will notice
1. Submitting a 'new record' on any page also creates an unwanted record is the other respective sheet. (e.g Submitting Page 2 details also creates a record on excel sheet :Page 1, and Page 2)
2. After Submitting details on Userform:Page2 (Visit 2), a second record row is created on Excel SheetPage 2 with same patient ID, where the first record is unwanted as it does not have all the information. There is also an unwanted record created on Excel SheetPage 1 and Excel SheetPage 3, at the same time.

What I wanted is deleting these unwanted records which would have the patient name & patient ID under columns 1 &2, and a default date in column 11. Rest of the cells are blank. Is there a way to get rid of this?

Apologies again for this long explanation. I am only hoping you are not put off by this long explanation

**luckyk** · 12-27-2012, 09:23 PM

Originally Posted by stnkynts

Attachment 202592

The reason no one has replied is probably due to the amount of work that is required to solve your problem. I have attached an updated copy of your workbook. Rather than trying to figure out what you had going on with the command buttons in your userform I just created a new button called test. I think I have it doing what you want it to. If it does then you can add it to where you need it.

I just realised, if there is a way to modify your code to delete the unwanted records created on Excel SheetPage:1,2&3. Some records are created where the only information they have patient name & patient ID under columns 1 &2, and a default date in column 11, while the remaining cells in that row are blank. This happens on all sheets ( I know why it happens, that's why I want something that deletes this from all sheets or at least the 'Consolidated' sheet)

**luckyk** · 12-28-2012, 11:20 AM

Hello stnkynts
I may have resolved my problem. What I have done is instead of adding a new command button. I used an extra code for the 'Submit' button command. The 'Submit' command was programmed to store the data from the Userform (Page1, Page2 & Page 3) to their respective excel sheet. What I did different now was create an If Else statement and add new 'Set WS = Sheets ("Consolidated") , giving some conditions around DtPicker values.(e.g Set ws = Sheets("Consolidated")
If Me.DTPickerV1.Value > 0 Then
ws.Cells(iRow, 2).Value = Me.TxtPtNmbr.Value
ws.Cells(iRow, 3).Value = Me.CmBXNewMole.Value
ws.Cells(iRow, 4).Value = Me.CmBxClinSusp.Value...... and so on)
So now what it does is,
1. Does not write the unwanted data
2. Simulatneously also submit data from each multi-page to the consolidated sheet

Let me know what you think