A few weeks ago one of our members, Kylie, sent me this question:
“How can I fill column E with the text ‘CURRENT’, if the Position Title in column D matches the first instance of ‘CURRENT’ in column E?”
See example data below; Kylie wants to fill the cells bordered in orange:
Note: I had to desensitise Kylie’s data and the first names that popped into my mind were inspired by the French Open. I hope Federer likes his new position as Business Customer Rep, or is that Bus Customer Rep…either way it’s going to be a big change for him 🙂
Download the Workbook
Enter your email address below to download the sample workbook.
Watch the Video
Fill Down based on Criteria
- Select the cells in column E from the first row of your data to the bottom of your data e.g. E4:E4000, but don’t select the whole of column E, you just want the rows containing data.
- Press CTRL+G to open the Go To dialog box > click the ‘Special’ button at the bottom:
- Select ‘Blanks’ and click OK
This will have selected all blank cells in your data range in column E
- In the formula bar enter this formula (assuming the first cell selected by Go To Special is cell E6, if not adjust cell references accordingly):
Let’s translate the formula into English:
IF the Position Title in D6 is the same as the Position Title in D5, then insert the Current Flag from E5, otherwise leave the cell blank.
- Press CTRL+ENTER to enter the formula in the selected empty cells
- Copy column E and Paste as Values to get rid of the formulas.
Note: if it was possible for Kylie's data to have the Position Title repeated for different employees you could make the formula an IF(AND... like so:
This would also ensure the CURRENT flag was only copied down to the relevant employee.