Compare ANY cells that match in two ranges (UDF)

[Lapper]

Hello Guys,

Can the below UDF be modified (corrected) to give an *exact* match of ANY cell in one range to ANY cell in another range? There can be repeats in both ranges. There can be more than one match and that is ok.

Match any cell in B1:B9 to any cell in A1:A9 and give result at C9 or wherever.

E.g. B2 & B8 plus A1 & A8 repeat and is ok.

Result: A2 and B5 match so result "Yes" at C9

----A----B-----------C
1 110 127
2 334 222
3 645 509
4 250 651
5 822 334
6 711 453
7 800 766
8 110 222
9 984 516 Result "Yes"

The results from the below UDF are not always accurate.

Please Login or Register  to view this content.

As well as a UDF I would also appreciate a normal formula for above if possible.

Thanks

Lapper

[FDibbins]

Try this formula...
=IF(SUMPRODUCT(COUNTIF(A1:A9,B1:B9))>0,"Yes","")

[Lapper]

Many thanks FDibbins. Exactly right.

Any luck with the UDF?


Lapper

[humdingaling]

if you are after EXACT then your find needs to be

Please Login or Register  to view this content.

Please Login or Register  to view this content.

also
do you have an example of an inaccurate answer?

[Lapper]

Just create 2 ranges at say A1:A10 and B1:B10 using for example

Please Login or Register  to view this content.

as the values
then at C10 use the UDF original code 'AnyMatch' and just keep recalculating and you will see the inaccuracies.

Using the same ranges as above your modified code only gives "NO" for me

Thanks

Lapper

[Edit].... I should have added that if you copy the ranges, with the random values, and then paste them as values only elsewhere then try the code, it will then be accurate. My requirement is for random values.

[humdingaling]

try adding
Application.Volatile at the start of the UDF

[Lapper]

No....Makes no difference.

[Lapper]

Anybody else care to try to *fix* the UDF code?

Thanks

Lapper

[Lapper]

I would also like a formula for this. Match EVERY cell in one range to ANY cell in another range.
There can be repeats in both ranges. There can be more than one match and that is ok.

Match EVERY cell in B1:B9 to ANY cell in A1:A9 and give result at C9 or wherever.

E.g. There could be repeats in column A and column B and that is ok.

Result: Every cell in B1:B9 has a match in A1:A9 so result "Yes" at C9

----A----B-----------C
1 110 645
2 334 250
3 645 711
4 250 800
5 822 110
6 711 800
7 800 334
8 268 984
9 984 645 Result "Yes"

Thanks

[humdingaling]

Please Login or Register  to view this content.

add lookin:=xlvalues to .find

[Lapper]

Thank you very much hum.............That fixed it

Any answer to #9?

Lapper

[FDibbins]

Not really sure why you would want a UDF for this, when a formula will probably be quicker and more efficient?

[Lapper]

Yes sorry. I mean I want a formula.


Lapper

[FDibbins]

Does my formula from post 2 not do that for you?

[Lapper]

No. See below

EVERY or EACH cell in B1:B9 must match ANY cell in A1:A9

As stated there can be repeats in each range.



----A----B-----------C
1-- 1-- 1
2-- 2-- 2
3-- 3-- 3
4-- 4-- 800
5-- 5-- 5
6-- 6-- 6
7-- 7-- 7
8-- 8-- 8
9-- 9-- 9 Result "Yes" But should be "No"
10--
11--

800 doesn't match ANY cell in A1:A9

Thanks

Lapper

[FDibbins]

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Phuocam]

Result "Yes" But should be "No"

Try...

Please Login or Register  to view this content.

[Lapper]

Here it is................ I hope

[Lapper]

Thanks for the new UDF Phuocam

That is a bonus (and works) because we are now after a formula.

See the attached sheet at #18

Thanks

[Phuocam]

Here it is...I hope

Try this formula in C9:

=IF(MIN(COUNTIF(A1:A9,B1:B9))>0,"Yes","")

Enter with Ctrl+Shift+Enter.

[Lapper]

Yes that works perfectly Phuocam

Thank you for that.

Lapper

[Phuocam]

You're welcome.