Remove duplicates and consolidate data in duplicated fields.

Hi All. Hope today is going well

I am trying to learn VBA but only starting.

I have a excel vba task to complete ( A sample file is attached)

Data in spreadsheet is shown "BEFORE" as in raw file and "AFTER" example of what should look like after macro is run.

AIM IS TO REMOVE DUPLICATE ROWS OF SAME "MOBILE NUMBER" BUT RETAIN ANY "WEBSITE 2" OR "EMAIL" IN ANY DUPLICATED ROWS BEFORE DELETING THE OTHER DUPLICATE RECORDS.

1. "MOBILE" IS SORTED IN ASCENDING ORDER
2. IF NO DUPLICATE GO ONTO NEXT ROW
3. GO DOWN LIST CHECK FOR DUPLICATES (CAN BE MORE THAN ONE DUPLICATE I.E KLAUDIA IN "BEFORE")
4. WHEN FIND DUPLICATE "IF EMAIL" IS BLANK - CHECK IF THERE IS AN EMAIL IN ANY DUPLICATE FIELD AND BRING THAT TO THE FIRST DUPLICATE
5. WHEN FIND DUPLICATE "IF WEBSITE 2" IS BLANK CHECK IF THERE IS A "WEBSITE 2" IN ANY DUPLICATE FIELD AND BRING THAT TO THE FIRST DUPLICATE
6. KEEP FIRST DUPLICATE WITH EMAIL AND WEBSITE 2 POPULATED (IF DATA) AND DELETE DUPLICATES BELOW
7. MOVE ONTO NEXT MOBILE NUMBER CHECKING IF A DUPLICATE ETC


COULD BE 5000 ROWS+


Appreciate your expertise and time in advance

Hi -

Run this code in the sample data provided.

The output goes to column G (No deletion is being made here rather a unique data will be pulled out).

Please Login or Register  to view this content.

Regards,
event

Superb work but can you do it so that it also pulls out all the "non duplicates" rows as well to column G??? (ie all those rows that just have an existing unique mobile) i.e Mimi , Mel, etc in the spreadshete

Hi -

As you can see, it pull also the single record (no duplicates) and put in column G as well.

Regards,
event

HI, If you kindly look in original sheet attached there is also records for names like "Mimi" and "Mel" etc that have no duplicate mobile numbers. I would like to have those in column G as well Sirs (As well as what you have done already)

Hi -

Please send a new file after running the script and note what portion you need to add.

Regards,
event

Hi.... I have attached SAMPLE FILE 2.... It shows the other mobile numbers (examples in yellow) that dont have any duplicates... I would need all these as well Sirs in column G.

Hi -

So you have run the code based on sheet2 data right? In this case, the way I see it, the data starts in row 3 while the first sheet starts in row 6.

so you need to change the codes to start in row 3

Please Login or Register  to view this content.

Regards,
event

Yes working superbly sir... if you provide an email I will send you something via Paypal as an appreciation

sent you something via paypal