Problem getting same high number from previos line

mdb231

Board Regular
Joined
May 16, 2006
Messages
73
Hi All again another problem
I have a large data base and to save time I need to get an infill for the highest number
within a set line.I can do fill in each race but that takes time hopeing you can tell me how to do it.
In column 2 from zero down to the next zero i'd like to put the highest number in column3 filled down in column 4 till the next zero
then the highest number from the next zero to next and so on.
Column1 Column2 Column3 Column4
Pontefract 0 73 75
Pontefract 0.05 72 75
Pontefract 6.65 66 75
Pontefract 6.55 75 75
Pontefract 8.9 69 75
Pontefract 2.55 73 75
Pontefract 6.05 72 75
Pontefract 46.9 49 75
Pontefract 0 79 86
Pontefract 2.02 82 86
Pontefract 0.5 82 86
Pontefract 1.25 80 86
Pontefract 2 84 86
Pontefract 9.02 86 86
Pontefract 17.02 77 86
Pontefract 38.02 74 86
Pontefract 0 60
Pontefract 0.1 64
Pontefract 6.1 61 66
Pontefract 5.1 61
Pontefract 19.1 66
Ascot 0 81 84
Ascot 15.37 79
Ascot 12.1 77
Ascot 5 78
Ascot 4.9 83
Ascot 12.6 78
Ascot 0.5 84
Ascot 15.35 83
Ascot 0 76 85
Ascot 2 77
Ascot 2.75 85
Ascot 4.75 80
Ascot 3.5 78
Ascot 4 77
Ascot 6.87 85
Ascot 14.85 77
Ascot 28.87 83
Ascot 32 77
Ascot 33.87 83

I hope that explains it all fingers crossed
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
Hi,

Assuming your data in columns A, B, C beginning in row 1, maybe this array formula in D1

=MAX(IF(COUNTIF(OFFSET($B$1,,,ROW($B$1:$B$1000)),0)=COUNTIF($B$1:B1,0),$C$1:$C$1000))

confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

copy down

M.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,378
There's probably a more clever way to do it, but this works.

ABCD
1Column1Column2Column3Column4
2Pontefract07375
3Pontefract0.057275
4Pontefract6.656675
5Pontefract6.557575
6Pontefract8.96975
7Pontefract2.557375
8Pontefract6.057275
9Pontefract46.94975
10Pontefract07986
11Pontefract2.028286
12Pontefract0.58286
13Pontefract1.258086
14Pontefract28486
15Pontefract9.028686
16Pontefract17.027786
17Pontefract38.027486
18Pontefract06066
19Pontefract0.16466
20Pontefract6.16166
21Pontefract5.16166
22Pontefract19.16666
23Ascot08184
24Ascot15.377984
25Ascot12.17784
26Ascot57884
27Ascot4.98384
28Ascot12.67884
29Ascot0.58484
30Ascot15.358384
31Ascot07685
32Ascot27785
33Ascot2.758585
34Ascot4.758085
35Ascot3.57885
36Ascot47785
37Ascot6.878585
38Ascot14.857785
39Ascot28.878385
40Ascot327785
41Ascot33.878385
420

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>


Worksheet Formulas
CellFormula
D2=IF(B2=0,MAX(OFFSET(B2,0,1,MATCH(0,B3:$B$10000,0))),D1)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Copy the formula down column D for each entry. The last cell in column B needs to be a zero to get the last max.
 

mdb231

Board Regular
Joined
May 16, 2006
Messages
73
Thank you kindly will try them out when I get home after work ,I'm sure they will do the job,that's saved me a goog days work of imputs.
Thanks again
 

mdb231

Board Regular
Joined
May 16, 2006
Messages
73

ADVERTISEMENT

Well it's not working as it's not in ABCD I did that to try to make it easier the actual columns are,
the B column is U
the C column is in AL
the D column is in AN

What parts do I have to change from the ones above.

Thanks
 

mdb231

Board Regular
Joined
May 16, 2006
Messages
73
Well it's not working as it's not in ABCD I did that to try to make it easier the actual columns are,
the B column is U
the C column is in AL
the D column is in AN

What parts do I have to change from the ones above.

Thanks
 

mdb231

Board Regular
Joined
May 16, 2006
Messages
73
Managed to fix it myself this time changed my data base to the A,B,C,D columns

thanks again :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,561
Messages
5,596,853
Members
414,107
Latest member
Tigretto

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
Top