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]

Do I simply change the C4 values to D6 and the U57 to V59 in the formula?

If you applied the CF I gave before you inserted the new column & row you should not need to do anything as the CF formula would automatically adapt.

If you have not already applied the CF then select C6:V59 and apply this CF. See further information below the mini sheet.

Gulfcourse.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
24	Hobbs
25		PTS
26	Keep
27		PTS
28	Kilroy
29		PTS
30	Lincoln		6	6	3	6	6	6	3	4	7	47	6	5	5	5	5	7	6	5	6	50
31		PTS	0	0	2	0	0	0	2	2	0	6	0	0	1	1	1	0	0	0	0	3
32	Martin
33		PTS
34	Moderick
35		PTS
36	Nassar		6	5	2	5	5	5	3	5	6	42	6	3	5	6	5	7	4	3	5	44
37		PTS	0	1	4	1	1	1	2	1	1	12	0	2	1	0	1	0	2	2	1	9
38	Nigro		4	5	4	4	4	6	3	3	6	39	5	4	6	5	6	7	6	4	5	48
39		PTS	2	1	1	2	2	0	2	4	1	15	1	1	0	1	0	0	0	1	1	5
40	Palladino
41		PTS
42	Petro		6	5	5	5	6	5	4	5	7	48	6	3	5	4	5	6	5	3	6	43
43		PTS	0	1	0	1	0	1	1	1	0	5	0	2	1	2	1	1	1	2	0	10
44	Phillips
45		PTS
46	Roderick		4	4	3	5	4	4	3	4	4	35	5	3	5	4	5	5	4	3	5	39
47		PTS	2	2	2	1	2	2	2	2	4	19	1	2	1	2	1	2	2	2	1	14
48	Sepic		6	5	4	4	4	6	3	5	5	42	4	3	5	5	5	6	5	3	4	40
49		PTS	0	1	1	2	2	0	2	1	2	11	2	2	1	1	1	1	1	2	2	13
50	Smigel
51		PTS
52	Theberge		6	4	3	4	4	5	3	4	7	40	4	3	5	5	5	6	5	3	6	42
53		PTS	0	2	2	2	2	1	2	2	0	13	2	2	1	1	1	1	1	2	0	11
54	Wallon		5	5	2	5	5	5	3	5	6	41	6	4	5	5	4	5	5	5	5	44
55		PTS	1	1	4	1	1	1	2	1	1	13	0	1	1	1	2	2	1	0	1	9
56	Zeman
57		PTS
58	Terry
59		PTS
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D6:V59	Expression	=AND(D6=MINIFS(D$6:D$59,D$6:D$59,">0",$B$6:$B$59,"<>"),COUNTIFS(D$6:D$59,D6,$B$6:$B$59,"<>")=1,-D$3,$B6<>"")	text	NO

If you are applying this CF to different rows/columns then select the range in question and then adapt the CF formulas as described below

=AND(D6=MINIFS(D$6:D$59,D$6:D$59,">0",$B$6:$B$59,"<>"),COUNTIFS(D$6:D$59,D6,$B$6:$B$59,"<>")=1,-D$3,$B6<>"")

Where I have 6 should be whatever the first row is of the selected area
59 should be the last row number of selection
D first column letter of selection
B column containing the names
3 is the row with the hole numbers (& "OUT" heading)

 

[Gulfcourse]

Thank you so much, it is very much appreciated. Heading out the door so I'll try it later!
Really appreciate the explanation as well, great learning tool.
Steve

 

[Peter_SSs]

Cheers. Glad to contribute.

 

[Gulfcourse]

I really do appreciate the help and try to find answers myself but come up empty sometimes, like now.
On the first sheet, the one will a row for scores and points for each player, I'd like to clear only the scores after the round.
If I select a range and clear contents it will no longer calculate the points.
I tried F5, Go to Special, Constants, and it selects all the scrore rows but also the names.
I only want to clear the cells, in the rows with the name, that I put scores into for holes 1-9 and 10-18, and have been unable to wrap my pea brain around it.
Thank you so much.

 

[Peter_SSs]

Try this with a copy of your workbook.

1. You probably already have a formula in the "OUT" and "IN" columns but if not try those shown in the mini sheet below
   
   

   Gulfcourse.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
   Sheet1

   Cell Formulas
   Range	Formula
   M6:M12,W6:W12	M6	=IF(COUNT(D6:L6),SUM(D6:L6),"")

   
   
   
2. Unless you already have an AutoFilter set up, select from B5:AA59 (or whatever the last PTS row is) then Data ribbon tab -> AutoFilter. The sheet should then appear with drop-down arrows in row 5 like this.
   
   
   
   
   
   
3. Click the drop-down in column C and remove the tick from the 'PTS' box -> OK. This should now just show you the rows that have names in column B
   
   
4. Select from D6:V58 (row 59 - last row - should now be hidden)
   
   
5. Press F5 -> Special... -> Constants -> OK
   The sheet should now look something like this
   
   
   
   
   
   
6. Press the Delete key
   
   
7. Click the drop-down arrow in column C again and choose (Select All) -> OK

 

[Gulfcourse]

WOW, thank you Peter.
I'm amazed, and appreciative, of your knowledge and hope I'm not overstepping by asking for additional help.
I've found, in many instances, it's all a matter of how a problem is worded to be able to find a solution, and people use many different words to describe the same problem!
You, and the others, have made my life running this league easier, and have saved me from spending more money for headache relief medication!
All the best,
Steve

 

[Peter_SSs]

You're welcome. Glad to help.