Filling a column with text

[rdepuydt]

Is there a way to fill the cells in AA with the value from Z for each change in column A? The formula I have in AA works somewhat except for the first few lines.

Device	FORMULA +15 STOP ADDRESS	OFFICIAL +15 MINUTE STOP ADDRESS
1047-CORY DEMPSEY		OFFICIAL +15 MINUTE STOP ADDRESS
1047-CORY DEMPSEY		OFFICIAL +15 MINUTE STOP ADDRESS
1047-CORY DEMPSEY		OFFICIAL +15 MINUTE STOP ADDRESS
1047-CORY DEMPSEY		OFFICIAL +15 MINUTE STOP ADDRESS
1047-CORY DEMPSEY	7118 Rockland Rd, Stonecrest, GA 30038, USA	7118 Rockland Rd, Stonecrest, GA 30038, USA
1047-CORY DEMPSEY	2615 Pineview Dr, Decatur, GA 30030, USA	2615 Pineview Dr, Decatur, GA 30030, USA
1054-JERRY HARDEMAN		2615 Pineview Dr, Decatur, GA 30030, USA
1054-JERRY HARDEMAN		2615 Pineview Dr, Decatur, GA 30030, USA

 

[Peter Bartholomew]

There are a number of ways of avoiding copying down the running value from the header row.
1. If the dataset has a record number use that to modify the reference to the cell above to be blank by placing it within an IF function.
2. The same as above but using IF(ROW()>2,AA1,"")
3. Test the content of the cell you are addressing IF(LEFT(AA1,8)="OFFICIAL", ...,AA1), to ensure it does not start with "OFFICIAL".
4. Test the location of the cell you are addressing IF(ISREF(AA$1 AA1),"",AA1) using range intersection.

 

[rdepuydt]

But is this copying the appropriate address in column AA based on the value in column A? Those first 5 lines should have the 7118 Rockland address in them.