Macro to find 2 lowest scores and sort?

bjbalmforth

Board Regular
Joined
Jul 21, 2005
Messages
117
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
ABCDEFGHIJKLMNOP
1NamesRound 1PLAYED 28-10-2005Round 2Played 10/12/2005Round 3Played 12/03/2006
2R Walker783443482364447336444
3F Fontane80343448036444
4R Jones823733584394447236444
5M Smith824134571364445627333
6R Tallon101483367336444
7Barry J10253447934555510046444
8P Dye111393378439444
B Grade Final Gross Results
 
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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)?
 
Upvote 0
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.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top