You are reading a single comment by @aggi and its replies.
Click here to read the full conversation.
-
I've done it using formulas.
Fill in the columns (G&H), append projects and names (F), use unique to pull out unique names and projects (L) and split them (M & N although sheets is missing Textsplit or FilterXML to split text by a delimiter which is what I'd normally use).
Then transpose a sorted unique list of dates (row 11) and use SUMIFS to pull out your numbers (I just put in a random formula for your 1s).
Ultimately though I'd use powerquery. I just did this to test whether the new Excel formulas make this kind of thing a bit easier.
aggi
hugo7
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