mole999
Well-known Member
- Joined
- Oct 23, 2004
- Messages
- 10,524
- Office Version
- 2019
- 2016
- 2013
- Platform
- Windows
Problem, I need to identify exact duplicates so I can remove them
I have
=IF(COUNTIF($AJ$2:$AJ$67596,AJ9)>1,IF(COUNTIF(AJ$2:AJ2,AJ2)=1,"","Duplicate"),"")
but it is currently doing over hundred thousand rows, and as its volatile its taking forever (well tens of minutes)
any quick fix that would do the same but place the static value DUPLICATE in the right place. I've tried multiple different ways, this worked on small test sets. I can then get on with working with the cut down data
Pretty Please
I have
Code:
Application.ScreenUpdating = False
With ActiveSheet
.Range("AM2").FormulaR1C1 = _
"=IF(COUNTIF(R2C36:R" & LR & "C36,RC[-3])>1,IF(COUNTIF(R2C[-3]:RC[-3],RC[-3])=1,"""",""Duplicate""),"""")"
.Range("Am2").Select
Selection.AutoFill Destination:=.Range("Am2:Am" & LR)
.Range("Am2:Am" & LR).Select
End With
Application.ScreenUpdating = True
=IF(COUNTIF($AJ$2:$AJ$67596,AJ9)>1,IF(COUNTIF(AJ$2:AJ2,AJ2)=1,"","Duplicate"),"")
but it is currently doing over hundred thousand rows, and as its volatile its taking forever (well tens of minutes)
any quick fix that would do the same but place the static value DUPLICATE in the right place. I've tried multiple different ways, this worked on small test sets. I can then get on with working with the cut down data
Pretty Please