Compare All cells in Range 1 to All cells in Range 2 – Give result

[MaxTrax]

Hi Guys,

I need two (2) formulas for the following please:

Compare ALL of the values in 1 range to ALL the values in the other range and give result if ANY cells match or don’t match.
(more than 1 cell may match and that’s ok)

I don’t want to compare just 1 cell (eg B1) in range B1:B200 to any cell in range A1:A500

The values will be Integers for one formula and text for the other.
Something like this:
If(Match(Any cell in B1:B200(or defined name) to Any cell in A1:A500), then give “Match” if matched otherwise give “No Match” if not matched.

These formulas will also be used with a *defined name* instead of a range sometimes. I assume ‘Name’ will just replace ‘Range’ in formulas.

They will be used separately throughout the workbook to compare *different* ranges.

Thanks

MaxTrax

 

[MaxTrax]

Hello Peter,

It's unclear to me too

Maybe match EACH cell values in a given range to ANY cells values in another given range (or defined 'Name' range) and give "Match" or " No Match". The ranges can/will be of different dimensions.

My last post is it. (a picture - a thousand words thing).

Thanks for the other formulas. Using Windows 10 and Excel 2016.

MaxTrax

 

[MaxTrax]

Peter. After studying your formulas I think I have what I want.

Could you explain in some detail the "AGGREGATE(14,6, " part? Particularly 14,6

Thanks

MaxTrax

 

[Peter_SSs]

Hello Peter,

It's unclear to me too

Maybe match EACH cell values in a given range to ANY cells values in another given range (or defined 'Name' range) and give "Match" or " No Match". The ranges can/will be of different dimensions.

My last post is it. (a picture - a thousand words thing).

Thanks for the other formulas. Using Windows 10 and Excel 2016.

MaxTrax

If it's unclear to you, there's no surprise it is unclear to us (or at least me). You have given one sample in your last post. However, given that you have stated the problem is unclear to you, how much weight can we give to the picture?


Is this your goal? If every value in column B occurs in column A, return "Match" otherwise "-"?

If so, what is the expected return for the data below?
Every value in column B does occur in column A, but can the two '4' values in column B match to the single '4' value in column A, or does there need to be at least two '4' values in column A to count as a Match?

Excel Workbook
	A	B
2	8	4
3	6	4
4	4	6
5	9
6	3
Data

Could you explain in some detail the "AGGREGATE(14,6, " part? Particularly 14,6

I won't explain in detail as you can find that in Excel's built-in Help but 14 stands for the "LARGE" function and 6 says to ignore any error values, though that is irrelevant for this particular use of the function.

 

[MaxTrax]

Is this your goal? If every value in column B occurs in column A, return "Match" otherwise "-"?

Yes

If so, what is the expected return for the data below?

"Match"

Every value in column B does occur in column A, but can the two '4' values in column B match to the single '4' value in column A, or does there need to be at least two '4' values in column A to count as a Match?

Yes. The two '4' values in column B can match to a single '4' in column A. There doesn't need to be more than one '4' in column A but there can be.

Thanks

MaxTrax

 

[Peter_SSs]

In that case try this. Then change the 5555 to something that is in column A.

Excel Workbook
	A	B	C
1	10	20
2	20	50
3	30	60
4	40	5555
5	50	120
6	60	140
7	70	120
8	80	20
9	90
10	100
11	110
12	120
13	130
14	140
15	150
16	160
17	170
18	180
19	190
20	200
21	210
22	220
23	230		-
Match

 

[MaxTrax]

Yes, that's what I require.

Thanks to you both

MaxTrax

 

[Peter_SSs]

Yes, that's what I require.

Thanks to you both

MaxTrax

Glad it helped.

In case it was required in an Excel version pre 2010, the following formula, entered with Ctrl+Shift+Enter should also work for the 2nd problem.

=IF(COUNT(MATCH(B1:B8,A1:A23,0))=ROWS(B1:B8),"Match","-")

 

[MaxTrax]

Hi Guys,

Below is a simplistic illustration of what I am hoping to receive a solution to. (A UDF would be fantastic)

This is an example only and the number series I finally use will be longer and different BUT the formula OR UDF principal will be the same in my end work.

I would like not to use helper columns if that can be avoided. If not then one maybe or I could use a defined name for 1 to 10.

The actual 'Set', if a formula can be written to complete it, will be copied extensively and recalculated often to give varying results throughout the worksheet.

The numbers used for this small example are 1 to 10 (10 numbers).
The numbers can be in any order (D2toD6) although it would be nice for them to be in numerical order.

The 'SET'

	A	B	C	D
1	Unique Set	Random Set	Result	Want this D2 to D6
2	3	9		2
3	9	6		4
4	1	5		7
5	6	1		8
6	5	3	Match	10

<tbody>
</tbody>

<tbody>
</tbody>

For A2:A6 I have a UDF called =randint(1,10) entered as an array C+S+E
and it only gives unique (never repeats) numbers. In this case 1 to 10

For B2:B6 I just use a random generator (1 to 10). Repeats are ok in B2:B6

For C6 I use:

=IF(AGGREGATE(15,6,COUNTIF(A2:A6,B2:B6),1),"Match","-")

(Thanks Peter_SSs) Which matches EACH number in B2:B6 to ANY number in A2:A6

This is what I require
I now wish for a Formula OR UDF in D2 to D6 to give the 5 remaining numbers which are NOT in A2 to A6 but, of course, only if 'Match' is given. They are 2, 4, 7, 8, 10

In D2 to D6 something like =IF(C6="Match", Then in D2 6 with no repeats give the 5 numbers which are not in A2:A6,Otherwise give "" in D2:D6


*Note* If the numbers were say, 0 to 10 (11 numbers), then one would miss out in D2 to D6 which is ok.


I realize "Match" will rarely occur with a larger number sequence.


Thanks