MS Excel / VBA help thread

You are reading a single comment by @NotThamesWater and its replies. Click here to read the full conversation.

•

Chalfie
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.

?

•

NotThamesWater in reply to @Chalfie

COUNTIFS (not COUNTIF) could work using arrays.

They are very expensive fuctions though, and can completely murder spreadsheets.

Match and vlookup could be used with the flag at the being non-exact, but sort order of the data effects the value returned.

About

Avatar for NotThamesWater @NotThamesWater started