MS Excel / VBA help thread

You are reading a single comment by @hugo7 and its replies. Click here to read the full conversation.

•

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:
1. Copy the Project name into the empty cells below to
2. Copy the Individual's name into the empty cells below
3. Change the hours worked to from value to 0.5 or 1 using an IF formula
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

2 Attachments

•

aggi in reply to @hugo7

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

greentricky in reply to @hugo7

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 shortcuts

I don't understand what it is you are trying to do with the hours, what makes it a 1 or 0,5?

About

Avatar for hugo7 @hugo7 started