You are reading a single comment by @greentricky and its replies.
Click here to read the full conversation.
-
Correct answer if a regular task VBA or Powerquery if available
If not regular or lazy to automate
Select your range
Ctrl + G
Select Blanks
click in the formula bar and type = and enter the cell reference of the cell above the highlighted white cell in the range excel has greyed out
ctrl + enter
all your cells are now filled
now select your range again
ctrl + v
Ctrl+Alt+V
That will remove the formulas by pasting as values
Now pivot to your heart is content, will take 30 seconds once you remember the shortcutsI don't understand what it is you are trying to do with the hours, what makes it a 1 or 0,5?
greentricky
I get given an xls extract with a list of names, dates worked and the project they're working on.
The different days people work, and people changes mean where the info appears moves up/down. However the columns are always the same.
It appears vertically(ish) and I need it in a horizontal format. I can do this reasonably easily with a pivot table.
BUT first I have to:
This is fairly time consuming and seems like it could be automated, right?
Rule 1. If cell has a value (ie a name) and cell below is empty then copy and paste value.
Apply to column B
Apply to column C
Rule 2. in column G apply IF formula
Should I be looking at VBA? Will it work?
Cheers.
EG - rows A-G is what I get, I onwards is what I ultimately want:
https://docs.google.com/spreadsheets/d/1S8I1DwLBInGWNBN9yLSOf9h2rxqyhwjlNJlbmeChKag/edit?usp=sharing