Excel - how to get name in matrix

[Ken]

Hi All,

Want to convert below Namelist in matrix form
Tab: NameList
[pre]Column A Column B
Names Status
Derek Agent
Acton Team Lead
Ukrit Manager
Eamon Agent
Earl Agent[/pre]

Tab: Matrix
how to convert in matrix as
[pre]Column B Column C Column D
Agent Team Lead Manager
Derek Acton Ukrit
Eamon
Earl[/pre]

formula not working =INDEX(NameList!A$2:A$5,MATCH(Matrix!B$2,NameList!B$2:B$621,1))

Error: Under Agent column 'Derek' appears in all 3 rows
How to get each name in status column ?

Thanks,

Techit

 

[SkipVought]

hi,

Name the ranges using the names in the TOP row. Formulas > Defined Names > Create from Selection. Also SORT your table on Status.

In A2 in Matrix sheet
[tt]
A2: =IFERROR(INDEX(OFFSET(Names,MATCH(A$1,Status,0)-1, 0, COUNTIF(Status,A$1),1),ROW()-1,1),"")
[/tt]

Copy cell A2 ACROSS & DOWN.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Ken]

Hi Skip,

Thanks it's working. Also found out another problem that some agents appear twice.

[pre]E.g.
Derek Agent
Derek Agent
Eamont Agent
Eamont Agent[/pre]

Currently using pivot to identify duplicates and deleting. Wonder if we can add in the same formula too.

Thanks,

TechIt

 

[SkipVought]

Use the Remove Duplicates feature in the Data Tab of the Ribbon.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}