matching column entries

[Guest]

hi,

possibly a basic query, but i have rarely used excel functions before

what i require is to filter a column to show only the non-unique entries.
i.e. if the data in one cell matches the data in another cell of the same
column then display this row. it no matches are found hide the row
(alternatively, just sort the column so matching entries are found at the top)

thanks in advance

ps. excel 97 sr2

 

[Dave Peterson]

I'd use a helper column and a formula (copied down).

=countif($a$2:$a$9999,a2)
(assuming headers in row 1)

Then copy down the range.

Then apply data|filter|autofilter to this range. Custom Filter to show greater
than 1.

 

[Max]

One way ..

Assume sample table below
in Sheet1, cols A to C
data from row2 down

Field1 Field2 Field3
1002 Text1 Data1
1005 Text2 Data2
1004 Text3 Data3
1003 Text4 Data4
1002 Text5 Data5
1002 Text6 Data6
1000 Text7 Data7
1001 Text8 Data8
1001 Text9 Data9
etc

Using an empty col to the right, say col E

Put in E2:
=IF(A2="","",IF(COUNTIF(A$2:A$100,A2)>1,ROW(),""))

Copy E2 down to E100
to cover the max expected range of data

In Sheet2
------------
With the same headers in A1:C1 : Field1, Field2, Field3

Put in A2:
=IF(ISERROR(SMALL(Sheet1!$E:$E,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$E:$E,ROWS($A$1:A1)),Sheet1!$E:$E,0)))

Copy A2 across to C2, fill down to C100
(cover the same range as in Sheet1)

Sheet2 returns the desired results (i.e. all non-unique rows),
For the sample data in Sheet1, you'll get:

Field1 Field2 Field3
1002 Text1 Data1
1002 Text5 Data5
1002 Text6 Data6
1001 Text8 Data8
1001 Text9 Data9
(rest are blank rows)

Adapt to suit ..

 

[Biff]

Hi!

Or, use Dave's formula idea with a little change:

=countif($a$2:$a$9999,a2)>1

Now, select both the helper column and the data column.
Sort on the helper column descending then on the data
column ascending.

Biff

 

[Guest]

easy when you know how

thanks dave

I'd use a helper column and a formula (copied down).

=countif($a$2:$a$9999,a2)
(assuming headers in row 1)

Then copy down the range.

Then apply data|filter|autofilter to this range. Custom Filter to show greater
than 1.

 

[Guest]

thanks for the input max

dave's suggestion works, so ill go with that unless i need something more
powerful!

cheers

 

[Max]

You're welcome !
Thanks for the feedback
Do go as preferred <g>