MS Excel / VBA help thread

I've started using PowerQuery and obviously any kind of training is not happening. I've discovered that functions are all case sensitive (that took a bit of swearing) but I'm still having to look everything up on the MS website.

Does anyone have a link to a crib sheet for Excel functions and the Power Query M equivalent so I know what I should be looking up. Cheers

I always go to chandoo first
https://chandoo.org/wp/tag/power-query/

A slightly technical question on Power Query/Power Pivot.

We get big csv files, 10,000,000 rows plus, and point PowerPivot at them and do the manipulation in PowerPivot.

Would there be any performance improvement in importing the csv into SQL server and then pointing PowerPivot at that?

just stick it into power BI and do everything in there

Do we have r users in here?

Most of the stuff we're doing is generating pivot tables from the data so PowerPivot is the best option for that (plus the Excel style formulas are much easier to work with).

Pivots of any kind are a hint that you should have used a database from the start.

While importing into SQL Server you should normalize your data model (each field should depend on the key, the whole key and nothing but the key) then add appropriate indexing.

If you use SQL Server 2016, that has R built-in.

Probably true, but we're an accountancy firm with shit IT support who don't do these big jobs often enough to justify training or a member of staff to deal with that side.

Another case of crap source data here.
I have a range of dates formatted like this:
1201

How can I convert these into 01/12 or possibly 01/12/2017?

Is there something a little more elegant than the below?

=RIGHT(A1, 2)&"/"&LEFT(A1, 2)&"/"&B1

(B1 being the year)

=DATE(B1,RIGHT(A1,2),LEFT(A1,2))

This'll store the result as a date, rather than a text string, so you can then date-format it however you like.

you could use LEFT and RIGHT in conjunction with DATE to make an actual date field

=DATE(B1,RIGHT(A1,2),LEFT(A1,2))

woops. too slow.

Thank you! Now, say the date string is at the start longer string also containing a timestamp - Without using the text-to-columns function, how could I extract those 4 date digits?

12011234

i.e. 12:34pm, 1st Dec

I would nest LEFT and RIGHT:

=DATE(B1,RIGHT(LEFT(A1,4),2),LEFT(A1,2))

Or use the text-to-columns, but there's probably another reason why you don't want that.

Or you can use MID instead:

=DATE(B1,MID(A1,3,2),LEFT(A1,2))

does this do anything that mid doesn't? e.g.

=date(b1,left(a1,2),mid(a1,3,2))

Edit: that answers my question. I think you've got the date and month the wrong way round (although the original example isn't that clear looking at it which is probably the issue).

@Pifko it is =Date(year,month,day)

Forgot about MID, that's perfect, thanks again.

Hello. I bet this is doable. I'm faffing around with rotas.

We have a list of dates and names
like so

Date          Name
01/01/17  j
01/02/17 j
01/01/17 b
01/03/17 b

What i want to do is:
find out if someone has booked the same date off as someone.

so i'd want to add another column thus

Date          Name  already taken
01/01/17  j                1
01/02/17 j                 0
01/01/17 b                   1
01/03/17 b                 0

This will be a table on a separate sheet. so as leave is added through the year it expands automatically.
Each month will have it's own rota.

How do I determine if a date has multiple occurences?
in prose:
does 01/01/17 appear below the row it is on or on any of the rows above it?

At the moment, I have a lookup on my rota that looks at the table of agreed leave and tells me who's on leave that day. HOWEVER. THere are some days when a date will have more than one person on leave. Ultimately, i'd like to concatenate the lookups, and return the results in one cell. So that I can say in one cell "j+b are on leave".

Does that make sense?

The count is easy. Countif

Assuming you've got the stuff in the first bit in cells A1-B5 then put this formula in cell C2
=COUNTIF(A:A,A2)-1

Which will count the number of occurrences of the date in A2 across the whole column, then deduct one (i.e. not counting itself)

If you wanted to count just in rows above
=COUNTIF(A$1:A2,A2)-1

And below
=COUNTIF(A2:A$1000,A2)-1

Sounds like a pivot table to me possibly. A pivot with date and person's name in the rows.

Knock out subtotals, etc then in the cell next to the pivot table use something like (pivot is in Column O (date) and P (name))

=IF(O3=O4,P3&"+"&Q4,P3)

to concatenate the names and then use a lookup or Index(Match) (lookup didn't work for some reason) against the dates to return a concatenated list of who is away for each date

hmmm.
I was wondering if it would be around index, match, and offset.

A bit oflooking around shows that it'll be a used defined function or an array formula...

I'm not sure I've explained what I want to do very well.

I have the table agreed_leave

date                  person

01/01/18            a
02/01/18           a
02/01/18           b
03/01/18           b
03/01/18           c
04/01/18           c

I use this table to add agreed leave in when it's been agreed and to track who's taking leave when.

I then have a sheet for each month for a rota. At the moment I have this

Rota

date                 task1  task2   who's on leave
01/01/18           b          c            a
 02/01/18       c         c              a

what it does currently is say
vlookup rota date in agreed_leave date, return the matching name.

There must be a way to return a+b in who's on leave.
something like
match rotadate in agreed_leave date, return row numbers, use row numbers to match index agreed_leave names, return agreed leave_names as concatenation.

?

I don't have a machine in front of my to play with this this week, but arbitrary length concatenations are a problem in excel formulae.

Unless you know you'll never need to concatenate more than N cells, this alone might force you to vba your way around this ...

Something like this might help - I think you want a vlookup to return more than one value from agreed_leave that matches the date:

https://www.extendoffice.com/documents/excel/1979-excel-lookup-value-return-multiple-corresponding-values.html

I think this is what I want to do...
https://www.extendoffice.com/documents/excel/3120-excel-return-multiple-values-separated-by-comma.html

I'll give that a try here at home.

^
that works
quite nicely
Especially if you wrap the function in IFError to remove #value at the end.