Remove duplicates
 

I am having a hard time removing duplicate records. I have tried the
advanced filter with unique values only but it is not working since
fields contain blanks.

Is there a formula I can use instead that will remove duplicate Ref Dr
#'s?

Here is a sample of my data.

Ref Dr ID Ref Name Count
R9999 2933 FOLSOM, ROBERT J 6
R9999 10595 1
R9998 1461 NOVOA, ELIO R 15
R9998 6310 NOBOA LELIO 1
R9995 1264 MOLINET, ROLAND 18
R9995 7853 DAVILA, GUILLERMO 1
R9982 514 LEE, KEN 55
R9982 5015 2
R9982 12301 ROSEN, ROBERT 1
R9978 456 SUDIAZ, FABIO E 65
R9978 6575 CARDONA, LYSETTE 1
R9978 11301 MURUVE, NICOLAS 1
R9978 9836 1


--
Tuttamay77
------------------------------------------------------------------------
Tuttamay77's Profile: http://www.excelforum.com/member.php...o&userid=33047
View this thread: http://www.excelforum.com/showthread...hreadid=541679

Remove duplicates
 

U¿ytkownik "Tuttamay77"
napisa³ w
wiadomoœci ...

I am having a hard time removing duplicate records. I have tried the
advanced filter with unique values only but it is not working since
fields contain blanks.

Is there a formula I can use instead that will remove duplicate Ref Dr
#'s?

Here is a sample of my data.

Ref Dr ID Ref Name Count
R9999 2933 FOLSOM, ROBERT J 6
R9999 10595 1
R9998 1461 NOVOA, ELIO R 15
R9998 6310 NOBOA LELIO 1
R9995 1264 MOLINET, ROLAND 18
R9995 7853 DAVILA, GUILLERMO 1
R9982 514 LEE, KEN 55
R9982 5015 2
R9982 12301 ROSEN, ROBERT 1
R9978 456 SUDIAZ, FABIO E 65
R9978 6575 CARDONA, LYSETTE 1
R9978 11301 MURUVE, NICOLAS 1
R9978 9836 1


--
Tuttamay77
------------------------------------------------------------------------
Tuttamay77's Profile:
http://www.excelforum.com/member.php...o&userid=33047
View this thread: http://www.excelforum.com/showthread...hreadid=541679


i would use additional columns ( i assume your data starts in col A )
first to have ref number:=LEft(A1;5)
to extract dr: =mid(A1;7;find(" ";A1;find(" ";A1;1)+1)-6)
then use adv filter
mcg

Remove duplicates
 

The data is in different/separate columns already. See attached sample
file. Thanks.


+-------------------------------------------------------------------+
|Filename: test.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4751 |
+-------------------------------------------------------------------+

--
Tuttamay77
------------------------------------------------------------------------
Tuttamay77's Profile: http://www.excelforum.com/member.php...o&userid=33047
View this thread: http://www.excelforum.com/showthread...hreadid=541679

Remove duplicates
 

Here's one way using non-array formulas
to extract the unique lines in another sheet

Rename your source data sheet to just: X
So the source data is in X, cols A to C, data from row6
to a max expected row5000 (col headers in A5:C5),
with key col = col A (Ref Dr)

In a new sheet: Y
With the same col headers in A1:C1

Put in D2:
=IF(X!A6="","",IF(COUNTIF(X!$A$6:A6,X!A6)1,"",ROW ()))
Copy D2 down to D5000
(Leave D1 empty)

Then put in A2:
=IF(ISERROR(SMALL($D$6:$D$5000,ROW(A1))),"",
INDEX(X!A$6:A$5000,MATCH(SMALL($D$6:$D$5000,ROW(A1 )),$D$6:$D$5000,0)))

Copy A2 across to C2, fill down only as far as required to extract all the
unique lines
Eg: Fill A2:C2 down to say, C2000, if it's estimated that there's likely to
be less than 2,000 uniques

Y will return the unique lines from X, with all results neatly bunched at
the top
(Hide away the criteria col D if needed)

Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Remove duplicates
 

Here's a sample implementation:
http://www.savefile.com/files/3239689
Remove duplicates.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---