G
Guest
I'm trying to take a worksheet and filter out values that don't exist on
another worksheet
another worksheet
Install the app
How to install the app on iOS
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
Dave Peterson
Chip Pearson has a bunch of techniques for working with duplicates at:
http://www.cpearson.com/excel/duplicat.htm
http://www.cpearson.com/excel/duplicat.htm
M
Max
Perhaps you may like to try this as well ..
Assume you have a list
in Sheet1, A1:A10
--------------------------
100
101
102
103
104
105
106
107
108
109
and another list
in Sheet2, A1:A5
------------------------
102
103
105
107
106
and the objective is to extract values in Sheet1's list
that don't exist in Sheet2's list
into Sheet3
Try this
In Sheet3
-------------
Select A1:A10
(select a range size equal to that of the list in Sheet1)
Put in the formula bar:
=IF(ISERROR(SMALL(IF(COUNTIF(Sheet2!A1:A5,Sheet1!A1:A10)<1,Sheet1!A1:A10),RO
W())),"",SMALL(IF(COUNTIF(Sheet2!A1:A5,Sheet1!A1:A10)<1,Sheet1!A1:A10),ROW()
))
Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER
For the sample data, A1:A10 will return:
100
101
104
108
109
<Rest are blanks>
Adapt to suit
Assume you have a list
in Sheet1, A1:A10
--------------------------
100
101
102
103
104
105
106
107
108
109
and another list
in Sheet2, A1:A5
------------------------
102
103
105
107
106
and the objective is to extract values in Sheet1's list
that don't exist in Sheet2's list
into Sheet3
Try this
In Sheet3
-------------
Select A1:A10
(select a range size equal to that of the list in Sheet1)
Put in the formula bar:
=IF(ISERROR(SMALL(IF(COUNTIF(Sheet2!A1:A5,Sheet1!A1:A10)<1,Sheet1!A1:A10),RO
W())),"",SMALL(IF(COUNTIF(Sheet2!A1:A5,Sheet1!A1:A10)<1,Sheet1!A1:A10),ROW()
))
Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER
For the sample data, A1:A10 will return:
100
101
104
108
109
<Rest are blanks>
Adapt to suit
M
Max
In Sheet3
-------------
Select A1:A10
(select a range size equal to that of the list in Sheet1)
Put in the formula bar:
=IF(ISERROR(SMALL(IF(COUNTIF(Sheet2!A1:A5,Sheet1!A1:A10)<1,Sheet1!A1:A10),RO
W())),"",SMALL(IF(COUNTIF(Sheet2!A1:A5,Sheet1!A1:A10)<1,Sheet1!A1:A10),ROW()
))
Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER
Perhaps better to replace the array formula in Sheet3 above with:
=IF(ISERROR(SMALL(IF(ISNA(MATCH(Sheet1!A1:A10,Sheet2!A1:A5,0)),ROW(Sheet1!A1
:A10)),ROW())),"",INDEX(Sheet1!A1:A10,SMALL(IF(ISNA(MATCH(Sheet1!A1:A10,Shee
t2!A1:A5,0)),ROW(Sheet1!A1:A10)),ROW())))
to work with lists containing numbers and/or text
G
Guest
the objective isn't to extract but to eliminate data that doesn't exist on
the second sheet i.e.:
sheet1:
101
102
103
104
105
106
107
108
109
110
sheet2
101
104
105
106
109
111
sheet 3 would show
101
104
105
109
the second sheet i.e.:
sheet1:
101
102
103
104
105
106
107
108
109
110
sheet2
101
104
105
106
109
111
sheet 3 would show
101
104
105
109
D
Dave Peterson
So in sheet1, column B, put a formula like:
=isnumber(match(a2,sheet2!a:a,0))
and drag down.
This will return True if there's a match. False if it's not there.
Then apply data|filter|autofilter to column B.
Copy those rows with True and paste onto A1 of sheet3.
===
You could just apply the filter and hide the stuff you want to hide. Sometimes
keeping all the data is nice.
=isnumber(match(a2,sheet2!a:a,0))
and drag down.
This will return True if there's a match. False if it's not there.
Then apply data|filter|autofilter to column B.
Copy those rows with True and paste onto A1 of sheet3.
===
You could just apply the filter and hide the stuff you want to hide. Sometimes
keeping all the data is nice.
the objective isn't to extract but to eliminate data that doesn't exist on
the second sheet i.e.:
sheet1:
101
102
103
104
105
106
107
108
109
110
sheet2
101
104
105
106
109
111
sheet 3 would show
101
104
105
109
M
Max
Ok, nice of you to clarify with clearer specs..
For the "reverse" situation desired, try ..
In Sheet3
-------------
Select A1:A10
(select a range size equal to that of the list in Sheet1)
Put in the formula bar:
=IF(ISERROR(SMALL(IF(NOT(ISNA(MATCH(Sheet1!A1:A10,Sheet2!A1:A6,0))),ROW(Shee
t1!A1:A10)),ROW())),"",INDEX(Sheet1!A1:A10,SMALL(IF(NOT(ISNA(MATCH(Sheet1!A1
:A10,Sheet2!A1:A6,0))),ROW(Sheet1!A1:A10)),ROW())))
Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER
[ The above is basically the same formula as the
previous suggested, except with a NOT(...) wrapped
around the ISNA(...), since we want the "reverse" situation,
and with an extension to the range in Sheet2
to now include cell A6]
For the sample data, A1:A10 will return:
101
104
105
106
109
<Rest are blanks>
Note: "106" should / will appear
(think there's a typo in your post on the results part)
For the "reverse" situation desired, try ..
In Sheet3
-------------
Select A1:A10
(select a range size equal to that of the list in Sheet1)
Put in the formula bar:
=IF(ISERROR(SMALL(IF(NOT(ISNA(MATCH(Sheet1!A1:A10,Sheet2!A1:A6,0))),ROW(Shee
t1!A1:A10)),ROW())),"",INDEX(Sheet1!A1:A10,SMALL(IF(NOT(ISNA(MATCH(Sheet1!A1
:A10,Sheet2!A1:A6,0))),ROW(Sheet1!A1:A10)),ROW())))
Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER
[ The above is basically the same formula as the
previous suggested, except with a NOT(...) wrapped
around the ISNA(...), since we want the "reverse" situation,
and with an extension to the range in Sheet2
to now include cell A6]
For the sample data, A1:A10 will return:
101
104
105
106
109
<Rest are blanks>
Note: "106" should / will appear
(think there's a typo in your post on the results part)
G
Guest
thanks
v/r
smokeyhavoc
v/r
smokeyhavoc
Max said:Ok, nice of you to clarify with clearer specs..
For the "reverse" situation desired, try ..
In Sheet3
-------------
Select A1:A10
(select a range size equal to that of the list in Sheet1)
Put in the formula bar:
=IF(ISERROR(SMALL(IF(NOT(ISNA(MATCH(Sheet1!A1:A10,Sheet2!A1:A6,0))),ROW(Shee
t1!A1:A10)),ROW())),"",INDEX(Sheet1!A1:A10,SMALL(IF(NOT(ISNA(MATCH(Sheet1!A1
:A10,Sheet2!A1:A6,0))),ROW(Sheet1!A1:A10)),ROW())))
Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER
[ The above is basically the same formula as the
previous suggested, except with a NOT(...) wrapped
around the ISNA(...), since we want the "reverse" situation,
and with an extension to the range in Sheet2
to now include cell A6]
For the sample data, A1:A10 will return:
101
104
105
106
109
<Rest are blanks>
Note: "106" should / will appear
(think there's a typo in your post on the results part)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
Smokeyhavoc said:the objective isn't to extract but to eliminate data that doesn't exist on
the second sheet i.e.:
sheet1:
101
102
103
104
105
106
107
108
109
110
sheet2
101
104
105
106
109
111
sheet 3 would show
101
104
105
109
M
Max
You're welcome !
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.