Macro to find 2 lowest scores and sort?

[bjbalmforth]

Here's the problem, players play over 3 rounds but their lowest 2 scores count, EG M Smith row 2 scores of 51 & 36 would be counted.

The other data in cells to the right of score is added to other round information EG, H2+m2, I2+N2, J2+O2, K2+P2.

Once this has been done the information needs to be sorted from the combined data in the order from left to right

Some players may only play 2 rounds thus both the scores would be used,EG it could be from rounds 1 & 3.

I would be guessing that this information would need to be put into columns right of the current data and sorted from there.

I Know this sounds a bit confusing, but it is hard to explain, I hope the table helps solve some of the confusion.

Stableford Social Golf Club.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Names	Round 1		PLAYED 28-10-2005			Round 2		Played 10/12/2005			Round 3		Played 12/03/2006
2	R Walker	78	34	4	3	4	82	36	4	4	4	73	36	4	4	4
3	F Fontane	80	34	3	4	4	80	36	4	4	4
4	R Jones	82	37	3	3	5	84	39	4	4	4	72	36	4	4	4
5	M Smith	82	41	3	4	5	71	36	4	4	4	56	27	3	3	3
6	R Tallon	101	48	3	3	6						73	36	4	4	4
7	Barry J	102	53	4	4	7	93	45	5	5	5	100	46	4	4	4
8	P Dye	111	39	3	3	7						84	39	4	4	4
B Grade Final Gross Results

 

[Domenic]

Try the following...

1) Define the following references...

Insert > Name > Define

Name: Array1

Refers to:

=(Col_1)+(Col_2)+(Col_3)+(Col_4)+(Col_5)

Click Add

Name: Array2

Refers to:

=SMALL(IF(Array1>0,Array1),Col_B)

Click Add

Name: Array3

Refers to:

=MATCH(Array2,Array1,0)*5-5

Click Add

Name: Col_1

Refers to:

=SUBTOTAL(9,OFFSET(Range,,Col_A,,1))*10^9

Click Add

Name: Col_2

Refers to:

=SUBTOTAL(9,OFFSET(Range,,Col_A+1,,1))*10^6

Click Add

Name: Col_3

Refers to:

=SUBTOTAL(9,OFFSET(Range,,Col_A+2,,1))*10^3

Click Add

Name: Col_4

Refers to:

=SUBTOTAL(9,OFFSET(Range,,Col_A+3,,1))/10^3

Click Add

Name: Col_5

Refers to:

=SUBTOTAL(9,OFFSET(Range,,Col_A+4,,1))/10^6

Click Add

Name: Col_A

Refers to:

=ROW(INDIRECT("1:"&INT(COLUMNS(Range)/5)))*5-5

Click Add

Name: Col_B

Refers to:

=ROW(INDIRECT("1:"&MIN(2,INT(COUNT(Range)/5))))

Click Add

Name: Range

Refers to:

=Sheet1!$B2:INDEX(Sheet1!$B2:$P2,MATCH(9.99999999999999E+307,Sheet1!$B2:$P2))

Click Ok

2) Enter the following formula in Q2, copy across and down:

=IF(COUNT(Range)>=5,SUMPRODUCT(SUBTOTAL(9,OFFSET(B2,,Array3,,1))),"")

Hope this helps!

P.S. I just read your last post. Off hand, I don't think I need to make any changes. But I'll have another look at it. In the meantime, I'll email you a sample file.

 

[Domenic]

Macro only places data after 2 rounds, as people have to play at least 2 rounds, then other data comes after the 3rd round. The Macro searches through the 3 rounds for matches, names must appear at least twice from the 3 rounds.

Since the macro only places the data after 2 rounds, and the other data comes after the 3rd round, I think the following would suffice...

1) Define the following references...

Select/highlight B2

Insert > Name > Define

Name: Array1

Refers to:

=(Col_1)+(Col_2)+(Col_3)+(Col_4)+(Col_5)

Click Add

Name: Array2

Refers to:

=SMALL(IF(Array1>0,Array1),{1,2})

Click Add

Name: Array3

Refers to:

=MATCH(Array2,Array1,0)*5-5

Click Add

Name: Col_1

Refers to:

=SUBTOTAL(9,OFFSET(Range,,Col_A,,1))*10^9

Click Add

Name: Col_2

Refers to:

=SUBTOTAL(9,OFFSET(Range,,Col_A+1,,1))*10^6

Click Add

Name: Col_3

Refers to:

=SUBTOTAL(9,OFFSET(Range,,Col_A+2,,1))*10^3

Click Add

Name: Col_4

Refers to:

=SUBTOTAL(9,OFFSET(Range,,Col_A+3,,1))/10^3

Click Add

Name: Col_5

Refers to:

=SUBTOTAL(9,OFFSET(Range,,Col_A+4,,1))/10^6

Click Add

Name: Col_A

Refers to:

=ROW(INDIRECT("1:"&COLUMNS(Range)/5))*5-5

Click Add

Name: Range

Refers to:

=Sheet1!$B2:$P2

Click Ok

2) Enter the following formula in Q2, copy across and down:

=IF(COUNT(Range)>=10,SUMPRODUCT(SUBTOTAL(9,OFFSET(B2,,Array3,,1))),"")

Hope this helps!