Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filtering duplicate information
Hi,
I have a large speadsheet where some of the columns are identical. i want to remove the duplicate rows entirely where the identical columns occur. i've tried using the advance filter function but because the rows are slightly different it doesnt work can you help please thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filtering duplicate information
If not every value duplicates the values in another row, how do you determine
which row is the original and which row is the duplicate? Is the first (i.e., closest to the top of the sheet) occurrence the "original" or the keeper? If so then let's assume the column that contains the duplicated data is column A and the data starts in row 2 with row 1 being headers. Use a formula like this in an empty column, starting in row 2 =countif(A$2:A2,A2) and copy it down. Copy the formulas, then Paste-Special Values over top the formulas. Now filter on that new column for anything over 1 and delete those rows "Rachael" wrote: Hi, I have a large speadsheet where some of the columns are identical. i want to remove the duplicate rows entirely where the identical columns occur. i've tried using the advance filter function but because the rows are slightly different it doesnt work can you help please thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filtering duplicate information
I have tried this but its not picking up the duplicated rows the top formula
gives an answer of 1 but the rest of the spreadsheet shows 0 so im unable to filter further thank you though "Duke Carey" wrote: If not every value duplicates the values in another row, how do you determine which row is the original and which row is the duplicate? Is the first (i.e., closest to the top of the sheet) occurrence the "original" or the keeper? If so then let's assume the column that contains the duplicated data is column A and the data starts in row 2 with row 1 being headers. Use a formula like this in an empty column, starting in row 2 =countif(A$2:A2,A2) and copy it down. Copy the formulas, then Paste-Special Values over top the formulas. Now filter on that new column for anything over 1 and delete those rows "Rachael" wrote: Hi, I have a large speadsheet where some of the columns are identical. i want to remove the duplicate rows entirely where the identical columns occur. i've tried using the advance filter function but because the rows are slightly different it doesnt work can you help please thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filtering duplicate information
Hi Rachael,
In an empty column, cocatenate the fields from the columns that you want to dedup or filter by. In another empty column do a countif based on the previous step. Filter on the second column. Hope this helps. "Rachael" wrote: Hi, I have a large speadsheet where some of the columns are identical. i want to remove the duplicate rows entirely where the identical columns occur. i've tried using the advance filter function but because the rows are slightly different it doesnt work can you help please thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filtering Information | Excel Discussion (Misc queries) | |||
filtering out rows with duplicate information | Excel Discussion (Misc queries) | |||
finding & filtering out duplicate cells | Excel Discussion (Misc queries) | |||
filtering duplicate enteries | Excel Discussion (Misc queries) | |||
filtering duplicate enteries | Excel Discussion (Misc queries) |