Home |
Search |
Today's Posts |
#1
|
|||
|
|||
matching column entries
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 |
#2
|
|||
|
|||
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. billytf wrote: 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 |
#3
|
|||
|
|||
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 .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "billytf" wrote in message ... 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 |
#4
|
|||
|
|||
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 -----Original Message----- 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. billytf wrote: 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 . |
#5
|
|||
|
|||
easy when you know how
thanks dave "Dave Peterson" wrote: 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. billytf wrote: 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 |
#6
|
|||
|
|||
thanks for the input max
dave's suggestion works, so ill go with that unless i need something more powerful! cheers "Max" wrote: 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 .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "billytf" wrote in message ... 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 |
#7
|
|||
|
|||
You're welcome !
Thanks for the feedback Do go as preferred <g -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "billytf" wrote in message ... thanks for the input max dave's suggestion works, so ill go with that unless i need something more powerful! cheers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting unique entries in column A but only if specific values appear in columns B and C | Excel Worksheet Functions | |||
Preventing Duplicate Entries within a column | Excel Discussion (Misc queries) | |||
Filtering a column to exclude any repeated entries. | Excel Discussion (Misc queries) | |||
how do i see if entries in one column are in another column? | New Users to Excel | |||
I want to delete rows with duplicate entries within one column. | Excel Discussion (Misc queries) |