MS Excel / VBA help thread

but i can't show you how to that in sheets.

I'm using excel, but have no practical way of sharing an xls. so just use sheets for this thread to give eg.

To clarify I use the pivot table as it allows me to get all dates as headings with blank values below where applicable. I can then change the blanks to zeros.

I'm open to other suggestions.

No probs. If you don't want to touch your data which comes in a tidy format in G12:J74 right?

So.
First things:
Where does this data come from?

Keep that as your raw data.

Then use power query (https://support.microsoft.com/en-us/office/about-power-query-in-excel-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a)
to find and import your data into a new book or new sheet (probably new book)

Then use pivot columns (https://support.microsoft.com/en-us/office/pivot-columns-power-query-abc9c8da-3be9-44c4-886e-0be331ab387a)
to turn your long data into wide data so that you read it like a human and have it as a calendar.

By using power query as part of your ETL (extract, transform, load) you can add 1000million bonus points to your CV. And also make things a bit more "automatic".

https://cran.r-hub.io/web/packages/fingertipscharts/vignettes/quick_charts.html

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.

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?

Cheers all. I'll go through the responses properly later.

I probably should have explained that the main purpose of reformatting it is so I can compare it against another sheet which should have the same information. Then identify the discrepancies, and work out the cause. The final presentation is in the horizontal format.

The hours need to change to days - ie half days, full days, days off with reason code.

It's not a forever peice of work - it's cross referencing while different systems are merged and errors are still being thrown out due to a mix of factors. Usually there will be 2-5 names (out of 60) with mismatches, so once I identify those people it's quite quick to see where the issues are with their info.

Power query will make the comparison much easier. Load your tables then Power Pivot the two tables using the appropriate common fields..

=IF(C29="",H28,C29)

Cheers this alone makes things faster.

Keen to also keep developing my knowledge so will have a look at power query.

Sounds like I need to get on the YT tutorials then!

I hired a data analyst two years ago who fundamentally changed my understanding of what excel was capable of. I found power query quite intuitive but power pivot more challenging. The real answer to these data questions is to use a direct analysis tool connected to the DB.

I think once all our systems are aligned by early next year (and running smoothly) the system(?) that tracks this will throw out all the data and all sorts of cool things. It's just in the meantime we're stuck with a manual checking function and me making a pretty spread sheet to give someone a monthly snapshot.

Was that from a pivot table? If so just repeat item labels to fill in the gaps.

Plus one year for aligned systems. Fucking IT guys..

Edit.. Fucking data architecture guys

Plumbers.

And they’re undervalued.

I've got a list of products with dates on them (varying different amounts of dates). To make it easier to read I'd like to alternately highlight by the product code. I'm sure you can do this by conditional formatting but I can't remember how. Any suggestions:

2 Attachments

Click on the Conditional Formatting icon
Click on New Rule option
In the New Formatting Rule dialog box, select – “Use a formula to determine which cells to format” option
Enter the following formula in the field:
something like
if a = "A"

https://techcommunity.microsoft.com/t5/excel/conditional-formatting-and-power-query/m-p/1343631

for power query?

Say you wanted to add three text annotations to a chart in excel. How can you do that?
Data labels?
3 x legends?
Don't say text boxes.

^
data labels with custom cell value and each point a new series.

Cheers but I'm looking for something a bit more advanced than that (the real data has shitloads of products) and they'll vary when I refresh. I want it to automatically detect when there is a change in that column.

I've done it before (although I remember it being processor intensive) but cannot remember how.

Edit: This version with a helper column works but I'm sure I had something more stylish

1 Attachment

Yeah, I thought it might be a bit more complicated (based on the power BI and query knowledge).I went with "it's enter it as a formula".

It's a conditional IF formula at the heart of the problem isn't it? And depending on your clauses in that, well. I did something by vba years ago but don't want to return there thank you.
Ultimately, WHY COLOUR?

How do I look up a name in column A of sheet 1 and then return all the values in the row that matches that name in sheet 2?

Annoyingly my long detailed problem statement just got deleted.

I feel like I want vlookup +? Returning some value.

Basically I want to know what cell Bob Smith is in so I can then just quickly = the same values in the row.