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

 

[GorD]

EG M Smith row 2 scores of 51 & 36 would be counted.

>

So how does that information relate to your table?

 

[bjbalmforth]

sory for the confusion, M Smith should read 71 & 56 for total of 127 being cells G & L then the 4 numbers to the right of cells G2 & L2 are also added together to give a total so the totals should read, 127, 63, 7, 7, 7, for M Smith this is his 2 lowest scores fro the 2 rounds.

This process has to be repeated down the sheet for 50 rows.

 

[Domenic]

Here's a formula approach...

1) Define the following references...

Select/highlight B2

Insert > Name > Define

Name: Num

Refers to: =MIN(2,INT(COUNT(Sheet1!$A2:$K2)/5))

Click Add

Name: Row_O

Refers to: =ROW(INDIRECT("1:"&Num))

Click Add

Name: Array1

Refers to:

=SMALL(IF((Sheet1!$B2:$P2<>"")*(MOD(COLUMN(Sheet1!$B2:$P2)-COLUMN(Sheet1!$B2),5)=0),Sheet1!$B2:$P2+COLUMN(Sheet1!$B2:$P2)/10^10),Row_O)

Click Add

Name: Array2

Refers to:

=MATCH(Array1,Sheet1!$B2:$P2+COLUMN(Sheet1!$B2:$P2)/10^10,0)

Click Ok

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

=SUMPRODUCT(SUBTOTAL(9,OFFSET(B2,,Array2-1,,1)))

If it would make it easier for you, I can email you a sample.

Hope this helps!

 

[bjbalmforth]

An example sheet would be great, the only reason that I thought that a macro would be better was that the information was coming in from a macro from other sheets.

When I trying other formula methods on the sheet when the data was put in the sheet the formula was erased when the sheet was next activated.

I will PM my email thanks.

 

[Domenic]

I received your email address and sent a sample file...

 

[Domenic]

After receiving your reply, I'm a little unclear as to how you'd like the lowest two scores calculated. Using the example you posted, which of the following two methods are you looking for?

Method 1

1) The two lowest scores from Columns B, G, and L

2) Ties to be broken by Columns C, H, and M

3) If there's still a tie, then the tie is to be broken by Columns D, I, and N

4) ...and so on until the tie is broken

Method 2

1) The score for each round is totalled

2) The lowest two scores are based on these totals

I'm guessing that you're probably looking to use the first method. Is this correct?

 

[bjbalmforth]

Yes the first method would be better, thankyou again for your time.

 

[Domenic]

Quick question...

Does your macro place the data for a particular round (five columns) all at once or one column at a time. In other words, for example, will you have a situation where data for Columns B, C, and D are entered, but data for Columns E and F come later (not all data for a particular round is entered)?

 

[bjbalmforth]

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.