Extract duplicates

[Mikelowrey]

Hello,

I have around 80K entries, what I would like to do is find the duplicates and extract them to a different document. how can I achieve that?

Thank you.

 

[Yongle]

HI Mike

Wow, very technical for me, trying to figure this out.

Suggests you may prefer a non VBA solution
Here is one for you:

The formula in column T returns the FIRST incidence of duplication of each name

1. Take a copy of your file and name it "MovedRows" (or whatever you want to call it)

2. Enter this formula in cell T3 and fill down( hover cursor over bottom right of cell until it looks like + then double click)
=IF(COUNTIF($G$3:G3,G3)=2,"Y","N")

3 enter "Retain" in T1

4.Select column T and copy/Paste Values
( locking the values before deleting anything)

5. With cursor in T1, menu Data \ Filter to set the autofilter

6. Save the file

7. Save a copy of the file As "RetainedRows" (or whatever you want to call it)

(you are now working in "RetainedRows")

7. Filter data based on value in column T = "Y"

8. Select those rows and delete them in one go

9. remove autofilter to show all the data

10. Save the file and close it

11. Open "MovedRows"

12. Filter data based on value in column T = "N"

13. Select those rows and delete them in one go

14. remove autofilter to show all the data

15. Save the file

The 2 workbooks should give you exactly what you wanted
- check that all your data is in either one file or the other (add last row number in both files MINUS 2 )

 

[Yongle]

Re-reading my notes above , it looks like I got my "Y" and "N" the wrong way round on steps 7 and 12
- you need to delete the rows that don't belong in each file

 

[Zakhorian]

Re: Extract duplicates: With in single cell

I have a different issue here
for e.g I have a cell consisting "I want to be more and more productive in excel".
I want a function which can extract the duplicate text More in the cell where the function would be applied.
the number of duplicates can go till 4-5 text.

Is there any way in VBA to perform this?

 

[Fluff]

Re: Extract duplicates: With in single cell

@Zakhorian
Please do not "hijack" another member's thread. You need to start a thread of your own.

 

[SpillerBD]

2. Enter this formula in cell T3 and fill down( hover cursor over bottom right of cell until it looks like + then double click)
=IF(COUNTIF($G$3:G3,G3)=2,"Y","N")

Your formula would treat 3rd occurrence same as the first...
I think the formula should be looking for the <>1 or >=2 or >1.

[COLOR=#222222][FONT=Verdana]=IF(COUNTIF($G$3:G3,G3)>1,"Y","N")[/FONT][/COLOR]

However the count method assumes the data is sorted where the original will be on the lowest Row#

An Advanced Filter could then copy the desired data to a new location.