Please can you help advise whether I'd need to use either VBA or lookup to produce an Excel table which details each employee's department, name, and their available dates?

I have an Excel spreadsheet which lists each employee's department, full name, scheduled tasks and all available dates in 2025.

I need my table to detail each employee's: department, name and available dates (i.e. employee department, employee full name, are available on 12/03/2025 and 14/03/2025)?

An example of what the data looks like:

I'd like the result table to look like this:

Thanks so much.

Please can you help advise whether I'd need to use either VBA or lookup to produce an Excel table which details each employee's department, name, and their available dates?

I have an Excel spreadsheet which lists each employee's department, full name, scheduled tasks and all available dates in 2025.

I need my table to detail each employee's: department, name and available dates (i.e. employee department, employee full name, are available on 12/03/2025 and 14/03/2025)?

An example of what the data looks like:

I'd like the result table to look like this:

Thanks so much.

• excel
• vba
• excel-formula

• 3 FILTER function can be a way. – Black cat Commented Mar 4 at 12:25
• Unfortunately, the original excel spreadsheet with the data is v high traffic and cannot be altered, so filter wouldn't work. I need to list out the available dates for each employee in a separate tab or spreadsheet, as the available dates will be different for each employee. Thank you so much though! – James Commented Mar 4 at 13:12
• 3 Please show us what the data looks like and what the result should look like. Also, share what you have tried. Excel's Filter feature and the FILTER function are not the same. Using the later, you could write a formula like =FILTER(Sheet1!A2:F11, Sheet1!B2:B11="James") which would filter out all rows from A to F that contain James in column B of sheet Sheet1 (that is, if you have MS365). – VBasic2008 Commented Mar 4 at 13:34
• Thank you so much- apologies for the misunderstanding on my part! It'd never even occurred to me to formula - I'd been so distracted by VBA!! I've tried VBA codes for similar issues from Stackexchange/Reddit but the syntax was wrong, Pivot Tables, standard filter feature. I'd need to essentially filter by blank, but need the individual dates for each corresponding blank, hope that makes sense. I do have MS365 An example of what the data looks like: i.sstatic/CMbKI1rk.png I'd like the result table to look like this: i.sstatic/IaVPrTWk.png – James Commented Mar 4 at 14:12
• Without being able to see what your source data looks like, we're pretty much shooting in the dark. If your data has Name|Dept|Task|Date as column headings, then the solution is a simple FILTER function. If its Name|Dept|[Full year's worth of dates] with the tasks filled in under the dates, then it gets more complicated. – Frank Ball Commented Mar 4 at 14:45

1 Answer 1

=LET(d,A1:G18,
L,LAMBDA(x,TOCOL(IFS(DROP(d,1,1)="",x),2)),
GROUPBY(L(DROP(TAKE(d,1),,1)),
        TEXT(L(DROP(TAKE(d,,1),1)),"dd/mm/yyyy"),
        ARRAYTOTEXT,,0))

d is you data including headers. L is a function to flatten input array x in case of empty values in your data excluding headers.

GROUPBY is used on the flattened names and it's flattened dates converted to text. ARRAYTOTEXT combines them per name.

Bonus: you can change "" to for example "Task3" to return the names and dates of people working on a task