Create 'Find' style box to display duplicated text entries

[zoso]

Hi there!

I've searched the board but am unable to find a way to display a list of all text entries that are duplicated, in the same way that the Find button shows all the cells containing a defined string.

My problem is that my sheet has about 2,000 rows in it, and I suspect there are unwanted duplicates but don't know what is duplicated, so the 'find' function is of no use!

What I'm after is a display that doesn't interfere in any way with the cells - just like the Find function i.e. I wouldn't want cells highlighting because I will have to manually 'un-highlight' them all afterwards. This will be a daily check, so I want to avoid the hassle of pasting the data to a blank sheet and the cells being highlighted there for me to check through every time.

Hope you can help!

 

[erik.van.geit]

Hi, zoso,

no highlights, not interfere with cells
but what do you want to do then with the duplicates ?
what will you do whith the results ?

how would you like the results to be displayed ?

kind regards,
Erik

EDIT: cleand up some typos

 

[zoso]

Hi Erik - it's good to hear from you again, thanks for replying!

What I was after was just 2 columns of results - 1 with the cell refs and the other with the duplicated text. I can have control over which duplicates I want to delete.

Is this possible?

 

[XLGibbs]

Zoso,

What exactly are you trying to do? Is this related to the rogue text that is randomly being pasted in your file? Seems like a rough way to go about it....

 

[zoso]

Hi Gibbs - thanks for your input!

Yes, it is, as I have no way of knowing which entries are duplicated. The one entry I found simply because there were so many characters in the cell it stood out.

 

[lenze]

Hi Zoso

I think you should use condtional fomatting to highlight the duplicates (Numerous examples on the board.

Then, after you do whatever with the duplicates, simply delete

the CF.

lenze

 

[zoso]

Hi Lenze - good to hear from you too!

I don't see how I could use that because wouldn't the text strings be the conditions? I'm therefore chasing my tail, cos if it's the duplicated text strings I want to view, wouldn't I have an infinite number of conditions needed to identify those?

 

[lenze]

No. Select your range and use CF Formula is

=IF(COUNTIF(yourRange, 1stCellInRange)>1,TRUE,FALSE)


See here for details
http://www.cpearson.com/excel/duplicat.htm#HighlightingDuplicates

HTH

lenze

 

[erik.van.geit]

Hi Lenze - good to hear from you too!

I don't see how I could use that because wouldn't the text strings be the conditions? I'm therefore chasing my tail, cos if it's the duplicated text strings I want to view, wouldn't I have an infinite number of conditions needed to identify those?

no, you need a countif like I used in this code (which I made because you told not to want to use highlights, although to my sense you would better use that)

Sub test()
'assuming first row = row 2
'row 1 needs to be empty

Dim LR As Long

LR = Cells(Rows.Count, 1).End(xlUp).Row

Range("B1:C1") = "header"
Range("B2:B" & LR).FormulaR1C1 = "=COUNTIF(R2C1:R" & LR & "C1,RC[-1])>1"
Stop 'this line so you can see the formula for conditional format before proceeding
Range("C2:C" & LR).FormulaR1C1 = "=""A"" & ROW()"
Range("B2:C" & LR).Value = Range("B2:C" & LR).Value
Range("A1:C1").AutoFilter
    
    With Range("A2:C" & LR)
    .AutoFilter Field:=2, Criteria1:=True
    .Copy Range("D2")
    End With
    
Columns("E").Delete
Columns("B:C").Delete
Range("A1:C1").AutoFilter

End Sub

kind regards,
Erik

 

[parry]

A low tech way. Simply filter the column that has duplicates and decide which one to keep...

Book1
	A	B	C	D
1	apple	Duplicate
2	banana	Ok
3	apple	Duplicate
4	pear	Ok
5	orange	Ok
6	apple	Duplicate
Sheet1