MS Excel / VBA help thread

Interesting bug.

I tend to use eomonth for this kind of date stuff. Seems to be a bit more robust.

It's not a bug. To put it another way,

MONTH(2) is exactly equivalent to MONTH(DATEVALUE("2 Jan 1900")) = 1
MONTH(45352) is exactly equivalent to MONTH(DATEVALUE("1 Mar 2024")) = 3

January keeps popping up (as per @Chalfie 's link) because small numbers ( 0 < x < 32) passed to MONTH(x) all represent dates in Jan 1900.

Ah yes, I skimmed through and thought the cut off formula was
=MONTH(TODAY()-1)

If you had 4 groups of people, 50ppl in total, with a different number in each group, and you wanted to match them with another person outside the group to meet on a 2wkly basis, how would you randomise it?

My thought is to randomise the order of the people in each group and then have a fixed formula to pair them up.

They'll only give the same result on the first of a month Feb-Dec.

Today being 1st March, that makes sense. Ta.

thought the cut off formula was

=MONTH(TODAY()-1)

This is how I was using it (in part of a longer Concat with Year) and once applying Chalfie's fix it worked.

Properly (well not properly but real random stuff is expensive) random would be pretty easy. I'd probably use randbetween to get your random number and pair on that.

You might end up with the same pair 3 times in a row or something though if you're doing properly random

I wasn't sure what you were going for (previous month or yesterday's month).

I wasn't sure what you were going for (previous month or yesterday's month).

It is prev month. The task is completed first week of the month, using last months data but rarely is done on 1st of the month, albeit that was the case today.

That feeling when you get emailed 40 workbooks of data from the same database to analyse, because someone else somewhere asked for things to be broken down across 10 locations and 4 financial years at the wrong point in the process.

Is there a quick option to combine these into one worksheet? I'm assuming I should save them all into one directory then cast a spell to import & combine everything or something?

Powerquery is the good answer, but there is a bit of a learning curve. If it's going to be a regular occurrence then this is the route I would take.

Free RDB Merge add-in will just paste everything together into one workbook
https://macexcel.com/examples/addins/rdbmerge/
You'll need to remove the headers in the middle of your data (just filter and delete) and obviously you'll run into trouble if the combined data is more than a million lines.

EDIT: Just noticed that is the Mac add-in and he has taken the Windows site down. I've no wonder if Mac add-ins work in Windows (and I assume you're using Windows) but if you need the Windows add-in drop me a PM and I'll send it over.

Thanks, it might be manual task for a quiet day as there's typically <100 rows per workbook by look of it. As it's wirk I doubt our IT policies will allow use of free add-ins.

If they’re all the same format and the data is on the same sheet:
Open ChatGPT
I want to import data from this single location into one book using power query

Thanks, I'll give it a go this way, at least they'll all be in the same folder at worst case.

The IT team at my place was happy with it, but obviously harder to verify without the website existing.

If you google how to combine multiple workbooks you'll get a load of VBA options that are pretty easy.

Thanks, good to know there're other options too.

The get data > from folder solution via chat gpt via @Chalfie worked a treat. So useful.

If you do any amount of data work then it's worth trying to get a bit of a handle on Power Query, it's really powerful for data manipulation.

Chandoo has some decent tutorials on it.
https://chandoo.org/wp/power-query-tutorial/

Chandoo saved my life.

You’ll need to verify you’ve bought in all the data and there’s no errors….

Thanks for this, will take a look too.

Help me out here -
I have to grab codelists from here (https://www.opencodelists.org/codelist/nhsd-primary-care-domain-refsets/cpeptide_cod/20200812/)
the option to download as csv is there - but obvs downloading as csv and opening in excel is crazy and leads to corruption
"just copy and paste from the screen" is not what i want to do because that is fucking wrong.
what are my options?

What's the problem with opening this CSV in Excel? And are you planning to automate?

dates/long numbers being corrupted

Ah yes the numbers are long enough to be interpreted as [edit] floating point numbers. No dates though?
I'd probably write a custom CSV parser in VBA which treats everything as strings by inserting '.
But if you need to do arithmetic on numbers that big, Excel is probably the wrong tool.

They are going to be stored in a sheet as information. The "numbers" are used in building an sql query later. But someone needs to say "yes" "no" to the stuff going in the query.
Make sense?