[RESOLVED] [ACCESS][EXCEL] Show only vales that do NOT match? Find NOT

[forthelove]

Hello all, I've been utilizing this incredible resource for quite some time, and have finally come across a problem I couldn't find already solved here. I'm really having a hard time even labeling the issue.

I'm pretty new to excel, and very new to access. The data originates in Access but I'm more comfortable dealing with excel so I have exported the data there.

I'm not picky how the problem is solved, can be vba or formula, or if a simple access query can be used then that would be most excellent I will be grateful to see any way to solve it.

The problem is to compare the assembly list against each part. I already know which assemblies the part is in, but I can't seem to find a way to calculate which assemblies from the table it is NOT in.

I have attached an img of the table as well as the xls file itself as an example. Im unsure of how else to post the data. sorry!

Thank you all for your help, great community!

[nO_OnE]

If I am understanding you correctly, you can do a simple query in access using the criteria.
Help with that really depends on which version of Access you using.

[nO_OnE]

MSDN Reference for Queries in Access 2007:
http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

[forthelove]

I'm stuck with OFFICE 2K3.

And my problem with Access queries is that I don't even know where to begin. Can you perhaps give me an example of this simple Access query you speak of?

Thank you no_one

[nO_OnE]

Here's some great places for you to start with access:
http://msdn.microsoft.com/en-us/office/aa905401.aspx
and
http://office.microsoft.com/en-us/ac...010067704.aspx

I would help walk you through it, but I have never used Access 2003, and the menus have changed a lot, and I am on my way out.
http://office.microsoft.com/en-us/ac...010247313.aspx

Basically you want to create a query on the data, and in the criteria you want to specify "not like" and reference the assembly numbers from the other table.

[westconn1]

i do not know of a formula to do what you ask, but here is a UDF

put in cell C3 =findnot(b3)

put in module

Function findnot(r As Range) As String
Dim fnd As Boolean
myarr = Split(r, ", ")
For Each t In Range("j2:j15")
    For i = 0 To UBound(myarr)
        bounds = Split(myarr(i), "-")
        If t >= CLng(bounds(0)) And t <= CLng(bounds(1)) Then fnd = True: Exit For
    Next
    If Not fnd Then findnot = findnot & t & ", "
    fnd = False
Next
If Right(findnot, 2) = ", " Then findnot = Left(findnot, Len(findnot) - 2)
End Function

[forthelove]

Thanks for the getting started guides no_one, I'll be looking into those!

Westconn, your macro is perfectly elegant! I literally got chills when I ran it. Thank you!