Skins Game Conditional Formatting

[Gulfcourse]

I've been unsuccessful trying to figure this out using multiple resources so I'm reaching out here.
I'm trying to be make the explanation thorough and apologize if it runs on too long.
Sheet 1 has everyones scores by hole who played the last round.
The Cells with front 9 scores go from E to M, column N being the front nine hole total, O to W the back 9 scores, column X the back 9 total, and Y the round total.
Some do not play all the time so some rows will have blank cells.
Sheet 1 has an additional row for each player that tracks Stableford points so I cannot do skins on the sheet, or don't know how to, that's why I created Sheet 2.
Sheet 2 lists everyones name and in cell C4 I used =('18-hole scores'!E6) to bring the score over and continued the formula throughout the sheet.
Columns C:K are holes 1-9 and M-U are 10-18.
If it makes any diference, if someone did not play and the cells in sheet 1 are blank, the corresponding cells in sheet 2 have 0's in them.
The row of scrores in sheet 2 go from 4 to 36.
Sheet 2's values change everytime we play another round and I change the scores on Sheet 1.

On sheet 2, I'm trying to conditionally format the lowest unique score that is not a 0 in each column, if the lowest score in a column is 3, and there were 2 of them it shouldn't be formatted.
I know there was only one 4 on the 18th hole, column U, so I tried =AND(U4=MIN($U$4:$U$36),COUNTIF($U$4:$U$36,U4)=1) and it does nothing to the cell with the 4.

I added a row (37) and used =SMALL(C4:C36,COUNTIF(C4:C36,0)+1) to find the lowest score for each hole and then added =COUNTIF(C4:C36,C37) in row (38) to find how many of the lowest scores were in each column.
I thought I could possibly use a formula that would look for a 1 in row 38, and then look and highlight the cell that matched the number in 37.
Per my effort in column U mentioned above, in this case U38 has a value of 1, U37 is 4, so I tried to many things to say if U38=1 format any cell in column U that matched the value in U37.
I don't know if I cannot get things to work because of using = to bring the values over, I tried formatting the cells as General and Numbers, didn't make any difference.
So that's were I'm at, I even went to the library and worked with someone there ann came up empty so any suggestions will be much appreciated.

 

[Peter_SSs]

Welcome to the MrExcel board!

Difficult to follow well enough to set up an accurate test workbook. Are you able to make up a small sample of each sheet & post that with XL2BB with the expected results done manually and then add any extra clarification by referring directly to the sample(s)?

 

[Gulfcourse]

Adding sheets now, thank you

PLAYER	Hole	1	2	3	4	5	6	7	8	9	OUT	10	11	12	13	14	15	16	17	18	IN	GROSS	Hdcp	NET	Total Points
	PAR	4	4	3	4	4	4	3	4	5	35	4	3	4	4	4	5	4	3	4	35
	Hdcp	7	15	4	11	17	3	9	13	1		6	2	16	12	18	8	10	4	14
Bonham
	PTS
Cox		5	5	3	4	5	6	4	5	7	44	4	3	5	4	4	6	4	3	5	38	82		82
	PTS	1	1	2	2	1	0	1	1	0	9	2	2	1	2	2	1	2	2	1	15				24
Dangerfield		6	5	3	4	5	5	4	4	4	40	5	4	4	5	4	6	5	3	6	42	82		82
	PTS	0	1	2	2	1	1	1	2	4	14	1	1	2	1	2	1	1	2	0	11				25
Davis
	PTS
Dufour		0
	PTS
Edmisten		5	4	3	6	3	4	3	5	6	39	5	4	5	6	5	7	6	3	5	46	85		85
	PTS	1	2	2	0	4	2	0	1	1	13	1	1	1	0	1	0	0	2	1	7				20
Funari
	PTS
Gosnell
	PTS
Gressell		5	6	4	6	6	6	4	5	7	49	6	3	4	5	5	7	6	5	6	47	96		96
	PTS	1	0	1	0	0	0	1	1	0	4	0	2	2	1	1	0	0	0	0	6				10
Hobbs
	PTS
Keep
	PTS
Kilroy
	PTS
Lincoln		6	6	3	6	6	6	3	4	7	47	6	5	5	5	5	7	6	5	6	50	97		97
	PTS	0	0	2	0	0	0	2	2	0	6	0	0	1	1	1	0	0	0	0	3				9
Martin
	PTS
Moderick
	PTS
Nassar		6	5	2	5	5	5	3	5	6	42	6	3	5	6	5	7	4	3	5	44	86		86
	PTS	0	1	4	1	1	1	2	1	1	12	0	2	1	0	1	0	2	2	1	9				21
Nigro		4	5	4	4	4	6	3	3	6	39	5	4	6	5	6	7	6	4	5	48	87		87
	PTS	2	1	1	2	2	0	2	4	1	15	1	1	0	1	0	0	0	1	1	5				20
Palladino
	PTS
Petro		6	5	5	5	6	5	4	5	7	48	6	3	5	4	5	6	5	3	6	43	91		91
	PTS	0	1	0	1	0	1	1	1	0	5	0	2	1	2	1	1	1	2	0	10				15
Phillips
	PTS
Roderick		4	4	3	5	4	4	3	4	4	35	5	3	5	4	5	5	4	3	5	39	74		74
	PTS	2	2	2	1	2	2	2	2	4	19	1	2	1	2	1	2	2	2	1	14				33
Sepic		6	5	4	4	4	6	3	5	5	42	4	3	5	5	5	6	5	3	4	40	82		82
	PTS	0	1	1	2	2	0	2	1	2	11	2	2	1	1	1	1	1	2	2	13				24
Smigel
	PTS
Theberge		6	4	3	4	4	5	3	4	7	40	4	3	5	5	5	6	5	3	6	42	82		82
	PTS	0	2	2	2	2	1	2	2	0	13	2	2	1	1	1	1	1	2	0	11				24
Wallon		5	5	2	5	5	5	3	5	6	41	6	4	5	5	4	5	5	5	5	44	85		85
	PTS	1	1	4	1	1	1	2	1	1	13	0	1	1	1	2	2	1	0	1	9				22
Zeman
	PTS
Terry

 

[Gulfcourse]

Prior was Sheet 1, this is Sheet 2

PLAYER	Hole	1	2	3	4	5	6	7	8	9	OUT	10	11	12	13	14	15	16	17	18	IN	GROSS	Hdcp	NET
	PAR	4	4	3	4	4	4	3	4	5	35	4	3	4	4	4	5	4	3	4	35
	Hdcp	7	15	4	11	17	3	9	13	1		6	2	16	12	18	8	10	4	14
Bonham		0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0			0
Cox		5	5	3	4	5	6	4	5	7	44	4	3	5	4	4	6	4	3	5	38	82	0	82
Dangerfield		6	5	3	4	5	5	4	4	4	40	5	4	4	5	4	6	5	3	6	42	82	0	82
Davis		0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0			0
Dufour		0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0			0
Edmisten		5	4	3	6	3	4	3	5	6	39	5	4	5	6	5	7	6	3	5	46	85	0	85
Funari		0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0			0
Gosnell		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Gressell		5	6	4	6	6	6	4	5	7	49	6	3	4	5	5	7	6	5	6	47	96	0	96
Hobbs		0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0			0
Keep		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Kilroy		0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0			0
Lincoln		6	6	3	6	6	6	3	4	7	47	6	5	5	5	5	7	6	5	6	50	97	0	97
Martin		0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0			0
Moderick		0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0			0
Nassar		6	5	2	5	5	5	3	5	6	42	6	3	5	6	5	7	4	3	5	44	86	0	86
Nigro		4	5	4	4	4	6	3	3	6	39	5	4	6	5	6	7	6	4	5	48	87	0	87
Palladino		0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0			0
Petro		6	5	5	5	6	5	4	5	7	48	6	3	5	4	5	6	5	3	6	43	91	0	91
Phillips		0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0			0
Roderick		4	4	3	5	4	4	3	4	4	35	5	3	5	4	5	5	4	3	5	39	74	0	74
Sepic		6	5	4	4	4	6	3	5	5	42	4	3	5	5	5	6	5	3	4	40	82	0	82
Smigel		0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0			0
Theberge		6	4	3	4	4	5	3	4	7	40	4	3	5	5	5	6	5	3	6	42	82	0	82
Wallon		5	5	2	5	5	5	3	5	6	41	6	4	5	5	4	5	5	5	5	44	85	0	85
Zeman		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Terry		0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0			0
Player 28		0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0			0
Player 29		0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0			0
Player 30		0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0			0
Player 31		0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0			0
Player 32		0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0			0
Player 33		0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0			0
Lowest NET		4	4	2	4	3	4	3	3	4	35	4	3	4	4	4	5	4	3	4
Count of Lowest NET Score		2	3	2	5	1	2	8	1	2	1	3	7	2	3	3	2	3	8	1

 

[Gulfcourse]

Welcome to the MrExcel board!

Difficult to follow well enough to set up an accurate test workbook. Are you able to make up a small sample of each sheet & post that with XL2BB with the expected results done manually and then add any extra clarification by referring directly to the sample(s)?

Thank you, I hope I did it correctly!

 

[Fluff]

Select C4:U36 & use this formula

=AND(C4=MINIFS(C$4:C$36,C$4:C$36,">0"),COUNTIFS(C$4:C$36,C4)=1,COLUMN(C4)<>12)

 

[Gulfcourse]

Select C4:U36 & use this formula

=AND(C4=MINIFS(C$4:C$36,C$4:C$36,">0"),COUNTIFS(C$4:C$36,C4)=1,COLUMN(C4)<>12)

Thank you, Thank you, Thank you, it works!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Peter_SSs]

If you stick with the current 2-sheet approach then you may want to consider a slight change to @Fluff's conditional formatting formula in Sheet2. Suppose that you get another player called Cox and you decide to add a new column B for first names, or you decide to add a new column B for the player's membership level or date of birth etc. If you add that new column the CF will likely highlight (or not highlight) some cells incorrectly. This change should avoid that. Rather than checking that the column is not column 12, it simply checks that the value in row 1 of the column is a number, which is another way of excluding the "OUT" column in the middle of the data.

=AND(C4=MINIFS(C$4:C$36,C$4:C$36,">0"),COUNTIFS(C$4:C$36,C4)=1,-C$1)

Sheet 1 has an additional row for each player that tracks Stableford points so I cannot do skins on the sheet, or don't know how to, that's why I created Sheet 2.

If you did want to do the skins on the original sheet, a few small modifications to the above conditional formatting formula would allow that.
For the sample below select C4:U57 and apply the CF shown.

BTW, generally better to 'Mini Sheet' with XL2BB so that we can easily see columns, rows, formulas etc.

Gulfcourse.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	PLAYER	Hole	1	2	3	4	5	6	7	8	9	OUT	10	11	12	13	14	15	16	17	18	IN
2		PAR	4	4	3	4	4	4	3	4	5	35	4	3	4	4	4	5	4	3	4	35
3		Hdcp	7	15	4	11	17	3	9	13	1		6	2	16	12	18	8	10	4	14
4	Bonham
5		PTS
6	Cox		5	5	3	4	5	6	4	5	7	44	4	3	5	4	4	6	4	3	5	38
7		PTS	1	1	2	2	1	0	1	1	0	9	2	2	1	2	2	1	2	2	1	15
8	Dangerfield		6	5	3	4	5	5	4	4	4	40	5	4	4	5	4	6	5	3	6	42
9		PTS	0	1	2	2	1	1	1	2	4	14	1	1	2	1	2	1	1	2	0	11
10	Davis
11		PTS
12	Dufour		0
13		PTS
14	Edmisten		5	4	3	6	3	4	3	5	6	39	5	4	5	6	5	7	6	3	5	46
15		PTS	1	2	2	0	4	2	0	1	1	13	1	1	1	0	1	0	0	2	1	7
16	Funari
17		PTS
18	Gosnell
19		PTS
20	Gressell		5	6	4	6	6	6	4	5	7	49	6	3	4	5	5	7	6	5	6	47
21		PTS	1	0	1	0	0	0	1	1	0	4	0	2	2	1	1	0	0	0	0	6
22	Hobbs
23		PTS
24	Keep
25		PTS
26	Kilroy
27		PTS
28	Lincoln		6	6	3	6	6	6	3	4	7	47	6	5	5	5	5	7	6	5	6	50
29		PTS	0	0	2	0	0	0	2	2	0	6	0	0	1	1	1	0	0	0	0	3
30	Martin
31		PTS
32	Moderick
33		PTS
34	Nassar		6	5	2	5	5	5	3	5	6	42	6	3	5	6	5	7	4	3	5	44
35		PTS	0	1	4	1	1	1	2	1	1	12	0	2	1	0	1	0	2	2	1	9
36	Nigro		4	5	4	4	4	6	3	3	6	39	5	4	6	5	6	7	6	4	5	48
37		PTS	2	1	1	2	2	0	2	4	1	15	1	1	0	1	0	0	0	1	1	5
38	Palladino
39		PTS
40	Petro		6	5	5	5	6	5	4	5	7	48	6	3	5	4	5	6	5	3	6	43
41		PTS	0	1	0	1	0	1	1	1	0	5	0	2	1	2	1	1	1	2	0	10
42	Phillips
43		PTS
44	Roderick		4	4	3	5	4	4	3	4	4	35	5	3	5	4	5	5	4	3	5	39
45		PTS	2	2	2	1	2	2	2	2	4	19	1	2	1	2	1	2	2	2	1	14
46	Sepic		6	5	4	4	4	6	3	5	5	42	4	3	5	5	5	6	5	3	4	40
47		PTS	0	1	1	2	2	0	2	1	2	11	2	2	1	1	1	1	1	2	2	13
48	Smigel
49		PTS
50	Theberge		6	4	3	4	4	5	3	4	7	40	4	3	5	5	5	6	5	3	6	42
51		PTS	0	2	2	2	2	1	2	2	0	13	2	2	1	1	1	1	1	2	0	11
52	Wallon		5	5	2	5	5	5	3	5	6	41	6	4	5	5	4	5	5	5	5	44
53		PTS	1	1	4	1	1	1	2	1	1	13	0	1	1	1	2	2	1	0	1	9
54	Zeman
55		PTS
56	Terry
57
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4:U57	Expression	=AND(C4=MINIFS(C$4:C$57,C$4:C$57,">0",$A$4:$A$57,"<>"),COUNTIFS(C$4:C$57,C4,$A$4:$A$57,"<>")=1,-C$1,$A4<>"")	text	NO

 

[Gulfcourse]

Thank you for the help, I tried the mini table but content exceeeded the range so I only put a few rows in
I would like to try seeing the skins on the oringal sheet but what was the 1st column in your formula (C4) is now D6 and goes to V59 instead of U57.
Do I simply change the C4 values to D6 and the U57 to V59 in the formula?

golf league tracker - stableford scoring sheet.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
3	PLAYER	Hole	1	2	3	4	5	6	7	8	9	OUT	10	11	12	13	14	15	16	17	18	IN
4		PAR	4	4	3	4	4	4	3	4	5	35	4	3	4	4	4	5	4	3	4	35
5		Hdcp	7	15	4	11	17	3	9	13	1		6	2	16	12	18	8	10	4	14
6	Bonham
7		PTS
8	Cox		5	5	3	4	5	6	4	5	7	44	4	3	5	4	4	6	4	3	5	38
9		PTS	1	1	2	2	1	0	1	1	0	9	2	2	1	2	2	1	2	2	1	15
10	Dangerfield		6	5	3	4	5	5	4	4	4	40	5	4	4	5	4	6	5	3	6	42
11		PTS	0	1	2	2	1	1	1	2	4	14	1	1	2	1	2	1	1	2	0	11
12	Davis
13		PTS
14	Dufour		0
15		PTS
16	Edmisten		5	4	3	6	3	4	3	5	6	39	5	4	5	6	5	7	6	3	5	46
17		PTS	1	2	2	0	4	2	0	1	1	13	1	1	1	0	1	0	0	2	1	7
18	Funari
19		PTS
20	Gosnell
21		PTS
22	Gressell		5	6	4	6	6	6	4	5	7	49	6	3	4	5	5	7	6	5	6	47
23		PTS	1	0	1	0	0	0	1	1	0	4	0	2	2	1	1	0	0	0	0	6
18-hole scores

Cell Formulas
Range	Formula
M4,W4	M4	=SUM(D4:L4)
D7:L7,D23:L23,N23:V23,N21:V21,D21:L21,N19:V19,D19:L19,N17:V17,D17:I17,K17:L17,N15:V15,D15:L15,N13:V13,D13:L13,N11:V11,D11:L11,N9:V9,D9:L9,N7:V7	D7	=IF(D6>0, VLOOKUP(D6-D$4-(INT($Y6/18)+(MOD($Y6,18)>=D$5)), 'Point System'!$A$4:$B$15, 2),"")
J17	J17	=SUM('18-hole scores'!L32)
M6,M8,M10,M12,M14,M16,M18,M20,M22,W6,W8,W10,W12,W14,W16,W18,W20,W22	M6	=IF(SUM(D6:L6)>0, SUM(D6:L6),"")
M7,M9,M11,M13,M15,M17,M19,M21,M23,W23,W7,W9,W11,W13,W15,W17,W19,W21	M7	=IF(SUM(D6:L6)>0, SUM(D7:L7),"")