I'm new, but I did read the forum rules and I hope I'm following them. I have a spreadsheet which can contain one or more rows per person/id. Each person/ID can have 1 row/appt or they can have multiple rows/appts. I'm trying to consolidate many rows in Sheet1 into one in Sheet2 . Each row in Sheet1 has name, address and then a medical specialty, doctor name and appt date. If there is one row for a person in Sheet1, there should be one row for that person in Sheet2. If there are 3 rows for that person in Sheet1, there should still be 1 row in Sheet2 (but that row would have additional columns to show 3 different appts for the person.
In the following, 3 rows would be turned into 1 row with 3 Specialties/Doctors/Dates:
FirstName>MiddleName>LastName>Address1>City>State>Zip>Specialty1>Doctor1>Date1>Specialty2>Doctor2>Date2>Specialty3>Doctor3>Date3
I'm doing this by copying info from Sheet1 into Sheet2.
I have 2 loops; one that copies all the name, address stuff and one to check to see if each Row in Sheet1 has the same ID or a new one. If the same ID, then I want to add another Specialty>Doctor>Date onto the same row in Sheet2. If it is a new ID then I want to start with a new row in Sheet2.
I'm attaching a spreadsheet with a module that contains a subroutine called Consolidate_Patient_Appts. I think in my attachment it shows up twice, but please look at the one that's in Module1.
I am sure, being new, that I am not doing this as efficiently as possible but I did my best and so far it seems to be working except when I find that I have a new ID and I'm ready to add a new row to Sheet2 how can I specify that I want to go back to the first column on the current row? I am using relative references and I might have 1 specialty/doctor/date or 6 of them so I don't know what to do.
I am happy to answer any questions, and really appreciate any help you can give me. I suspect this is probably really simple and I am just missing something obvious because this is my first try. Thanks again.
Bookmarks