• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Filling a column with text

rdepuydt

New Member
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.

DeviceFORMULA +15 STOP ADDRESSOFFICIAL +15 MINUTE STOP ADDRESS
1047-CORY DEMPSEYOFFICIAL +15 MINUTE STOP ADDRESS
1047-CORY DEMPSEYOFFICIAL +15 MINUTE STOP ADDRESS
1047-CORY DEMPSEYOFFICIAL +15 MINUTE STOP ADDRESS
1047-CORY DEMPSEYOFFICIAL +15 MINUTE STOP ADDRESS
1047-CORY DEMPSEY7118 Rockland Rd, Stonecrest, GA 30038, USA7118 Rockland Rd, Stonecrest, GA 30038, USA
1047-CORY DEMPSEY2615 Pineview Dr, Decatur, GA 30030, USA2615 Pineview Dr, Decatur, GA 30030, USA
1054-JERRY HARDEMAN2615 Pineview Dr, Decatur, GA 30030, USA
1054-JERRY HARDEMAN2615 Pineview Dr, Decatur, GA 30030, USA
 

Attachments

  • Renee - SEC TRIPS WITH YARD AND SITE STOP 8.18.20_single day.xlsx
    220.3 KB · Views: 7
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.
 
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.
 
Back
Top