Match 5 columns values in row exactly within range

[jarhead58]

I'm trying to compare B2:G2 values to see if there is an exact match in other rows. If so, highlight it and matching row. This is just a sample table but I have at least 1000 other rows. TIA

testrun.xlsm
	B	C	D	E	F	G
2	3	43	45	61	65	14
3	10	24	27	35	53	18
4	6	13	38	39	53	6
5	27	32	34	43	52	13
6	4	23	37	61	67	7
7	17	54	56	63	69	20
8	11	14	31	47	48	4
9	3	4	6	48	53	10
10	28	31	40	41	46	4
11	8	12	18	44	51	18
12	2	57	58	60	65	26
13	51	54	57	60	69	11
14	4	5	17	43	52	5
15	7	15	18	32	45	20
16	13	15	17	45	63	13
17	14	16	37	48	58	18
18	23	32	33	45	49	14
19	2	6	40	42	55	24
20	3	43	45	61	65	14
Sheet1

 

[Eric W]

Try:

Book2
	A	B	C	D	E	F	G
1
2		3	43	45	61	65	14
3		10	24	27	35	53	18
4		6	13	38	39	53	6
5		27	32	34	43	52	13
6		4	23	37	61	67	7
7		17	54	56	63	69	20
8		11	14	31	47	48	4
9		3	4	6	48	53	10
10		28	31	40	41	46	4
11		8	12	18	44	51	18
12		2	57	58	60	65	26
13		51	54	57	60	69	11
14		4	5	17	43	52	5
15		7	15	18	32	45	20
16		13	15	17	45	63	13
17		14	16	37	48	58	18
18		23	32	33	45	49	14
19		2	6	40	42	55	24
20		3	43	45	61	65	14
21
Sheet5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:G1000	Expression	=COUNTIFS($B$2:$B$1000,$B2,$C$2:$C$1000,$C2,$D$2:$D$1000,$D2,$E$2:$E$1000,$E2,$F$2:$F$1000,$F2,$G$2:$G$1000,$G2)>1	text	NO

 

[jarhead58]

Am I using the "Use a formula...." conditional? I don't see anything that would match what you have. TIA

Try:

Book2
	A	B	C	D	E	F	G
1
2		3	43	45	61	65	14
3		10	24	27	35	53	18
4		6	13	38	39	53	6
5		27	32	34	43	52	13
6		4	23	37	61	67	7
7		17	54	56	63	69	20
8		11	14	31	47	48	4
9		3	4	6	48	53	10
10		28	31	40	41	46	4
11		8	12	18	44	51	18
12		2	57	58	60	65	26
13		51	54	57	60	69	11
14		4	5	17	43	52	5
15		7	15	18	32	45	20
16		13	15	17	45	63	13
17		14	16	37	48	58	18
18		23	32	33	45	49	14
19		2	6	40	42	55	24
20		3	43	45	61	65	14
21
Sheet5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:G1000	Expression	=COUNTIFS($B$2:$B$1000,$B2,$C$2:$C$1000,$C2,$D$2:$D$1000,$D2,$E$2:$E$1000,$E2,$F$2:$F$1000,$F2,$G$2:$G$1000,$G2)>1	text	NO

Am I using the "Use a formula...." conditional? I don't see anything that would match what you have. TIA

 

[Eric W]

Yes, select your range, I used B2:G1000, yours may be different. Then click Conditional Formatting > New Rule > Use a formula > then enter the formula in the formula box. Again, you may need to change the ranges in the formula to match your sheet. Then click Format... and choose your Fill color.

Note that the top left corner of the selected range is B2, and all the individual ranges in the formula ($B2, $C2, $D2, etc.) also refer to row 2. The top selected row must match the row in the formula.

 

[jarhead58]

Yes, select your range, I used B2:G1000, yours may be different. Then click Conditional Formatting > New Rule > Use a formula > then enter the formula in the formula box. Again, you may need to change the ranges in the formula to match your sheet. Then click Format... and choose your Fill color.

Note that the top left corner of the selected range is B2, and all the individual ranges in the formula ($B2, $C2, $D2, etc.) also refer to row 2. The top selected row must match the row in the formula.

Perfect, thank you!!