MS Excel / VBA help thread

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

•

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

•

aggi in reply to @Chalfie
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

About

@aggi started