Merging Duplicate Rows and Keeping Original Data

[vinnie179]

I know this question has been asked and addressed in different ways, but I can't quite find what I'm looking for. To start off, I KNOW NOTHING ABOUT CODING. I'm hoping someone would be so kind as to help shave off hours at my work by showing me specific code that I can copy and paste into a macro.

The situation: I need to merge rows with duplicate values in column A (Patient Name being the most important one), with columns B, C, & D usually having different isolated values as well. Columns E, F, G, H, & I are date columns, but the data is always going to be the number 1, meaning a patient was seen once that day (if they were seen two times that day for different reasons, information would be in an unmerged second row [same patient name listed in two separate rows], where columns B & C would be different). Column J is an autosum of columns E through I if that makes a difference. Column K is a notes column. The data that needs to be merged is always added to the bottom of the spreadsheet in order to show that a patient was seen on any given day, with columns B through K almost always being blank. Example:

Column A---------Column B---Col C----Col D--Col E--Col F--Col G--Col H--Col I--Col J---------Col K
Patient Name-----Therapist---Shared--%P----2/3----2/4----2/5-----2/6----2/7---Total Visits--Notes

Alice Alpha--------AB----------PT-------1---------------------------------------------0-------------blah
Boris Beta---------BC----------SELF----2----------------------------------------------0------------blahblah
Carl Carlisle-------CD---------PTA------3----------------------------------------------0
Carl Carlisle-------AB---------SELF-----2----------------------------------------------0
Donny Delta-------DE---------PT--------1---------------------------------------------0
Ernie Elephant-----EF---------PTA-------2---------------------------------------------0
Alice Alpha-----------------------------------------------1
Carl Carlisle--------------------------------------1--------------1---------------1
Ernie Elephant-------------------------------------------1---------------1

This is what I'm hoping it can look like:

Column A---------Column B---Col C----Col D--Col E--Col F--Col G--Col H--Col I--Col J---------Col K
Patient Name-----Therapist---Shared--%P----2/3----2/4----2/5-----2/6----2/7---Total Visits--Notes

Alice Alpha--------AB----------PT-------1---------------1-----------------------------1-------------blah
Boris Beta---------BC----------SELF----2----------------------------------------------0------------blahblah
Carl Carlisle-------CD---------PTA------3-------1--------------1---------------1------3
Carl Carlisle-------AB---------SELF-----2----------------------------------------------0
Donny Delta-------DE---------PT--------1---------------------------------------------0
Ernie Elephant-----EF---------PTA-------2--------------1---------------1-------------2

In this example Carl Carlisle is being seen for two different things, however how would it be written so the macro would know which Carl Carlisle row to merge with? I'm thinking that before running the macro I could manually enter the information into column B so it knows which Carl Carlisle row above to merge with.

Data always starts at row 14 (row 13 is frozen pane header column), and extends to a row that is different every week depending on how many people happen to be in the list.

I found something from this link that looks very similar to what I need, but with no knowledge of coding, I have no idea how it should be tweaked: Merge Duplicate Rows Keeping Data In Same Columns

I know I'm asking a lot, but the amount of time this takes to manually go through hundreds of rows of patient names every week is incredibly time consuming, and I have too many other things to stay on top of at work for this to drag me down day in and day out.

Any help that anyone can give I would appreciate so much. If you need additional information on the spreadsheet, let me know.

Thank you!

[millz]

It would be extremely helpful if you can attach a sample workbook. Let people have a feel of how your workbook looks like and also easily test code, instead of manually creating a brand new dummy workbook just for testing. It's even more complicated now, since you mentioned there are frozen rows.

[vinnie179]

Okay, I just attached a sample workbook. Dummy names highlighted in pink is under conditional formatting for duplicate values, but that doesn't need to be in there. Row 13 (the frozen pane) can be undone before running the macro if that makes things easier. Also, the information above row 13 is just other statistical stuff that I don't think would affect the macro, but it's necessary for it to be there. Basically it shows each therapist and the number of patients they have listed under their initials who are seen 1, 2, or 3 times a week. The information below the last row of utilized cells is yet another tally, which gets manually moved up or down rows, depending on how many active patients there are in a given week. That I can move on my own after a macro, as I just want to make this as easy to code as possible for you or someone else. Again, sometimes a patient is seen for two different things, so they would have two separate lines, and there would almost always be seen by two different therapists, so that could be the indicator for a macro to know to not merge the two lines.

I wish I could do this stuff on my own, but I'm grateful for any help you can give.

[macropod]

You could use a macro like:

Please Login or Register  to view this content.

However, I also note that your original post had multiple entries for Carl Carlisle, with different therapists, but the data you wanted merged didn't indicate which therapist's records should be updated. As coded, the above macro will merge & delete the last of those lines also. To avoid that, some extra code would be required, for testing whether there's anything in the therapist column and to avoid both sets of records being updated. For proper control, you'd need to indicate the therapist details for every one of the merge row candidates, so they too can be assigned to the correct therapists. That said, the following code additions after 'For j = LRow To i + 1 Step -1' will prevent duplications and the merging of the therapist records:

Please Login or Register  to view this content.

Using ' i + 1' populates the last therapist record, whilst ' i - 1' populates the first therapist record.

[millz]

Try the attached. If there are more than one record for a single patient, you will be asked which one should be merged to.

[vinnie179]

Great, thank you so much! I'll run some tests over the next couple days and let you know.