Only Keep Non-duplicates in an Excel spreadsheet

M

mrkempf

I want to remove both duplicate records leaving non-dups only in
spreadsheet. How do i do this? Here's a before and after example o
what I'm hoping to do easily in Excel. Given 9 rows and colums a, b
and c:

(BEFORE)
row a b c
1 cdef886 44.00 x
2 cdef886 14.00 y
3. cdef886 30.00 y
4. bb7ceg7 8.99 x
5. bb7ceg7 8.99 y
6. 895848 139.00 x
7. 123abde 45.95 y
8. jh8cegg 7.23 x
9 jh8cegg 7.23 y

(AFTER) If I could leave non-dups based on colums a and b, rows 4, 5,
and 9 would be removed, leaving the rest
 
P

Peo Sjoblom

One way, insert at least 2 empty rows at the top, give the columns a header
(use bold format for header),
in for instance E2 put

=SUMPRODUCT(--($A$3:$A$11=A3),--($B$3:$B$11=B3))=1

where A3 and B3 are the first cells with data,
select the whole table (using your example and after 2 rows have been
inserted)
select A2:C11, do data>filter>advanced filter,
copy to another location, criteria range is

$E$1:$E$2


select where to copy it, will give this table

cdef886 44 x
cdef886 14 y
cdef886 30 y
895848 139 x
123abde 45.95 y



--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Top