Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
Show only rows with duplicate values in a column?
I've a spreadsheet with thousands of rows of people (last name, first
name, address, etc.) I need to weed out duplicates, and sorting by lastname, firstname and then scanning the firstnames for duplicates that have the same last name is onerous!! Is there a way to have Excel (2003) show only rows in which there are more than one of a given value in a column? So let's say there's only one row with a value of "Jones" in the lastname, it'll now show that row but it'll show the three rows with "Smith" in the column? (Would be really cool if it showed rows in which there are more than one with the same value in TWO columns, but I won't dream that big.) Thanks for any suggestions! Liam |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show only rows with duplicate values in a column?
On Wed, 1 Jul 2009 07:00:42 -0700 (PDT), Mechphisto
wrote: I've a spreadsheet with thousands of rows of people (last name, first name, address, etc.) I need to weed out duplicates, and sorting by lastname, firstname and then scanning the firstnames for duplicates that have the same last name is onerous!! Is there a way to have Excel (2003) show only rows in which there are more than one of a given value in a column? So let's say there's only one row with a value of "Jones" in the lastname, it'll now show that row but it'll show the three rows with "Smith" in the column? (Would be really cool if it showed rows in which there are more than one with the same value in TWO columns, but I won't dream that big.) Thanks for any suggestions! Liam If your data is in column A and B from row 1 to row 100, you can introduce a helper column C and put the following formula in C1: =A1&B1 (this could be expanded to combine as many columns you want, but this example just takes two columns) Copy cell C1 down as far as you have data in columns A and B, to row 100 in this example. In cell D1 you put the following formula: =IF(SUMPRODUCT(--(C$1:C$100=C2))1,"DUPLICATE","") change the 100 to suit the number of data row that you have. Copy cell D1 down to D100. You can now apply an autofilter on column D to only display the rows with DUPLICATE. Hope this helps / Lars-Åke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show only rows with duplicate values in a column? | Excel Discussion (Misc queries) | |||
duplicate data to show/print in a column | Excel Discussion (Misc queries) | |||
Delete rows with duplicate values | Excel Discussion (Misc queries) | |||
one column of cells show ####. Values show when I open it. Help | Excel Discussion (Misc queries) | |||
Delete rows with duplicate values | Excel Worksheet Functions |