Forum Discussion
If And Formula
Attached is a small example of the data. They are in the same Rows/Columns so when I transfer the formula, I shouldn't have to make any changes.
I need this formula to auto fill down and to the right.
There are some good formula responses here, so let me add a Power Query response.
First, select the data range then use Data > Get & Transform Data > From Table/Range, ensuring that 'My table has headers' is not checked, like this:
After you click OK, the Power Query editor will open:
Use Add Column > Add Custom Column and configure it like this:
if List.Contains({null,0},[Column2]) then [Column1] else null
Which gives you this:
Now select the 'Custom' column and right-click and select Fill > Down to get this:
Now change the data type of Column1 to Date. Click the ABC123 icon in the column header:
This gives you:
Now with Column1 still selected, use Home > Remove Rows > Remove Errors:
Now double-click each column and rename them appropriately:
Now use Home > Close & Load , then create a pivot table:
Now you can restructure the pivot table however you want, or create another summary. Additionally, this query can be re-run whenever there are changes to the source data.