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

1

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        |
+-------+-----------+-----------+--------------+----------------+----------+-------------+---------------+--------------+```

frnorke

Posted 2019-04-29T18:00:37.867

Reputation: 13

I don’t understand what result you want.  (First of all, you ask “How would I insert a column to the left of the "Week" column named 'Site' …”; I assume that that’s a  rhetorical question, and that you know how to insert columns.)  My best guess is that you want to fill in “05309” in every row, and, again, I assume that you can figure out how to enter the same value in every cell in a column.  And you say “skipping the blank rows between stores”.  AFAICT, you’ve shown us data for *one* store over two months, and I see no blank rows.  Please explain better and show better data.   … (Cont’d) – Scott – 2019-04-29T18:51:54.570

(Cont’d) …   Also, it would be great if you could post your data as text (rather than as a screenshot) as was done here, here, here, here, here and here; use the Format Text as Table or the Plain Text Tables generator site if you want.   … (Cont’d)

– Scott – 2019-04-29T18:51:57.540

(Cont’d) …  Please do not respond in comments; [edit] your question to make it clearer and more complete. – Scott – 2019-04-29T18:51:59.330

Sorry about that. I've tried to update the question with more information and a more direct outcome (what I think the outcome would be). – frnorke – 2019-04-29T19:08:29.750

The original Column B (which becomes Column C when you insert a new Column A) is critical to your question because it contains the Site ID that you want replicated.  The original Column A (which becomes Column B) is important to your question because it shows when you transition into a different site / store (and month).  There’s no real reason to provide sample data for all nine columns.  The first few rows of each store/month, and the rows between the stores/months, are critical to your question.  There’s no reason to provide sample data for every day in four months’ worth of data. – Scott – 2019-04-29T21:59:47.000

(Cont’d) …  You say “I’ve provided what I think the sheet would look like after the transformation.”,  so why do you keep removing the expected results (the new Column A) from your question? – Scott – 2019-04-29T21:59:52.650

Also, when you edit a post, please fill in the ‘‘Comment’’ field with a brief summary / description of what you changed. – Scott – 2019-04-29T22:09:48.780

Answers

0

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.

Scott

Posted 2019-04-29T18:00:37.867

Reputation: 17 653

Scott, thank you. I just have to figure out how to modify the row number to iterate over the data set as the Site: <ID> changes. Can you recommend how to specify a range in the row number so I iterate over the entire 14213 rows of data? – frnorke – 2019-04-29T20:37:57.550

Again, I don’t understand what you’re asking.  I believe that I have given you a *COMPLETE* answer to the question that you asked.  If there’s some part of your problem that you haven’t told me about, please edit your question to describe it.  If you believe that the above answer doesn’t completely solve your problem, explain what needs to be different. Note that I tested my formula on your data set and I got the desired result all the way through, except for Rows 76 and 77 (4/5/2019 and 4/6/2019), where my formula returns “05309” and your data-set has blanks. – Scott – 2019-04-29T20:56:01.663

I apologize. I made a mistake when I entered the example data set. I have 14213 rows of data in the format as the example shows. I have to insert a column to the left of the 'Week' column that is named 'Site' and references the Site: <ID> for each day of the week for that specific Site. I.e. the first Site is 05039 that block of data is from rows 1-38. Then another block of data from the same Site for the month of March that is from 42-76. At row 115, the Site:<ID> changes to 05305. I have to be able to continue through all 14213 rows picking up the Site:<ID> as it changes. – frnorke – 2019-04-29T21:17:23.823

Scott. Thank you for your help and patience with me. I very much appreciate it. Have a great day! I believe I marked it as answered. – frnorke – 2019-04-30T13:14:31.033

0

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:

enter image description here

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:

enter image description here

Robert Paulsen

Posted 2019-04-29T18:00:37.867

Reputation: 149

(1) This doesn’t produce the result that (I believe) the OP wants.  This is forgivable, since the OP has been evasive about documenting what they want.  (2) But you know that the OP’s real “pages” are not 17 rows long, right?  (3) For that matter, the question does not explicitly state that the worksheet has fixed-length “pages”.  (Although, again, the question does not do a very good job of explaining what the input will look like.  In cases like this, you have to assume that the example is a representative example.) … (Cont’d) – Scott – 2019-04-30T18:29:17.423

(Cont’d) …  (4) What is the value of 6  *  2+5?  Do you see where I’m going with this?  (5) But, nice artwork.  (6) On the other hand, it’s good to at least respect the data in the question.  Using “1”, “2” and “3” as site numbers in your example (when the question has five-digit site numbers, and shows one-digit week numbers in the first column) is confusing.  … (Cont’d) – Scott – 2019-04-30T18:29:19.580

(Cont’d) …  (7) I love examples.  Examples are a great supplement to explanations.  But I have a bit of a pet peeve about people and documents that present examples *instead of* explanations.  If you’re going to explain R1C1 notation, and then you’re going to use a mixed absolute/relative reference in R1C1 notation, you should explain the difference between absolute and relative references in ‭R1C1 notation (and not *just* show an example). – Scott – 2019-04-30T18:29:21.487

0

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).

Mike Honey

Posted 2019-04-29T18:00:37.867

Reputation: 2 119

Asked: 2019-04-29T18:00:37.867

Viewed: 73 times

Active: 2019-05-03T01:27:25.423