Create 'Find' style box to display duplicated text entries

zoso

Well-known Member
Joined
Oct 23, 2003
Messages
725
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!
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Well-known Member
Joined
Oct 23, 2003
Messages
725
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

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
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

Well-known Member
Joined
Oct 23, 2003
Messages
725

ADVERTISEMENT

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

Legend
Joined
Feb 18, 2002
Messages
13,690
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

Well-known Member
Joined
Oct 23, 2003
Messages
725

ADVERTISEMENT

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?
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
zoso said:
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)
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,167
Messages
5,570,640
Members
412,334
Latest member
ExcelForLifeDontHate
Top