Has been googling this all day long..can somebody help me with this
Looking for a formula to identify duplicate entry in a list of two column. Highlight/delete the duplicate entry and keep only the orginal one.
PFA a sample scenario, PS- A value is duplicate only if the both the case number and version number are same.
Try.
In G5 and copy down.
=IF(SUMPRODUCT((E5:E5=E5)*($F$5:F5=F5))=1,"Unique","Duplicate")
Then in I5 put this ARRAY formula. Copy down and across.
=IFERROR(INDEX(E$5:E$11,SMALL(IF($G$5:$G$11="Unique",ROW(E$5:E$11)-4),ROW(E1))),"")
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Fotis1991
I think the formula requires a small tweaking coz im getting many entries as duplicate. which actually is not ? Can you jus go through the document attached along
Row 5 values are not the same as row 8 values. Are these?
This formula?
=IF(SUMPRODUCT(($B$5:$B$476=B5)*($C$5:$C$476=C5))=1;"unique";"duplicate")
Fotis,
Thanks a lot. Sorry for the late reply.
The new formula is working fine. Is there a way i can say the first instance on a duplicate case number as unique and the second one as a duplicate. For eg in file name - 'Retrospikz duplicate V2.0' Row number 11 (B11-C11) and row number 267 (B267-C267)are duplicates.Row 11 should be mentioned as 'Unique' and Row 267 as a 'duplicate'
Thanks a lot once again.
=IF(SUMPRODUCT(($B$5:B5=B5)*($C$5:C5=C5))=1,"unique","duplicate")
And pls don't reply 2-3 days later. I have to re-think what i did 2-3 days earlier and i spend time and thought for this
Thanks a lot. Its working wonders...!!
Once again, Im really sorry for the late reply.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks