Problem getting same high number from previos line

[mdb231]

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

 

[Marcelo Branco]

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]

There's probably a more clever way to do it, but this works.

	A	B	C	D
1	Column1	Column2	Column3	Column4
2	Pontefract	0	73	75
3	Pontefract	0.05	72	75
4	Pontefract	6.65	66	75
5	Pontefract	6.55	75	75
6	Pontefract	8.9	69	75
7	Pontefract	2.55	73	75
8	Pontefract	6.05	72	75
9	Pontefract	46.9	49	75
10	Pontefract	0	79	86
11	Pontefract	2.02	82	86
12	Pontefract	0.5	82	86
13	Pontefract	1.25	80	86
14	Pontefract	2	84	86
15	Pontefract	9.02	86	86
16	Pontefract	17.02	77	86
17	Pontefract	38.02	74	86
18	Pontefract	0	60	66
19	Pontefract	0.1	64	66
20	Pontefract	6.1	61	66
21	Pontefract	5.1	61	66
22	Pontefract	19.1	66	66
23	Ascot	0	81	84
24	Ascot	15.37	79	84
25	Ascot	12.1	77	84
26	Ascot	5	78	84
27	Ascot	4.9	83	84
28	Ascot	12.6	78	84
29	Ascot	0.5	84	84
30	Ascot	15.35	83	84
31	Ascot	0	76	85
32	Ascot	2	77	85
33	Ascot	2.75	85	85
34	Ascot	4.75	80	85
35	Ascot	3.5	78	85
36	Ascot	4	77	85
37	Ascot	6.87	85	85
38	Ascot	14.85	77	85
39	Ascot	28.87	83	85
40	Ascot	32	77	85
41	Ascot	33.87	83	85
42		0

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

Worksheet Formulas Cell Formula 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]

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]

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]

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]

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

thanks again