Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter for unique value
I have 2 sets of data that should be identical but I know they are not - how
can I filter so that it gives me only the unique values? I have found ways of filtering to delete duplicates but that doesn't tell me which cells are unique to that set of data. Thanks if anyone can help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter for unique value
As long as you have some column within the data which is a unique
reference (like an employee number, or stock code) then you can use the MATCH function to see if the item in a particular record is contained anywhere in the other data set - the formula is copied down and would return something like "not present" if there is no match. A similar formula can be used in the other data set to see if those records are contained within the first data set. Then it is just a matter of applying autofilter to the data sets to check for "not present" to determine the unique values. Hope this helps. Pete On Jun 17, 12:11*am, Christi Witte <Christi wrote: I have 2 sets of data that should be identical but I know they are not - how can I filter so that it gives me only the unique values? *I have found ways of filtering to delete duplicates but that doesn't tell me which cells are unique to that set of data. Thanks if anyone can help |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter for unique value
I have thousands of records in each column. I would like it to compare the 2
columns and highlight a value that is not in the other column. "Pete_UK" wrote: As long as you have some column within the data which is a unique reference (like an employee number, or stock code) then you can use the MATCH function to see if the item in a particular record is contained anywhere in the other data set - the formula is copied down and would return something like "not present" if there is no match. A similar formula can be used in the other data set to see if those records are contained within the first data set. Then it is just a matter of applying autofilter to the data sets to check for "not present" to determine the unique values. Hope this helps. Pete On Jun 17, 12:11 am, Christi Witte <Christi wrote: I have 2 sets of data that should be identical but I know they are not - how can I filter so that it gives me only the unique values? I have found ways of filtering to delete duplicates but that doesn't tell me which cells are unique to that set of data. Thanks if anyone can help |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter for unique value
On Jun 17, 9:32 am, Christi Witte
wrote: I have thousands of records in each column. I would like it to compare the 2 columns and highlight a value that is not in the other column. "Pete_UK" wrote: As long as you have some column within the data which is a unique reference (like an employee number, or stock code) then you can use the MATCH function to see if the item in a particular record is contained anywhere in the other data set - the formula is copied down and would return something like "not present" if there is no match. A similar formula can be used in the other data set to see if those records are contained within the first data set. Then it is just a matter of applying autofilter to the data sets to check for "not present" to determine the unique values. Hope this helps. Pete On Jun 17, 12:11 am, Christi Witte <Christi wrote: I have 2 sets of data that should be identical but I know they are not - how can I filter so that it gives me only the unique values? I have found ways of filtering to delete duplicates but that doesn't tell me which cells are unique to that set of data. Thanks if anyone can help You could Google "morefunc" for a free set of UDFs. One of them is supposed to pull unique values, although I haven't used it so not sure if it fits with what you are doing. Other useful functions in morefunc, too. You could also use OR with arrays and conditional formatting to give an answer. If column A and B are your data, then.... C1: =OR(A1=$B$1:$B$100) D1: =OR(B1=$A$1:$A$100) Enter as array with CTRL-SHIFT-ENTER, not just ENTER. Copy those formulas down as far as necessary and adjust for proper size. Then do conditional formatting with whatever you want to highlight the cells on columns A and B with a formula like: =C1=FALSE |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter unique on key fields only | Excel Worksheet Functions | |||
Filter nonblank AND unique | Excel Discussion (Misc queries) | |||
Filter Unique Records | Excel Discussion (Misc queries) | |||
unique filter results in some non-unique records. | Excel Discussion (Misc queries) | |||
Unique Value without using FILTER | Excel Worksheet Functions |