Complex Sorting Macro for names & emails

[Justijb]

Good Morning All,

As always I like to start off my posts with saying thank you to everyone who responds and provides assistance. I have a tough one to crack...I need to create a code/script that sorts 5 columns of data. First column is (A) last name, first name (exact format). Second column (B) is email address, third column (C) date, (D) is address, and finally (E) is comments.

What I need is to sort column A by name of family. Meaning, two rows will have the same email address because two people in that family use the same email address. that would need to sorted onto one row. It would need to delete that row with the information, sort it into the previous row. So Row 3 is John Doe, Row 4 is Jane Doe, I need move data from row 4 and insert into row 3. This needs to be done for about 4k records. Note: What would even be more awesome if there is a way to make the name into a family name. So if column (a) has two same names in two different rows to make it as John & Jane Doe in column a, have one single email in column B ,etc.

Hopefully, my request is clear and concise. Any assistance would be very helpful. Below is an example of what I need.

FROM THIS:
[TABLE="width: 583"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Email Address
[/TD]
[TD]Date Received
[/TD]
[TD]Mailing Address if no email
[/TD]
[TD]Comments
[/TD]
[/TR]
[TR]
[TD]Doe, John
[/TD]
[TD]jdoe@email.com
[/TD]
[TD]10/2/2017
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Doe, Jane
[/TD]
[TD]jdoe@email.com
[/TD]
[TD]10/2/2017
[/TD]
[/TR]
</tbody>[/TABLE]


TO THIS:
[TABLE="width: 583"]
<tbody>[TR]
[TD]First Name
[/TD]
[TD]Last Name
[/TD]
[TD]Email
[/TD]
[TD]Date Received
[/TD]
[TD]Mailing Address[/TD]
[/TR]
[TR]
[TD]John & Jane
[/TD]
[TD]Doe
[/TD]
[TD]jdoe@email.com
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD]Thanks again!

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Dryver14]

A few questions.

1. Is there always a comma after family name.
2. is the first name always below the family name?
3 If below, is it in the row below or wrapped in a cell?
4 Will date received always be the same, if not what takes precedent?

 

[Justijb]

Hello Dryver14, thank you for responding.

1)yes, comma separates the name, e.g. doe, john
2)yes. last name, first name. always.
3)wrapped in a cell, a3 is doe, john, a4 is doe, jane
4)date received is different through out.

Hopefully I didn't add more confusion. Thanks again!

 

[Dryver14]

OK nearly there if jane emailed on 11/2/18 and john on 10/2/18 do you just keep the top date or take the later date?

 

[Justijb]

Good question and point. So if the last name is the same, the date will be the same. Apologies for the confusion. So yes, just take the top date.

 

[Justijb]

Also, and hopefully this doesn't change much but there are single records (single names) throughout the spreadsheet as well. So it might be doe, john in row 1, doe, jane in row 2, smith, frank in row 3, yolk, john in row 4, then go back to family names in row 5 & 6

 

[Dryver14]

I dont really understand that, but looking at the problem from this end the first thing I would do is insert a column between names and email and use text to column to separate the names out.

Is that possible, makes the task far easier.

Anyone following, I am not proud, If you have a better idea, jump in at any time

 

[Justijb]

I appreciate the tip. I did that and now the names are separated into last and first name in column a & b respectively. So I would need the script to sort by column a. If a1 & a2 have the same last name then move first name in b2 into b1 and sort the rest of the row accordingly. If last name in column a doesn't have a match leave as is.

 

[Dryver14]

OK, that makes it much easier, tea time but I will jump on this later if that's OK

 

[Justijb]

No worries and appreciate all the effort. Enjoy your tea! Talk to you then.