Rows which are equal

[Neo]

Hi All,

I have 2 questions:
a) How to get from a sheet all rows which are equal,
i.e. all for which there is at least one another equal to it
b) How to get from a sheet all rows which have the same
value in one of columns (almost the same as in a) but
comparing values in one column instead of comparing
the whole row).

Your help will be very appreciated.

Regards,
Neo

 

[Lori]

a) Try:

1. Data > Filter > Advanced Filter with list range as the table and
click unique records only. 2. Mark these unique records by filling down
an "X" to the right of the data.
3. Now choose Data > Filter > AutoFilter and select blanks in the "X"
column.
4. Copy the selection to another sheet and select Data > Filter >
Advanced Filter with unique records only on the new sheet.

b) Not sure what you mean, but to see the differences between columns,
select the columns by holding down ctrl key and clicking on column
headings e.g. A,D,F,H,...

Then press ctrl+\ (Edit > Goto > Special > Column Differences) to
select the differences with the last column selected which you could
highlight in a different colour

 

[Neo]

Thanks for your answer.

1. Data > Filter > Advanced Filter with list range as the table and
click unique records only. 2. Mark these unique records by filling down
an "X" to the right of the data.
3. Now choose Data > Filter > AutoFilter and select blanks in the "X"
column.
4. Copy the selection to another sheet and select Data > Filter >
Advanced Filter with unique records only on the new sheet.

It doesn't function. It's likely that I do anything wrong.
Especially I don't understand when and where to fill down an "X".

b) Not sure what you mean, but to see the differences between columns,
select the columns by holding down ctrl key and clicking on column
headings e.g. A,D,F,H,...
Then press ctrl+\ (Edit > Goto > Special > Column Differences) to
select the differences with the last column selected which you could
highlight in a different colour

It doesn't work either. :-(

Describing my problems more simply: In both cases I want to find out
whether there are any rows (records) repeated. In the case a) I want
to know whether there are any entire raws repeated. In the case b)
I want to know whether there are any rows (records) with the same
value in the column containing the identification of the data.
In both cases, if there are any, I want to know which ones.
That's all.
Sorry for my poor English.

Regards
Neo

 

[Lori]

I think I understand better what you mean for (b) which is the same as
for (a) but with only one column. If you can manage to do (a), then (b)
should be easy as the only difference is that you select one column for
the filter instead of multiple columns.

The easiest way to find if there are repeated rows in one or more
columns is to select the relevant columns and use Data > Filter >
Advanced Filter with unique records only checked. On the status bar it
should tell you how many rows are returned.

If not all rows are displayed you can find the repeated ones by
inverting this selection i.e. displaying all those that are not
currently shown. The method below should do this but I didn't explain
it that clearly, here is a slight variation:

1. Choose Data > Filter > Advanced Filter, list range= columns in
table, unique records checked.

2. Select all the rows in the table and press ctrl + ; (semicolon) to
show only visible cells.

3. Choose Data > Filter > Show All and then Ctrl + 9 to hide selected
rows.

Now you only have the repeated data items. The last step is to copy
these to another sheet without repeats:

4. Select all the data and then Ctrl + ; (semicolon) to show only
visible cells.

5. Copy and paste this selection to a new sheet

6. Choose Data > Filter > Advanced Filter, list range= columns in
table, unique records checked.

 

[Neo]

Thank you very much.

At the moment it works for 1 column.
I use the following method:
- I select the relevant column
- Data > Filter > Advanced Filter with "unique records only" checked
- on the status bar it shows me the number of unique records
(=unique data in the selected column)
- I fill a specially added column with "X" for all rows (=unique)
- I sort the sheet according to the column with X
- I can copy the not repeated rows to another sheet
It is OK.

But when I try the method with all columns selected it tells me that
all records are unique which is not true.

Regards
Neo

 

[Neo]

Thank you very much.

At the moment it works for 1 column.
I use the following method:
- I select the relevant column
- Data > Filter > Advanced Filter with "unique records only" checked
- on the status bar it shows me the number of unique records
(=unique data in the selected column)
- I fill a specially added column with "X" for all rows (=unique)

Sorry, I forgot that before the next step it is necessary:
Data > Filter > Advanced Filter with "unique records only" UNCHECKED

 

[Lori]

Glad you found a method that worked based on this approach for the one
column.
I can't see why it would not work with all columns unless there are
slight differences somewhere, you could try a pivottable which counts
the number of items perhaps? I don't think I can help more than that,
thanks for the feedback...

 

[Neo]

I can't see why it would not work with all columns unless there are

slight differences somewhere, you could try a pivottable which counts
the number of items perhaps?

I think there must be any differences. I'll keep trying.

Thanks once again
Regards
Neo