Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove double data?
Hello,
I have this list, lets call it A, that contains 3 columns. I have another list, B, that as the same number of columns. List A contains all the data of list B. In list A, I would like to be able to remove all the data that can be found in list B. Is there a specific function? I tried working with the Advanced filter where I chose to work with Unique records only but it doesn't quite work as list A remains unchanged. Can you offer some help? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove double data?
One way to do this using formula.....
Assming list A is in Sheet1 without headers and list B is in Sheet2 without headers try this formula in Sheet1 D1 which will return "In List B" if found in List B or else return blank..Once done you can sort by Col D and remove the entries.... =IF(OR(A1&B1&C1="",ISERROR(MATCH(A1&B1&C1,Sheet2!$ A$1:$A$100&Sheet2!$B$1:$B$100&Sheet2!$C$1:$C$100,0 ))),"","In ListB") Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "marycler" wrote: Hello, I have this list, lets call it A, that contains 3 columns. I have another list, B, that as the same number of columns. List A contains all the data of list B. In list A, I would like to be able to remove all the data that can be found in list B. Is there a specific function? I tried working with the Advanced filter where I chose to work with Unique records only but it doesn't quite work as list A remains unchanged. Can you offer some help? Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove double data?
Hi
with a macro see Cpearson web it has other options as well including the macro http://www.cpearson.com/excel/deleting.htm "marycler" wrote: Hello, I have this list, lets call it A, that contains 3 columns. I have another list, B, that as the same number of columns. List A contains all the data of list B. In list A, I would like to be able to remove all the data that can be found in list B. Is there a specific function? I tried working with the Advanced filter where I chose to work with Unique records only but it doesn't quite work as list A remains unchanged. Can you offer some help? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I double the column data? | Excel Discussion (Misc queries) | |||
double axis, double problem (i hope only to me) | Charts and Charting in Excel | |||
Double Sorting of Data | Excel Discussion (Misc queries) | |||
Remove double data entries | Excel Discussion (Misc queries) | |||
Sorting lists to remove double entries | Excel Discussion (Misc queries) |