VBA Function that mimics the Access Not Matching Query

  • Thread starter Thread starter Herbert Chan
  • Start date Start date
H

Herbert Chan

Hello,

I've a spreadsheet in Excel and I have some data.

I want to be able to extract the data from set1 that are not in set2. In
Access, that will be a nonmatching query. However, in Excel, how do I do it
with VBA function? Any written functions already out there?

Thanks.

Herbert
 
What is the definition of a set here?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Herbert,

I think you'd be better served asking this question in one of the Excel
newsgroups.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Hello,

But my data are arranged like this:

x1 x2 x3 x4 x5
x6 x7 x8 x9 x10
x11 x12 x13 x14 x15

y1 y2 y3 y4 y5
y6 y7 y8 y9 y10
y11 y12 y13 y14 y15

So it's like I want to extract the y's that are not in the x's.

It's good to know that ADO can be used directly within Excel, but it seems
that it cannot be used in my case, right? At least not very directly. So
I'm wondering if there're any functions out there that can do this
extraction.

Thanks.

Herbert
 
depending on how large you range is, I would just go off to the right and
use a formula like

(assume the x's start in A1)

=if(countif(RangeofY's,A1)=0,A1,"")

then drag fill that down and across in the same pattern as you X values

this will give you your X values not included in Y.

This could be accplished in a similar manner using a macro.
 
I still don't understand what you want to do. Is it

1) Identify rows in the y-block for which there is no matching row in
the x-block? If so, treat each block as a table and use ADO or DAO.

2) Identify cells in the y-block that have a different value from the
corresponding cell in the x-block (e.g. if the value of y5 is different
from the value of x5)? If so, use ordinary Excel formulas in a third
block)

3) Something else? If so, please explain unambiguously, preferably with
the aid of sample data.
 
whoops, you said y's not in the x's and I gave you x's not in the y's. Make
the following adjustments. Formula would be adjacent to the y's and assume
the first y is in A1.

(assume the y's start in A1)

=if(countif(RangeofX's,A1)=0,A1,"")
 
Back
Top