Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and display only duplicates
I need help please! I have a worksheet with multiple rows/columns of data. I
need to look at the text in each cell in column B (which is already sorted alphabetically) and if a cell is repeated, to display only the rows in which the cells in a column are duplicates. For example below I would only want to to display the two rows that the text Cory is repeated. Col A Col B * Aaron * Ben * Cory * Cory * David * Evan I have not been able to figure this out on my own. Any help is greatly appreciated. Thank you, Sarah |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and display only duplicates
Usually people do this will formulas on the worksheet and use Autofilter.
1) in column c put Row 2 and copy down the column =if(countif($B:$B,B2) 1,true,false) 2) Select column C. On menu Data - Filters - AutoFilter 3) Now select True on AutoFilter to get duplicates "Sarah_Lund" wrote: I need help please! I have a worksheet with multiple rows/columns of data. I need to look at the text in each cell in column B (which is already sorted alphabetically) and if a cell is repeated, to display only the rows in which the cells in a column are duplicates. For example below I would only want to to display the two rows that the text Cory is repeated. Col A Col B * Aaron * Ben * Cory * Cory * David * Evan I have not been able to figure this out on my own. Any help is greatly appreciated. Thank you, Sarah |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and display only duplicates
Hi Sarah,
In C2, enter the following formula: =COUNTIF($B$2:$B$11,B2)1 and drag the formula down as far as needed. Now autofilter columns A:C of your data, using a criterion of true for the third column of the autofilter. Now only data rows will be visible for which there is a duplication of column C data --- Regards. Norman "Sarah_Lund" wrote in message ... I need help please! I have a worksheet with multiple rows/columns of data. I need to look at the text in each cell in column B (which is already sorted alphabetically) and if a cell is repeated, to display only the rows in which the cells in a column are duplicates. For example below I would only want to to display the two rows that the text Cory is repeated. Col A Col B * Aaron * Ben * Cory * Cory * David * Evan I have not been able to figure this out on my own. Any help is greatly appreciated. Thank you, Sarah |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and display only duplicates
Here's one approach...
With the Col_B sorted list beginning in B10 and B10 as the column heading (eg name) A1: DupeTest A2: =OR(B11=B10,B11=B12) Select from B10 down through the last data cell of Col_B From the Excel Main Menu: <data<filter<advanced filter List Range: (already selected) Criteria Range: $A$1:$A$2 Click [OK] Only the contiguous duplicate values will be displayed. Is that something you can work with? Post back if you have more questions. ------------------- Regards, Ron Microsoft MVP - Excel "Sarah_Lund" wrote in message ... I need help please! I have a worksheet with multiple rows/columns of data. I need to look at the text in each cell in column B (which is already sorted alphabetically) and if a cell is repeated, to display only the rows in which the cells in a column are duplicates. For example below I would only want to to display the two rows that the text Cory is repeated. Col A Col B * Aaron * Ben * Cory * Cory * David * Evan I have not been able to figure this out on my own. Any help is greatly appreciated. Thank you, Sarah |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and display only duplicates
This works great. Is there any way to do this with a macro so the enduser
doesn't have to do these steps each time they get an updated excel file? Thank you, Sarah "Norman Jones" wrote: Hi Sarah, In C2, enter the following formula: =COUNTIF($B$2:$B$11,B2)1 and drag the formula down as far as needed. Now autofilter columns A:C of your data, using a criterion of true for the third column of the autofilter. Now only data rows will be visible for which there is a duplication of column C data --- Regards. Norman "Sarah_Lund" wrote in message ... I need help please! I have a worksheet with multiple rows/columns of data. I need to look at the text in each cell in column B (which is already sorted alphabetically) and if a cell is repeated, to display only the rows in which the cells in a column are duplicates. For example below I would only want to to display the two rows that the text Cory is repeated. Col A Col B * Aaron * Ben * Cory * Cory * David * Evan I have not been able to figure this out on my own. Any help is greatly appreciated. Thank you, Sarah |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and display only duplicates
Hi Sarah,
If you need to automate this, turn on the macro recorder and perform the necessary operations manually. This will provide you with code which may be edited to afford more generic application. If you experience problems in editing the recorder code, post back with the problematic code. --- Regards. Norman "Sarah_Lund" wrote in message ... This works great. Is there any way to do this with a macro so the enduser doesn't have to do these steps each time they get an updated excel file? Thank you, Sarah "Norman Jones" wrote: Hi Sarah, In C2, enter the following formula: =COUNTIF($B$2:$B$11,B2)1 and drag the formula down as far as needed. Now autofilter columns A:C of your data, using a criterion of true for the third column of the autofilter. Now only data rows will be visible for which there is a duplication of column C data --- Regards. Norman "Sarah_Lund" wrote in message ... I need help please! I have a worksheet with multiple rows/columns of data. I need to look at the text in each cell in column B (which is already sorted alphabetically) and if a cell is repeated, to display only the rows in which the cells in a column are duplicates. For example below I would only want to to display the two rows that the text Cory is repeated. Col A Col B * Aaron * Ben * Cory * Cory * David * Evan I have not been able to figure this out on my own. Any help is greatly appreciated. Thank you, Sarah |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and display only duplicates
This worked exactly how I wanted.
Thank you! Sarah "Norman Jones" wrote: Hi Sarah, If you need to automate this, turn on the macro recorder and perform the necessary operations manually. This will provide you with code which may be edited to afford more generic application. If you experience problems in editing the recorder code, post back with the problematic code. --- Regards. Norman "Sarah_Lund" wrote in message ... This works great. Is there any way to do this with a macro so the enduser doesn't have to do these steps each time they get an updated excel file? Thank you, Sarah "Norman Jones" wrote: Hi Sarah, In C2, enter the following formula: =COUNTIF($B$2:$B$11,B2)1 and drag the formula down as far as needed. Now autofilter columns A:C of your data, using a criterion of true for the third column of the autofilter. Now only data rows will be visible for which there is a duplication of column C data --- Regards. Norman "Sarah_Lund" wrote in message ... I need help please! I have a worksheet with multiple rows/columns of data. I need to look at the text in each cell in column B (which is already sorted alphabetically) and if a cell is repeated, to display only the rows in which the cells in a column are duplicates. For example below I would only want to to display the two rows that the text Cory is repeated. Col A Col B * Aaron * Ben * Cory * Cory * David * Evan I have not been able to figure this out on my own. Any help is greatly appreciated. Thank you, Sarah |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Duplicates | Excel Discussion (Misc queries) | |||
Display all values in other cells with no duplicates | Excel Discussion (Misc queries) | |||
Display a 1 for the first of duplicates | Excel Worksheet Functions | |||
Duplicate value are removed, but how can i display in tx8 the amount of duplicates there are ? | Excel Programming | |||
Find duplicates | Excel Programming |