2nd lowest of 4, middle of 3, lowest of 2, 1 of 1

[jmk1153]

Hello,

I'm hoping this isn't too complicated but here's what I'm trying to accomplish with no avail.

I have numbers in rows B, C ,D and E. I am trying to write a macro in excel that will populate column F based on the following:

2nd lowest number if there are 4 non-zero numbers, the middle number if 3 non-zero numbers, the lowest of 2 numbers if there are 2 non-zero numbers, 1 of 1 if there is only one non-zero number.

If it's too complicated to consider non-zero numbers, I could always replace 0s with a blank cell beforehand to make that easier.

The one caveat is duplicate numbers would need to count towards the formula i.e. 5/5/5/4 would result in a value of 5, not 4.

5
5
5 - 2nd lowest of 4
4

e.g.

A	B	C	D	E	F - result
Jim	5​	5​	5​	4​	5​
Paige	1​	3​	2​	0​	2​
Joseph	0​	0​	1​	2​	1​
Carl	1​	1​	2​	0​	1​
Kim	4​	3​	2​	1​	2​
Joe	0​	1​	1​	1​	1​
Chase	4​	4​	0​	0​	4​
Paul	0​	3​	0​	0​	3​

 

[JGordon11]

Book2
	A	B	C	D	E	F	G
1		Score1	Score2	Score3	Score4	Result
2	Jim	5	5	5	4	5
3	Paige	1	3	2	0	2
4	Joseph	0	0	1	2	1
5	Carl	1	1	2	0	1
6	Kim	4	3	2	1	2
7	Joe	0	1	1	1	1
8	Chase	4	4	0	0	4
9	Paul	0	3	0	0	3
10	A	5	4	2	1	2
11	B	1	3	4	3	3
12	C	5	3	2	0	3
13	D	4	3	2	2	2
14	E	4	2	2	3	2
15	F	4	5	1	3	3
16	G	4	3	0	3	3
17	H	5	5	1	3	3
18	I	1	1	4	1	1
19	J	5	1	5	1	1
20	K	0	2	5	4	4
21	L	4	0	0	2	2
22	M	5	3	0	5	5
23	N	4	5	1	3	3
24	O	5	4	2	2	2
25
Sheet2

Cell Formulas
Range	Formula
F2:F24	F2	=SMALL(B2:E2,CHOOSE(COUNTIF(B2:E2,0)+1, 2,3,3,4))

 

[kweaver]

Is this it?

Sub jmk1132()
Dim lr, i As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lr
 If WorksheetFunction.CountIf(Range("B" & i & ":E" & i), 0) = 0 Then
   Cells(i, "F") = WorksheetFunction.Small(Range("B" & i & ":E" & i), 2)
 ElseIf WorksheetFunction.CountIf(Range("B" & i & ":E" & i), 0) = 1 Then
 Cells(i, "F") = WorksheetFunction.Large(Range("B" & i & ":E" & i), 2)
 ElseIf WorksheetFunction.CountIf(Range("B" & i & ":E" & i), 0) = 2 Then
   Cells(i, "F") = WorksheetFunction.Small(Range("B" & i & ":E" & i), 3)
 Else
   Cells(i, "F") = WorksheetFunction.Small(Range("B" & i & ":E" & i), 4)
 End If
Next i
End Sub

 

[jmk1153]

Book2
	A	B	C	D	E	F	G
1		Score1	Score2	Score3	Score4	Result
2	Jim	5	5	5	4	5
3	Paige	1	3	2	0	2
4	Joseph	0	0	1	2	1
5	Carl	1	1	2	0	1
6	Kim	4	3	2	1	2
7	Joe	0	1	1	1	1
8	Chase	4	4	0	0	4
9	Paul	0	3	0	0	3
10	A	5	4	2	1	2
11	B	1	3	4	3	3
12	C	5	3	2	0	3
13	D	4	3	2	2	2
14	E	4	2	2	3	2
15	F	4	5	1	3	3
16	G	4	3	0	3	3
17	H	5	5	1	3	3
18	I	1	1	4	1	1
19	J	5	1	5	1	1
20	K	0	2	5	4	4
21	L	4	0	0	2	2
22	M	5	3	0	5	5
23	N	4	5	1	3	3
24	O	5	4	2	2	2
25
Sheet2

Cell Formulas
Range	Formula
F2:F24	F2	=SMALL(B2:E2,CHOOSE(COUNTIF(B2:E2,0)+1, 2,3,3,4))

Thank you. I didn't realize excel function would be able to handle that!

 

[jmk1153]

Is this it?

Sub jmk1132()
Dim lr, i As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lr
 If WorksheetFunction.CountIf(Range("B" & i & ":E" & i), 0) = 0 Then
   Cells(i, "F") = WorksheetFunction.Small(Range("B" & i & ":E" & i), 2)
 ElseIf WorksheetFunction.CountIf(Range("B" & i & ":E" & i), 0) = 1 Then
 Cells(i, "F") = WorksheetFunction.Large(Range("B" & i & ":E" & i), 2)
 ElseIf WorksheetFunction.CountIf(Range("B" & i & ":E" & i), 0) = 2 Then
   Cells(i, "F") = WorksheetFunction.Small(Range("B" & i & ":E" & i), 3)
 Else
   Cells(i, "F") = WorksheetFunction.Small(Range("B" & i & ":E" & i), 4)
 End If
Next i
End Sub

Yes, this code worked like a charm. Thank you for the assistance!