# Problem getting same high number from previos line

#### mdb231

##### Board Regular
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

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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.

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

</tbody>

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

</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.

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

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

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

Managed to fix it myself this time changed my data base to the A,B,C,D columns

thanks again

Replies
4
Views
260
Replies
3
Views
259
Replies
6
Views
308
Replies
1
Views
200
Replies
11
Views
364

1,203,182
Messages
6,053,974
Members
444,695
Latest member
asiaciara

### 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.

### Which adblocker are you using?

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

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