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

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

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

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