+ Reply to Thread
Results 1 to 4 of 4

VBA in Excel; copying data in many rows to create one row per person; using rel. reference

  1. #1
    Registered User
    Join Date
    06-30-2013
    Location
    Philadelphia, PA, USA
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    2

    VBA in Excel; copying data in many rows to create one row per person; using rel. reference

    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.
    Attached Files Attached Files
    Last edited by ginger2k; 06-30-2013 at 10:31 PM. Reason: specifying where to find Consolidate_Patient_appts subroutine

  2. #2
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: VBA in Excel; copying data in many rows to create one row per person; using rel. refer

    Hi ginger2k

    Welcome to the forum.

    I looked at your macro and found it very "busy" I thought of trying to modify your code and then realised (as so often in life ) that it was easier to start again.

    I attach my offering - see if it fits with your requirements. You should note that if you try to put more than 6 rows after the first row for any EMPI (or MRN as it becomes) the system will unceremoniously kick you out. There has to be a limit, but it could be much higher if you need it.

    Let me know how you get on.

    Regards
    Alastair
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    06-30-2013
    Location
    Philadelphia, PA, USA
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    2

    Smile Re: VBA in Excel; copying data in many rows to create one row per person; using rel. refer

    Alastair: Thank you so much! I think this is what I need. And you were very kind to say "busy". "Mess" would have been more like it! Your code will help me learn so I do appreciate it. Thank you again for taking the time to respond.

    All the best,
    Valerie

  4. #4
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: VBA in Excel; copying data in many rows to create one row per person; using rel. refer

    Hi Valerie

    Glad you found it useful. Feel free to PM me with any further queries

    Regards
    Alastair

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1