To remove duplicates and get the cell-address of the original

[Sagar.Ghole]

To remove duplicates and get the cell-address of the original..

Sr.No Status Name Add Designation
1 1 Jack ABC Mgr
2 2 Pope XYZ CeO
3 D Jack ABC Mgr
4 D Pope XYZ CEO
5 D Pope XYZ CEO

As you can see in the above data, there are instances when all the three columns would match and this would be a duplicate record...
Now by using the SUMPRODUCT function, Im able to get the duplicates, however I would need two things first to remove the new entries as well as get the cell address of the original one..

Now is it possible to remove the duplicate entries by MS Excel or a Macro would be needed and then other thing is how do I get the cell address of the orginal one if I were to have it in my formula...

Can i get to display the cell address of the original one concatenated with the "D"?

[shg]

If your intent is to remove the duplicate entries that appear earlier in the list (i.e., lower row number) rather than later, why not just change the references in the formula so that the earlier entry is flagged as the duplicate?

[Sagar.Ghole]

If your intent is to remove the duplicate entries that appear earlier in the list (i.e., lower row number) rather than later, why not just change the references in the formula so that the earlier entry is flagged as the duplicate?

What I want is to remove the duplicates which come later in the database and also be able to get the cell-address of the original record in the beginning / top to view before deletion...if possible..

Is it possible to remove the duplicates after all the records have been entered via Macro?

[sglife]

I think this is what you need

Please Login or Register  to view this content.

[Sagar.Ghole]

I think this is what you need

Please Login or Register  to view this content.

Exactly that's what I wanted, to display the Original Row number but is it possible to delete the Duplicate Records later...

I think VBA would be an option on which I woul need help...

I would like a command button saying Delete Duplicates ...on the press event we need the Duplicates to be removed..

I need to flag all the values which are duplicates:

Sr.No FName Lname Company Designation
1 Jack Ross abc Mgr
2 Pope Evan XYZ CeO
3 Ross Jack abc Mgr
4 Jack Adam asa Mgr
5 Pope Evan XYZ CEO
6 Jack Ross abc Mgr


Now in the above records, the 1,3,6 record is duplicate as well as 2,5 are also duplicates the formula Im using is giving me Duplicates for 3,5 and 6..

In my formula I get the "Duplicates only for 3 ,5 and 6 I want it to be displaying for 1,3,6 and 2,5...

I want to also take into consideration the length of eac cell so as that to delete those records with minimum information..

Let's say in the above records the length is different then the one with the minimum length out of the three duplicates should be displayed as Duplicates so as to be deleted further.