Need formula that repeats a value from above when "1" is shown

[mcdanijt]

I get an export out of our scheduling system and need to aggregate the raw materials needed. I need a formula that, when "1" or blank is shown, it will give the next value above that is not a "1" or blank

See below for my data set and what is needed

How my data exports Column I need to create with a formula

How my Data exports	Column I need to create with Formula
A	A
1	A
	A
B	B
1	B
	B
C	C
1	C
1	C
1	C
	C
D	D
1	D
1	D
1	D
	D
E	E
1	E
	E

<tbody>
</tbody>

 

[Scott Huish]

Assuming your non 1 or blank values are text as in your sample:

=LOOKUP(REPT("z",255),$A$1:A1)

Copy down.

Excel 2010
	A	B
1	A	A
2	1	A
3		A
4	B	B
5	1	B
6		B
7	C	C
8	1	C
9	1	C
10	1	C
11		C
12	D	D
13	1	D
14	1	D
15	1	D
16		D
17	E	E
18	1	E
Sheet1

Cell Formulas
Range	Formula
B1	=LOOKUP(REPT("z",255),$A$1:A1)
B2	=LOOKUP(REPT("z",255),$A$1:A2)
B3	=LOOKUP(REPT("z",255),$A$1:A3)
B4	=LOOKUP(REPT("z",255),$A$1:A4)
B5	=LOOKUP(REPT("z",255),$A$1:A5)
B6	=LOOKUP(REPT("z",255),$A$1:A6)
B7	=LOOKUP(REPT("z",255),$A$1:A7)
B8	=LOOKUP(REPT("z",255),$A$1:A8)
B9	=LOOKUP(REPT("z",255),$A$1:A9)
B10	=LOOKUP(REPT("z",255),$A$1:A10)
B11	=LOOKUP(REPT("z",255),$A$1:A11)
B12	=LOOKUP(REPT("z",255),$A$1:A12)
B13	=LOOKUP(REPT("z",255),$A$1:A13)
B14	=LOOKUP(REPT("z",255),$A$1:A14)
B15	=LOOKUP(REPT("z",255),$A$1:A15)
B16	=LOOKUP(REPT("z",255),$A$1:A16)
B17	=LOOKUP(REPT("z",255),$A$1:A17)
B18	=LOOKUP(REPT("z",255),$A$1:A18)

 

[mcdanijt]

Thank you this worked perfectly