Merging rows and combining data

[bergie60]

Hi Guys,

Have been watching the forum for a while now and has helped me with a few problems, however, I need help with merging some data that I cannot seem to fathom. It is exported data from our database for providing reminders to owners regarding their pets vaccination or worming reminders. I have attached an example below with desired results.

I would like to merge the data for the patient names (column b) into the same cell if they belong to the same client (column e or g) preferably creating a list in column b (with or without commas). I need Column C (the reminder name) to be have only one type of reminder displayed but in a specific order of supremacy (if that's the right way to say it). The order should be annual vaccination then rabies vaccination then worming reminder. So in the example sheet client 98 has a worming reminder and annual vaccination for two different animals but column c says annual vaccination only but client 78 has two different animals with worming reminders so column c says worming reminder.

It doesn't matter which patient species comes up if two different ones are combined (eg if a client has a dog and a cat the species column (column c) can say either)

I hope this all makes sense. Any help would be much appreciated.

Thanks

Simon

[JBeaucaire]

give this a try:

Please Login or Register  to view this content.

[mike7952]

Jerry beat me to it but heres another.

Please Login or Register  to view this content.

[bergie60]

Hi both of you.

Thanks so much for your quick replies. Unfortunately I cannot get Mike's solution to work (probably as I am so new to this).Jerry, thank you so much, your solution is amazing and does the job almost 100% correctly. There is only one slight issue which occurs in so few cases that I could correct it manually. On a few occasions when the macro is run on a larger set of data than the example, it combines multiple patients from the same client name incorrectly. This seems to occur when patients are due more than one reminder each. So for example, when spike and alfie are both due worming reminders and vaccination reminders (ie there are four rows of data but only for two animals) it combines them as either "spike, spike, alfie, alfie" or "spike, alfie, spike, alfie" (depending on the types of reminder) rather than just "spike, alfie". I wondered if there was a small tweak to the code that could fix this, but as I said it occurs on relatively few occasions that it is easy to fix manually as well.

Thanks for all our help,

Simon

[JBeaucaire]

Can you update your example data/results to demonstrate this, too?

[jindon]

See if the attached is kind of what you wanted.

[JBeaucaire]

Maybe this small tweak if all you want to do is avoid duplicate names:

Please Login or Register  to view this content.

[bergie60]

Hi Jerry,

That now works absolutely perfectly. I have a very happy receptionist with a very large smile on her face. So thank you very much for your help, you're a star.

I will mark the issue as solved.

Best wishes

Simon

[mike7952]

@Simon,

Just wondering when you said couldnt get it to work. Any Errors? I was adding the results to the results sheet. I changed the code to add the results to example sheet. See if this will work with your uploaded example workbook.

Please Login or Register  to view this content.

[AB33]

Mike,
Your first code works too, except that you have space-the separator , instead of comma.
I have changed these two lines in to comma separator and the code works pefectly.

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

[mike7952]

Thanks AB, I was adding the results to the results tab, Im guessing maybe the OP didnt realize it. So the second code added the results to the example sheet.