Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rows which are equal
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rows which are equal
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 wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rows which are equal
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rows which are equal
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 wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rows which are equal
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rows which are equal
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 - 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rows which are equal
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 wrote: 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 - 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rows which are equal
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rows & Columns in Excel | Excel Worksheet Functions | |||
Automatically inserting rows | Excel Worksheet Functions | |||
Hiding Rows if the linked rows are blank | Excel Discussion (Misc queries) | |||
Copying conditional formatting...HELP PLEASE!!!! | Excel Worksheet Functions | |||
flexible paste rows function that inserts the right number of rows | Excel Discussion (Misc queries) |