MS Excel / VBA help thread

Ah tidy data

creating that dataset was the best work ive done today

I guess you'd wnat your data like this first

Customer Month Product Type Measure value

You'll have more luck if you do it ^

too many rows to get it into a single column then back into multiple. will load it into tm1 and back out again. on monday.

Also
=DATEVALUE("01-"&SUBSTITUTE(A1,".","-"))

Ah, new page fail

What I'd do here is use PowerQuery to unpivot those Type columns.
https://www.excel-university.com/unpivot-excel-data/
I'm not sure if that's an option looking at your version of Excel though so otherwise you can use this macro.
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/31387513-unpivot-data-without-powerquery-or-macro

Then just pivot on the resulting data

Gather and spread.

most performant

😬

do you mean fastest?

I was making reference to a much earlier point in this very thread.

I was making reference to a previant point in this very thread.

Is it possible to do a 'live' table in Google Sheets? Basically I want to create a massive list of records, send people the link and have them browse and able to sort it by whatever category they choose, then edit out entries as I sell them.

Yep

https://webapps.stackexchange.com/questions/103843/how-do-i-share-a-google-sheet-with-read-only-permissions

Yeah, sharing is the easy bit, but I'm after the field-sortable function of Excel tables. I don't thing GS has quite the same thing. There's something called Pivot Sheets, but that's more of a filtering system I think.

Sheets has both sorting and filtering on columns, in much the same way as Excel. But I don't think it works on mobile unless you have the Sheets app installed. At least, I couldn't get it to work on an existing Sheet that I know has filters, just now.

Oh ok. That's good, thanks. I'll have another look tomorrow

I know it can do it, but trying to find a decent walk through for:
I have multiple files of the same format in one folder.
I want to combine files, then pull the errors out, let me fix them, and then append them back into the table of combined file data. (this i can do in powerquery (no magic just click click click ). it would be nice to fix the errors without manual intervention, but it's probably best I check because the files are generated by humans (fucking stupid humans) and there will be problems.

What I want a walkthrough for is a flow tutorial so that when new files are added to the sharepoint folder, power automate grabs the data from the file and plonks it in the table of data. Which I'll then use for powerbi. Why am I not using powerbi to do this? because i want the table of data separate for other apps to use.

Does this make sense?

Hi all - trying formulate based on colour of cells (which themselves are conditionally formatted), any ideas? i.e list of tasks, when they are overdue formatted red and I want to be able to then pull the number of days overdue to charge client.

I've run =GET.CELL(38, cell) which returns numbers for a couple of colours, but seems to get confused with the conditional formatting. Had a bit of a dig online but cant seem to find anything that helpful.

Any help greatly appreciated!

Hey aggi - just seen this, didnt get a notification for some reason. Thought I had got around it by locking only certain parts but having just looked through its all gone to pot anyway! Yes I think it will be easier to just keep everyone else locked out from now on in...

I want to lookup A1 in column D and return the value from column C - so a negative vlookup.

Match works doesnt it? or xlookup?

My first question here is can you use a helper column?
So the column does :
If this then value else value etc etc

The you have a value you can do something with (like count if cell value is 12246 And then you can apply a format if cell is etc)

I would do a if <today formula to help me get there but there are 3 entities submitting bits by a due date, if overdue the due date is red, if received from all the due date is grey so i need to count the days when it is red if that makes sense? May be worth having a look at coming in from the other side tbf

yeah index match should work?

Index / Match
You use match to find the position of the thing you're looking for, then you use that as the position for index.

First one is vlookup, searching the 1st column and returning the 2nd

Second one is index/match, searching the 2nd column and returning the 1st

1 Attachment

seems too hard :)

am just gonna move C to E and vlookup