MS Excel / VBA help thread

You are reading a single comment by @Chalfie 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?

•

duncs in reply to @Chalfie

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
•

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

Avatar for Chalfie @Chalfie started