MS Excel / VBA help thread

Bored and running down the clock with those Conditional Formatting Icon Sets they have nowdays.

Not as simple as I thought it would be. Am in danger over going past 5. Ah well, there's always tomorrow.

I'm not sure if I'm going mad, but Excel doesn't seem to be working as I remember.

If I'm in a cell, type in a function, I would normally use a combination typing, arrow keys, mouse & keyboard shortcuts to select other cells

e.g. I would type =SUM( then use the arrow keys (or mouse) to navigate to cell A1, and then use <ctrl>+<shift>+<arrow down> to select a range, and expect it to give me, say A1:A10, then I type the closing bracket, and we're all done.

Instead, it decides that I just wanted to type =SUM(A1) and to select the cells below the cone that I'm editing.

If I done use <ctrl>, and just <shift>+<arrow>, I can select the range the long way (but nobody has time for that).

Even if I select two cells, with <shift>+<arrow>, I can then include the <ctrl> key to select to the bottom of the range. But, again - extra key press

Have I missed some crucial new Excel development (back in 2010), have I missed a standard option, or has my memory completely gone and my muscle memory has gone nuts, and this was always how it worked?

I've never heard of using ++ (the plus key/symbol twice?) to select a range.

What OS is this?

Ah - that would be a forum formatting thing

I'll edit

[Done]

I'm just playing with Xlookup and trying to work out how I do one of my fairly standard formulas on it.

Pretty often I'm doing a two criteria lookup where the data is in an array with headers across the top and side. Previously I combined a vlookup and a match to sort that like this

=VLOOKUP(J1,$D$1:$H$5,MATCH(K1,$D$1:$H$1),0)

but I'm not sure how I'd apply this to an xlookup. Any bright ideas? Illustration is below:

1 Attachment

Makes more sense now. That should still work but I vaguely remember the same issue in the past.

I can't remember quite what fixed it. Have you changed region settings (although I think that gives issues with having to replace , in formulas not this)? Accidentally turned scroll lock or something on (this would be my favourite guess). Turned on lotus compatability?

EDIT: Seems to be acommon issue. Possibly due to add-ins as well https://superuser.com/questions/1274640/ctrl-shift-arrow-not-working-in-excel

due to add-ins

Removing, re-adding and restarting seemed to have fixed it.

Thanks!

I still don't really use Xlookup (I'm an index match guy), but I believe in your case you could use nested xlookups

1 Attachment

Cheers, that works. I guess the second lookup is returning the vertical array and it is picking the match from that which makes sense.

Helllllo!
Why is it that I can map in R, ArcGIS, but! The thing we probably need to get out is a simple excel dashboard with counts and rates and stuff, that people can look at, that data feeds into. AND shows a map at postcode level.

Don't say power bi. It will confuse the end user.
Bing maps are shit right? and the IG about postcode data and Bing is a no go.

Any solutions?
I thought about using the datastore london ward level excel maps, but really we're looking at street level stuff. I've even thought about trying to just do a simple xy chart.

Have you tried 3D Maps. It's pretty neat and easy to use. Works with postcodes, addresses or lat/long
https://support.microsoft.com/en-us/office/get-started-with-power-map-88a28df6-8258-40aa-b5cc-577873fb0f4a

And it doesn't use an external database for anyngeocoding? (Like bing maps for example?)
It'll just put stuff on existing shapefiles , if you've got lat/long or easting / northing?

Power bi

Hand draw every hour

GET IN THE FUCKING SEA.

(is that still a thing?)
(how about cockwombling twatface? are they still edgy swears?)

is that still a thing?

After Bristol,yes.

are they still edgy swears?

Always

4evea2011

Not sure. It works offline but does throw up some error messages so I suspect normally it would try an use an external database.

Yeah. We've gone with square cartograms from london datastore (which was my first preference for simple - problem is their simple and abstract representations of an area which will confuse. but sharing xlsm files? having a map embedded? giving it to people who stll say "i'm not an excel person"?)

MAIN REASON: because i only have excel 13. and they're not enabled in 365 in our environment.

MAIN REASON: because i only have excel 13. and they're not enabled in 365 in our environment.

My daily pain

Main reason for my daily pain:
wanting to do other stuff, but having to do this.

Power query question:

I have a postcode field in table 1
PC

I have 3 postcode fields in table 2
pc1 pc2 pc3

in R i can do an inner join like this
PC-PC1
PC-PC2
PC-PC3
then append the table to get all the matching joins
(no doubt someone will come along and tell me how to do this in a conditional format)

In power query
what's the best way to replicate this?
i can do 3 separate merge then an append, but it seems to be easier to say:
if PC = pc1
then left join
else
is PC = pc2
then left join
else
is PC = pc3
then left join

How?
Finding stuff easily about power query is harder than like....

FUCK ME. I am thick at night.
Take PC column, duplicate, find " ", replace with "".
Take pc_1, duplicate, find " ", replace with ""
join on this.

I didn't know such simple things could be done in the query and started making it difficult for myself.

Trying to pull data from various bookies on an ongoing basis - any ideas on how best to do so for specific game odds? Match odds are fine through power query as 'upcoming matches' will update when refreshed, but im struggling to work out specific game odds without it being super labour intensive. ie I can pull down all the match specific odds for, say, spurs v west ham, but after that match that query is useless. Any ideas? thanks

Do you need multiple bookies? I have used this source in the past http://www.football-data.co.uk/englandm.php but think it only has B365 odds in it

Edit: I’m wrong it has plenty of results, depends if you want in advance though.