Check if a pair exists in column

[bsweet0us]

I think this is fairly straightforward, but I'm not versed in VBA enough to figure out the best way to do it.

Here's the scenario. I have values in Sheet1 that run from M6 down the column an indeterminate amount. I have another set of values beginning in N6 and running down the same number of rows as those in column M. The values in the corresponding row in M and N make up a pair. (M6 and N6 make a pair. M7 and N7 make a pair. Etc.)

On Sheet2, beginning in cell C5 and continuing down an indeterminate number of rows a list of values. The values in adjacent rows make a pair and the first member of the pair is always in an odd row. (C5 and C6 are a pair. C7 and C8 are a pair. Etc.)

I need to determine if ANY of the pairs in columns M & N match a pair in column C on the other sheet. The pairs are in no particular order but could be placed in alphabetical order without issue as long as the pairs stay in adjacent cells. All the values are names of people, so they are all strings. Values are first and last names in the same cell.

I can provide additional details as needed and I look forward to the magic this community comes up with for me!

 

[StephenCrump]

I'm not versed in VBA enough to figure out the best way to do it.

So, perhaps a formula approach?

	A	B	C	D	E	F
1	Data1		Matches		Data2	Matches
2	qw	er	1		Blah
3	ty	ui	0		Blah
4	op	as	0		Blah
5	df	gh	0		Blah
6	jk	lz	2		Blah
7	xc	vb	0		Blah
8					jk	5
9					lz
10					Blah
11					Blah
12					qw	1
13					er
14					Blah
15					Blah
16					jk	5
17					lz
18					Blah
19					Blah
20					Blah
21					Blah
Sheet3

Cell Formulas
Range	Formula
C2:C7	C2	=COUNTIF(F$2:F$21,ROWS(C$2:C2))
F2:F21	F2	=IFERROR(IF(MOD(ROWS(E$2:E2),2)=1,MATCH(E2&"|"&E3,INDEX(Data1,,1)&"|"&INDEX(Data1,,2),),""),"")

Named Ranges
Name	Refers To	Cells
Data1	=Sheet3!$A$2:$B$7	F2:F21
Data2	=Sheet3!$E$2:$E$20	F2:F21

Good luck. There are umpteen ways that first and last name data won't match exactly.

 

[bsweet0us]

So, perhaps a formula approach?

	A	B	C	D	E	F
1	Data1		Matches		Data2	Matches
2	qw	er	1		Blah
3	ty	ui	0		Blah
4	op	as	0		Blah
5	df	gh	0		Blah
6	jk	lz	2		Blah
7	xc	vb	0		Blah
8					jk	5
9					lz
10					Blah
11					Blah
12					qw	1
13					er
14					Blah
15					Blah
16					jk	5
17					lz
18					Blah
19					Blah
20					Blah
21					Blah
Sheet3

Cell Formulas
Range	Formula
C2:C7	C2	=COUNTIF(F$2:F$21,ROWS(C$2:C2))
F2:F21	F2	=IFERROR(IF(MOD(ROWS(E$2:E2),2)=1,MATCH(E2&"|"&E3,INDEX(Data1,,1)&"|"&INDEX(Data1,,2),),""),"")

Named Ranges
Name	Refers To	Cells
Data1	=Sheet3!$A$2:$B$7	F2:F21
Data2	=Sheet3!$E$2:$E$20	F2:F21

Good luck. There are umpteen ways that first and last name data won't match exactly.

Thanks for the help, but this isn't exactly what I'm looking for.

So, perhaps a formula approach?

	A	B	C	D	E	F
1	Data1		Matches		Data2	Matches
2	qw	er	1		Blah
3	ty	ui	0		Blah
4	op	as	0		Blah
5	df	gh	0		Blah
6	jk	lz	2		Blah
7	xc	vb	0		Blah
8					jk	5
9					lz
10					Blah
11					Blah
12					qw	1
13					er
14					Blah
15					Blah
16					jk	5
17					lz
18					Blah
19					Blah
20					Blah
21					Blah
Sheet3

Cell Formulas
Range	Formula
C2:C7	C2	=COUNTIF(F$2:F$21,ROWS(C$2:C2))
F2:F21	F2	=IFERROR(IF(MOD(ROWS(E$2:E2),2)=1,MATCH(E2&"|"&E3,INDEX(Data1,,1)&"|"&INDEX(Data1,,2),),""),"")

Named Ranges
Name	Refers To	Cells
Data1	=Sheet3!$A$2:$B$7	F2:F21
Data2	=Sheet3!$E$2:$E$20	F2:F21

Good luck. There are umpteen ways that first and last name data won't match exact

This seems to give me the result I need, but I think it's going to have to be coded.

Here's the purpose of the workbook. It is for a doubles bowling tournament in which each entrant is randomly paired with another entrant. However, each entrant can also choose other entrants as partners. So, if John enters the tournament, he's paired with another entrant but he doesn't know who it is. John wants to bowl with Cindy and Bobby for sure, so he enters with them but he is still going to be paired with another entrant at random.

The workbook consists of two sheets. One has a list of the entrants and the other has a list of pre-selected partners (like John/Cindy and John/Bobby from above). Once all entrants are inputted, there is code that randomizes the list into pairs (placed in the M and N columns from my initial post). Using the example from above, the code that I'm seeking needs to see if John was randomly paired with Cindy or Bobby and re-randomize if that is the case because he is already partnered with them. The list of entrants is generally less than 50 people in size and not everybody enters with a partner, so I think the odds of infinite re-randomizations is very small.

 

[StephenCrump]

Ah, you already have code. That changes things a little. It would make more sense to modify your existing VBA code to check for matches before putting the results into Excel.

You're probably better of now posting your code in a new thread, perhaps called something like: VBA code to generate random pairs without duplicates