Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
KW KW is offline
external usenet poster
 
Posts: 10
Default filtering problems

I have a worksheet with three columns and thousands of rows. I am trying to
find 250 duplicates within 46000 rows. Seems like a piece of cake until you
factor in an ID number that makes every row unique. I need to make a list of
all the duplicates. Here is an example of the data.....
7114 187501 42939
17858 187501 42939
22526 187501 42940
20705 187501 42940
14967 187501 42941
17129 187501 42941
19658 187501 42942
3652 187501 42942

Can anyone help?? Thanks in advance.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,480
Default filtering problems

Hi

It depends whether you are after Duplicates in columns B and in Column C or
in B&C combined.
If the former, then in D1 enter
=IF(COUNTIF($B$1:B1,B1)1,"Duplicate",COUNTIF($B1: B1,B1))
and in E1 enter
=IF(COUNTIF($B$1:C1,C1)1,"Duplicate",COUNTIF($B1: C1,C1))
and copy down as far as required

If it is B&C then in D1 enter
=B1&"|"&C1
in E1 enter
=IF(COUNTIF($H$1:H1,B1&"|"&C1)1,"Duplicate",COUNT IF($H$1:H1,B1&"|"&C1))
and copy down as far as required
--
Regards
Roger Govier

"KW" wrote in message
...
I have a worksheet with three columns and thousands of rows. I am trying
to
find 250 duplicates within 46000 rows. Seems like a piece of cake until
you
factor in an ID number that makes every row unique. I need to make a list
of
all the duplicates. Here is an example of the data.....
7114 187501 42939
17858 187501 42939
22526 187501 42940
20705 187501 42940
14967 187501 42941
17129 187501 42941
19658 187501 42942
3652 187501 42942

Can anyone help?? Thanks in advance.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
filtering halb1961 Excel Discussion (Misc queries) 3 February 26th 08 01:23 AM
Filtering jfg Excel Discussion (Misc queries) 2 June 14th 07 06:46 AM
Excel 2003 filtering problems [email protected] Excel Worksheet Functions 2 May 3rd 07 08:24 PM
Filtering ? Tarjei Lundarvollen Excel Worksheet Functions 7 July 18th 05 11:00 PM
trouble filtering a list. Why isn't column filtering? Pat Excel Worksheet Functions 1 July 18th 05 03:30 PM


All times are GMT +1. The time now is 05:20 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"