Creating table from data in columns

Hello all,

I am really sorry if this was already discussed in previous threads, but I am straggling to find out how to name this sort of problem I have (if so, please send a link to relevant thread).

I do have information in 3 different columns (Name, department, position) and I have to create a table from these data, where rows will show department, column will show positions and values inside the table will be names (divided there based on dep. and position). Of course there can be more people with same position in same department so I would need table to show more cells for that combinations of DEPxPOS in this case.

I would really appreciate your help here. Thank you.
Maros

Excel attached

This would work if there was only one name for specific DEPxPOS combination. This way excel just picked up last name from that list for combination where there are more of them. For example, for Associate from Finance, excel picked up only Erik, but I need all names for each department, so in this case Anna and Erik. Another example is Weld Associate, now I have only Robert, but in reality Jack, Jasmine and Robert are working there.

Sorry, I thought it will be clear from my first post, but maybe I haven't explained it correctly. I attached new excel sheet with expected results in red, I am looking for some formula (if it is even possible) which will help me to get this result. If it is not possible to get more names in one cell, maybe there is a way to do it with multiple cells for one POSITIONxDEPARTMENT combination?

Sorry my bad

give the attached a try - macro on green button

Hi, that works great! Of course, this was only fictional data and for our purposes we will have to extend range of data (now it is working till row 23) and also add new positions an departments. Is there any way we can manage it by ourselves?