Using XLOOKUP (or some other function) to collect and transpose data based on recurrence

[Walkon]

Good day,
I have a data set that lists employees and their associated departments (Table 1 below). I want to create a new sheet that transposes the data so that the rows are the departments and the columns are the employees (Table 2 below). In the actual data set, there are many more columns in Table 1 and Name and DeptID are not adjacent. IS this possible with XLOOKUP or some other function (pivot table?).

 

[Vogateer]

Assuming you have it setup as a Table via Insert -> Table, you can use the named references, Filter, and Transpose to get it to work:

=TRANSPOSE(FILTER(Table1[Full Name], (Table1[DeptID]=$D3)))

$D3 here would be the first Department, SKE in your table, to the left of Christia Hooper.

 

[Vogateer]

Dragging that formula down gives me this:

 

[Walkon]

Assuming you have it setup as a Table via Insert -> Table, you can use the named references, Filter, and Transpose to get it to work:

=TRANSPOSE(FILTER(Table1[Full Name], (Table1[DeptID]=$D3)))

$D3 here would be the first Department, SKE in your table, to the left of Christia Hooper.

Good afternoon,

Thank-you very much for the quick reply and the solution! This has saved me loads of time and frustration.

All the best,

Walkon

 

[Vogateer]

Good afternoon,

Thank-you very much for the quick reply and the solution! This has saved me loads of time and frustration.

All the best,

Walkon

You're welcome!

Would you mind marking my post above as the answer so that this thread shows up as solved? It also gives me credit for an answer, which is nice.

 

[Walkon]

Happy to. I think I just did that but am new so let me know if it doesn't appear.
Thx