Excel – Set cell values in a column based on another cell value in intervals

Question

I have a sheet with datasets for weekly hours by store. They are all in one sheet separated by store (see the first Site: ‘ID’ number in the first row, third column).  Currently, in my file, the first column is blank.  How can I fill it in with the Site: ‘ID’ number corresponding to the row, i.e., the Site number for the current store, and skip the rows that between stores that have the Site: ‘ID’, Period: ‘NUM’ and Year: ‘YEAR’.

I've provided what I want the sheet to look like after the transformation.

What would the formula be that would allow me to "fill down" each row that inserts the Site: ‘ID’ and the skips the two rows, inserts the text 'Site' and then the corresponding Site: ‘ID’.

Here's a sample of the data-set:

+-------+-----------+-----------+--------------+----------------+----------+-------------+---------------+--------------+
| Site: |   05309   |           |   Period:    |       3        |          |    Year:    |     2019      |              |
+-------+-----------+-----------+--------------+----------------+----------+-------------+---------------+--------------+
|       |           |           |              |                |          |             |               |              |
| Week  | Date      | Day       | WeekOriginal | WeekForecasted | DailyAvg | DayOriginal | DayForecasted | DayScheduled |
| 1     | 4/7/2019  | Sunday    | 252.61       | 357.00         | 10.60%   | 26.78       | 37.84         | 37.00        |
| 1     | 4/8/2019  | Monday    | 252.61       | 357.00         | 13.40%   | 33.84       | 47.82         | 58.50        |
| 1     | 4/9/2019  | Tuesday   | 252.61       | 357.00         | 13.60%   | 34.35       | 48.54         | 52.00        |
| 1     | 4/10/2019 | Wednesday | 252.61       | 357.00         | 15.16%   | 38.30       | 54.12         | 63.00        |
| 1     | 4/11/2019 | Thursday  | 252.61       | 357.00         | 15.31%   | 38.69       | 54.67         | 50.00        |
| 1     | 4/12/2019 | Friday    | 252.61       | 357.00         | 17.87%   | 45.15       | 63.80         | 52.00        |
| 1     | 4/13/2019 | Saturday  | 252.61       | 357.00         | 14.06%   | 35.52       | 50.19         | 39.00        |
| 2     | 4/14/2019 | Sunday    | 252.71       | 357.00         | 10.60%   | 26.79       | 37.84         | 30.00        |
| 2     | 4/15/2019 | Monday    | 252.71       | 357.00         | 13.40%   | 33.85       | 47.82         | 52.50        |
| 2     | 4/16/2019 | Tuesday   | 252.71       | 357.00         | 13.60%   | 34.36       | 48.54         | 54.50        |
| 2     | 4/17/2019 | Wednesday | 252.71       | 357.00         | 15.16%   | 38.31       | 54.12         | 64.00        |
| 2     | 4/18/2019 | Thursday  | 252.71       | 357.00         | 15.31%   | 38.70       | 54.67         | 63.00        |
| 2     | 4/19/2019 | Friday    | 252.71       | 357.00         | 17.87%   | 45.16       | 63.80         | 65.00        |
| 2     | 4/20/2019 | Saturday  | 252.71       | 357.00         | 14.06%   | 35.53       | 50.19         | 31.00        |
| 3     | 4/21/2019 | Sunday    | 269.28       | 357.00         | 10.60%   | 28.54       | 37.84         | 29.00        |
| 3     | 4/22/2019 | Monday    | 269.28       | 357.00         | 13.40%   | 36.07       | 47.82         | 60.50        |
| 3     | 4/23/2019 | Tuesday   | 269.28       | 357.00         | 13.60%   | 36.62       | 48.54         | 59.50        |
| 3     | 4/24/2019 | Wednesday | 269.28       | 357.00         | 15.16%   | 40.82       | 54.12         | 63.25        |
| 3     | 4/25/2019 | Thursday  | 269.28       | 357.00         | 15.31%   | 41.24       | 54.67         | 58.50        |
| 3     | 4/26/2019 | Friday    | 269.28       | 357.00         | 17.87%   | 48.12       | 63.80         | 63.00        |
| 3     | 4/27/2019 | Saturday  | 269.28       | 357.00         | 14.06%   | 37.86       | 50.19         | 35.50        |
| 4     | 4/28/2019 | Sunday    | 274.62       | 357.00         | 10.60%   | 29.11       | 37.84         | -            |
| 4     | 4/29/2019 | Monday    | 274.62       | 357.00         | 13.40%   | 36.79       | 47.82         | -            |
| 4     | 4/30/2019 | Tuesday   | 274.62       | 357.00         | 13.60%   | 37.34       | 48.54         | -            |
| 4     | 5/1/2019  | Wednesday | 274.62       | 357.00         | 15.16%   | 41.63       | 54.12         | -            |
| 4     | 5/2/2019  | Thursday  | 274.62       | 357.00         | 15.31%   | 42.06       | 54.67         | -            |
| 4     | 5/3/2019  | Friday    | 274.62       | 357.00         | 17.87%   | 49.08       | 63.80         | -            |
| 4     | 5/4/2019  | Saturday  | 274.62       | 357.00         | 14.06%   | 38.61       | 50.19         | -            |
| 5     |           |           |              |                |          |             |               |              |
| 5     |           |           |              |                |          |             |               |              |
| 5     |           |           |              |                |          |             |               |              |
| 5     |           |           |              |                |          |             |               |              |
| 5     |           |           |              |                |          |             |               |              |
| 5     |           |           |              |                |          |             |               |              |
| 5     |           |           |              |                |          |             |               |              |
| Site: | 05309     |           | Period:      | 2              |          | Year:       | 2019          |              |
|       |           |           |              |                |          |             |               |              |
| Week  | Date      | Day       | WeekOriginal | WeekForecasted | DailyAvg | DayOriginal | DayForecasted | DayScheduled |
| 1     | 3/3/2019  | Sunday    | 342.21       | 357.00         | 10.66%   | 36.49       | 38.06         | 27.00        |
| 1     | 3/4/2019  | Monday    | 342.21       | 357.00         | 14.27%   | 48.82       | 50.93         | 59.50        |
| 1     | 3/5/2019  | Tuesday   | 342.21       | 357.00         | 14.41%   | 49.30       | 51.43         | 51.00        |
| 1     | 3/6/2019  | Wednesday | 342.21       | 357.00         | 14.82%   | 50.71       | 52.91         | 48.00        |
| 1     | 3/7/2019  | Thursday  | 342.21       | 357.00         | 15.67%   | 53.64       | 55.96         | 44.00        |
| 1     | 3/8/2019  | Friday    | 342.21       | 357.00         | 17.26%   | 59.07       | 61.62         | 47.00        |
| 1     | 3/9/2019  | Saturday  | 342.21       | 357.00         | 12.91%   | 44.18       | 46.09         | 31.00        |
| 2     | 3/10/2019 | Sunday    | 347.79       | 357.00         | 10.66%   | 37.08       | 38.06         | 27.00        |
| 2     | 3/11/2019 | Monday    | 347.79       | 357.00         | 14.27%   | 49.61       | 50.93         | 65.00        |
| 2     | 3/12/2019 | Tuesday   | 347.79       | 357.00         | 14.41%   | 50.11       | 51.43         | 59.00        |
| 2     | 3/13/2019 | Wednesday | 347.79       | 357.00         | 14.82%   | 51.54       | 52.91         | 59.00        |
| 2     | 3/14/2019 | Thursday  | 347.79       | 357.00         | 15.67%   | 54.51       | 55.96         | 51.00        |
| 2     | 3/15/2019 | Friday    | 347.79       | 357.00         | 17.26%   | 60.03       | 61.62         | 57.00        |
| 2     | 3/16/2019 | Saturday  | 347.79       | 357.00         | 12.91%   | 44.90       | 46.09         | 31.00        |
| 3     | 3/17/2019 | Sunday    | 328.22       | 357.00         | 10.66%   | 34.99       | 38.06         | 27.00        |
| 3     | 3/18/2019 | Monday    | 328.22       | 357.00         | 14.27%   | 46.82       | 50.93         | 60.50        |
| 3     | 3/19/2019 | Tuesday   | 328.22       | 357.00         | 14.41%   | 47.29       | 51.43         | 55.00        |
| 3     | 3/20/2019 | Wednesday | 328.22       | 357.00         | 14.82%   | 48.64       | 52.91         | 53.00        |
| 3     | 3/21/2019 | Thursday  | 328.22       | 357.00         | 15.67%   | 51.45       | 55.96         | 53.50        |
| 3     | 3/22/2019 | Friday    | 328.22       | 357.00         | 17.26%   | 56.65       | 61.62         | 61.00        |
| 3     | 3/23/2019 | Saturday  | 328.22       | 357.00         | 12.91%   | 42.38       | 46.09         | 37.00        |
| 4     | 3/24/2019 | Sunday    | 384.47       | 357.00         | 10.66%   | 40.99       | 38.06         | 34.00        |
| 4     | 3/25/2019 | Monday    | 384.47       | 357.00         | 14.27%   | 54.85       | 50.93         | 61.50        |
| 4     | 3/26/2019 | Tuesday   | 384.47       | 357.00         | 14.41%   | 55.39       | 51.43         | 64.00        |
| 4     | 3/27/2019 | Wednesday | 384.47       | 357.00         | 14.82%   | 56.98       | 52.91         | 72.00        |
| 4     | 3/28/2019 | Thursday  | 384.47       | 357.00         | 15.67%   | 60.26       | 55.96         | 62.50        |
| 4     | 3/29/2019 | Friday    | 384.47       | 357.00         | 17.26%   | 66.36       | 61.62         | 54.50        |
| 4     | 3/30/2019 | Saturday  | 384.47       | 357.00         | 12.91%   | 49.64       | 46.09         | 40.00        |
| 5     | 3/31/2019 | Sunday    | 364.38       | 357.00         | 10.66%   | 38.85       | 38.06         | 16.00        |
| 5     | 4/1/2019  | Monday    | 364.38       | 357.00         | 14.27%   | 51.98       | 50.93         | 3.00         |
| 5     | 4/2/2019  | Tuesday   | 364.38       | 357.00         | 14.41%   | 52.50       | 51.43         | 3.00         |
| 5     | 4/3/2019  | Wednesday | 364.38       | 357.00         | 14.82%   | 54.00       | 52.91         | 3.00         |
| 5     | 4/4/2019  | Thursday  | 364.38       | 357.00         | 15.67%   | 57.11       | 55.96         | 3.00         |
| 5     | 4/5/2019  | Friday    | 364.38       | 357.00         | 17.26%   | 62.89       | 61.62         | 3.00         |
| 5     | 4/6/2019  | Saturday  | 364.38       | 357.00         | 12.91%   | 47.05       | 46.09         | 15.00        |
| Site: | 05309     |           | Period:      | 1              |          | Year:       | 2019          |              |
|       |           |           |              |                |          |             |               |              |
| Week  | Date      | Day       | WeekOriginal | WeekForecasted | DailyAvg | DayOriginal | DayForecasted | DayScheduled |
| 1     | 2/3/2019  | Sunday    | 255.95       | 357.00         | 11.38%   | 29.12       | 40.61         | 14.00        |
| 1     | 2/4/2019  | Monday    | 255.95       | 357.00         | 13.10%   | 33.53       | 46.76         | 43.50        |
| 1     | 2/5/2019  | Tuesday   | 255.95       | 357.00         | 14.98%   | 38.35       | 53.50         | 54.00        |
| 1     | 2/6/2019  | Wednesday | 255.95       | 357.00         | 15.85%   | 40.57       | 56.59         | 48.00        |
| 1     | 2/7/2019  | Thursday  | 255.95       | 357.00         | 15.22%   | 38.96       | 54.34         | 38.00        |
| 1     | 2/8/2019  | Friday    | 255.95       | 357.00         | 16.58%   | 42.43       | 59.18         | 47.00        |
| 1     | 2/9/2019  | Saturday  | 255.95       | 357.00         | 12.89%   | 32.99       | 46.01         | 31.00        |
| 2     | 2/10/2019 | Sunday    | 267.99       | 357.00         | 11.38%   | 30.49       | 40.61         | 29.00        |
| 2     | 2/11/2019 | Monday    | 267.99       | 357.00         | 13.10%   | 35.10       | 46.76         | 58.50        |
| 2     | 2/12/2019 | Tuesday   | 267.99       | 357.00         | 14.98%   | 40.16       | 53.50         | 47.50        |
| 2     | 2/13/2019 | Wednesday | 267.99       | 357.00         | 15.85%   | 42.48       | 56.59         | 66.00        |
| 2     | 2/14/2019 | Thursday  | 267.99       | 357.00         | 15.22%   | 40.79       | 54.34         | 46.50        |
| 2     | 2/15/2019 | Friday    | 267.99       | 357.00         | 16.58%   | 44.43       | 59.18         | 54.50        |
| 2     | 2/16/2019 | Saturday  | 267.99       | 357.00         | 12.89%   | 34.54       | 46.01         | 31.00        |
| 3     | 2/17/2019 | Sunday    | 256.49       | 357.00         | 11.38%   | 29.18       | 40.61         | 26.50        |
| 3     | 2/18/2019 | Monday    | 256.49       | 357.00         | 13.10%   | 33.60       | 46.76         | 26.50        |
| 3     | 2/19/2019 | Tuesday   | 256.49       | 357.00         | 14.98%   | 38.43       | 53.50         | 46.50        |
| 3     | 2/20/2019 | Wednesday | 256.49       | 357.00         | 15.85%   | 40.66       | 56.59         | 52.00        |
| 3     | 2/21/2019 | Thursday  | 256.49       | 357.00         | 15.22%   | 39.04       | 54.34         | 54.50        |
| 3     | 2/22/2019 | Friday    | 256.49       | 357.00         | 16.58%   | 42.52       | 59.18         | 42.50        |
| 3     | 2/23/2019 | Saturday  | 256.49       | 357.00         | 12.89%   | 33.06       | 46.01         | 30.50        |
| 4     | 2/24/2019 | Sunday    | 266.41       | 357.00         | 11.38%   | 30.31       | 40.61         | 27.00        |
| 4     | 2/25/2019 | Monday    | 266.41       | 357.00         | 13.10%   | 34.90       | 46.76         | 56.00        |
| 4     | 2/26/2019 | Tuesday   | 266.41       | 357.00         | 14.98%   | 39.92       | 53.50         | 50.00        |
| 4     | 2/27/2019 | Wednesday | 266.41       | 357.00         | 15.85%   | 42.23       | 56.59         | 55.00        |
| 4     | 2/28/2019 | Thursday  | 266.41       | 357.00         | 15.22%   | 40.55       | 54.34         | 64.00        |
| 4     | 3/1/2019  | Friday    | 266.41       | 357.00         | 16.58%   | 44.17       | 59.18         | 61.00        |
| 4     | 3/2/2019  | Saturday  | 266.41       | 357.00         | 12.89%   | 34.34       | 46.01         | 39.00        |
| 5     |           |           |              |                |          |             |               |              |
| 5     |           |           |              |                |          |             |               |              |
| 5     |           |           |              |                |          |             |               |              |
| 5     |           |           |              |                |          |             |               |              |
| 5     |           |           |              |                |          |             |               |              |
| 5     |           |           |              |                |          |             |               |              |
| 5     |           |           |              |                |          |             |               |              |
| Site: | 05305     |           | Period:      | 3              |          | Year:       | 2019          |              |
|       |           |           |              |                |          |             |               |              |
| Week  | Date      | Day       | WeekOriginal | WeekForecasted | DailyAvg | DayOriginal | DayForecasted | DayScheduled |
| 1     | 4/7/2019  | Sunday    | 50.18        | 80.00          | 0.00%    | -           | -             | -            |
| 1     | 4/8/2019  | Monday    | 50.18        | 80.00          | 19.77%   | 9.92        | 15.82         | 16.00        |
| 1     | 4/9/2019  | Tuesday   | 50.18        | 80.00          | 19.80%   | 9.93        | 15.84         | 16.00        |
| 1     | 4/10/2019 | Wednesday | 50.18        | 80.00          | 20.67%   | 10.37       | 16.53         | 16.00        |
| 1     | 4/11/2019 | Thursday  | 50.18        | 80.00          | 25.40%   | 12.74       | 20.32         | 16.00        |
| 1     | 4/12/2019 | Friday    | 50.18        | 80.00          | 14.36%   | 7.21        | 11.49         | 16.00        |
| 1     | 4/13/2019 | Saturday  | 50.18        | 80.00          | 0.00%    | -           | -             | -            |
| 2     | 4/14/2019 | Sunday    | 55.70        | 80.00          | 0.00%    | -           | -             | -            |
| 2     | 4/15/2019 | Monday    | 55.70        | 80.00          | 19.77%   | 11.01       | 15.82         | 16.00        |
| 2     | 4/16/2019 | Tuesday   | 55.70        | 80.00          | 19.80%   | 11.03       | 15.84         | 16.00        |
| 2     | 4/17/2019 | Wednesday | 55.70        | 80.00          | 20.67%   | 11.51       | 16.53         | 16.00        |
| 2     | 4/18/2019 | Thursday  | 55.70        | 80.00          | 25.40%   | 14.15       | 20.32         | 16.00        |
| 2     | 4/19/2019 | Friday    | 55.70        | 80.00          | 14.36%   | 8.00        | 11.49         | 16.00        |
| 2     | 4/20/2019 | Saturday  | 55.70        | 80.00          | 0.00%    | -           | -             | -            |
| 3     | 4/21/2019 | Sunday    | 52.49        | 80.00          | 0.00%    | -           | -             | -            |
| 3     | 4/22/2019 | Monday    | 52.49        | 80.00          | 19.77%   | 10.38       | 15.82         | 16.00        |
| 3     | 4/23/2019 | Tuesday   | 52.49        | 80.00          | 19.80%   | 10.39       | 15.84         | 16.00        |
| 3     | 4/24/2019 | Wednesday | 52.49        | 80.00          | 20.67%   | 10.85       | 16.53         | 16.00        |
| 3     | 4/25/2019 | Thursday  | 52.49        | 80.00          | 25.40%   | 13.33       | 20.32         | 16.00        |
| 3     | 4/26/2019 | Friday    | 52.49        | 80.00          | 14.36%   | 7.54        | 11.49         | 16.00        |
| 3     | 4/27/2019 | Saturday  | 52.49        | 80.00          | 0.00%    | -           | -             | -            |
| 4     | 4/28/2019 | Sunday    | 61.41        | 80.00          | 0.00%    | -           | -             | -            |
| 4     | 4/29/2019 | Monday    | 61.41        | 80.00          | 19.77%   | 12.14       | 15.82         | 16.00        |
| 4     | 4/30/2019 | Tuesday   | 61.41        | 80.00          | 19.80%   | 12.16       | 15.84         | 16.00        |
| 4     | 5/1/2019  | Wednesday | 61.41        | 80.00          | 20.67%   | 12.69       | 16.53         | 16.00        |
| 4     | 5/2/2019  | Thursday  | 61.41        | 80.00          | 25.40%   | 15.60       | 20.32         | 16.00        |
| 4     | 5/3/2019  | Friday    | 61.41        | 80.00          | 14.36%   | 8.82        | 11.49         | 16.00        |
| 4     | 5/4/2019  | Saturday  | 61.41        | 80.00          | 0.00%    | -           | -             | -            |
| 5     |           |           |              |                |          |             |               |              |
| 5     |           |           |              |                |          |             |               |              |
| 5     |           |           |              |                |          |             |               |              |
| 5     |           |           |              |                |          |             |               |              |
| 5     |           |           |              |                |          |             |               |              |
| 5     |           |           |              |                |          |             |               |              |
| 5     |           |           |              |                |          |             |               |              |
| Site: | 05305     |           | Period:      | 2              |          | Year:       | 2019          |              |
|       |           |           |              |                |          |             |               |              |
| Week  | Date      | Day       | WeekOriginal | WeekForecasted | DailyAvg | DayOriginal | DayForecasted | DayScheduled |
| 1     | 3/3/2019  | Sunday    | 81.38        | 80.00          | 0.00%    | -           | -             | -            |
| 1     | 3/4/2019  | Monday    | 81.38        | 80.00          | 20.24%   | 16.47       | 16.19         | 16.00        |
+-------+-----------+-----------+--------------+----------------+----------+-------------+---------------+--------------+```

Answer 1

Enter =IF(OR(B4="Site:",B4=""), "", IF(B4="Week", "Site", IF(B1="Site:",C1,A3)&"")) into A4 and drag/fill down.

This says,

• If we’re on one of the pre-header rows (i.e., the row that gives the “Site”, “Period” and “Year”, or the totally blank row; i.e., the rows before the “Week” / “Date” / “Day” … header row), display blank.
• If we’re on a header row (that says “Week” / “Date” / “Day”), display “Site”.
• If we’re on the first row of data for this store for this month (i.e., the third row up says “Site:” in Column B), then display the site value from the adjacent cell (i.e., C1), otherwise
• Display the value from the row above (A3), which is the site number for this store, based on the previous bullet.
• The &"" forces the site number to be treated as a string, rather than a number, to stop Excel from discarding the leading zero.

Answer 2

It looks like this date is in pages -- so I'll use the term "Page" for each set of data related to a site.

I assume these pages are always laid out the same (the site number in the header is always in 2nd row and 3rd column). This means we can use MOD() and R1C1 notation to find site value';s cell for each site.

For brevity, I've shortened your dataset.

Before the solution, here are the parts you need to know. (NOTE: All three of these examples return "1" because of the value in C2 in my screenshot):

The INDIRECT() formula allows one to programmatically build a string with a cell reference. For example, we can take the string "C" and join it with the string "2" to get a reference to cell C2:

=INDIRECT("C" & "2")

We can change this to use R1C1 notation (by passing FALSE). In R1C1 notation, the following formula references the cell C2 (R2C3 means Row:2, Column:3)

=INDIRECT("R2C3", FALSE)

The R1C1 notation also allows us to reference cells by relative reference from a location. For example, using the example screenshot below, we can use the following formula to reference cell C2 because it's -2 rows and +2 columns from cell A5:

=INDIRECT("R[-3]C[2]",FALSE)

This is the final formula that you would 'fill down' to get the Site value:

=INDIRECT("R["& 2 - MOD(ROW(),17) &"]C3",FALSE)

Here is a breakdown of the final formula calculates:

Here is the proof-of-concept on a simpler data source. I've added to additional columns to show you how portions of the formula calculate:

Answer 3

I'll just assume you want a "Tidy Data" style result.

For a Power Query solution, I would first add a calculated column Site.1 using this expression:

if [Column1] = "Site:" then [Column1] = "Site:" then [Column2] else null

Then I would convert Site.1 to Text and use Fill Down.

Then I would add another calculated column Site using this expression:

if [Column1] = "Week" then "Site" else [Site.1]

Then I would remove the Site.1 column, and Filter on Column1 to remove "Site:" and null.

Then I would promote headers - now the columns will be named Week, Date etc, and a Site column appears on every row.

Final step is to Filter on Week to remove "Week" (remaining header rows).