Formula to help transpose horizontal agent result to vertical columns

[mrteater]

I am looking for help with a Formula to help transpose horizontal agent result to vertical columns. I have hundreds of lines of data for agents. Each agent has a line of data that stretches about 246 columns when exported. I'm looking for a formula so I can paste the data on the 'Skills Data' tab and it will update the teams columns with the corresponding values on the 'Teams Skills' tab. then I can replicate this for the 20+ teams I have.

Document attached with 2 examples of what the results should look like.

[AliGW]

Try this and copy to every other column:

=WRAPROWS(FILTER('Skills data'!$I$2:$AB$9,'Skills data'!$C$2:$C$9=C$2),2)

You'll need to clear the results table first. You may need to sign up to the Office Insider Beta Channel to access the WRAPROWS function.

[shukla.ankur281190]

Try

C4

Please Login or Register  to view this content.

Copy across

[HansDouwe]

Please try in C18 and copy down and across:

Please Login or Register  to view this content.

Please try in C16 and copy across:

Please Login or Register  to view this content.

[Glenn Kennedy]

Delete ALL expecte3d answers. or:

=LET(d,'Skills data'!I2:AD9,INDEX(d,INT((ROW(d)-1)),COLUMN(d)-MIN(COLUMN(d))+1))

in C4. Formula spills out and there's no need for Office Insider.

[AliGW]

@Glenn

Will that work if the order of the agents were to change?

Actually, it doesn't seem to pull the data required.

Required:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	C	D
3	Client	Skill
4	Company 1	4
5	Company 4	3
6	Company 7	2
7	Company 5	1
8	Company 6	4
9	Company 8	4
10	Company 10	4
11	Company 2	2
12	Company 9	4
13	Company 3	4

Sheet: Team Skills

Your option:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	C	D
15	Company 1	4
16	Company 5	2
17	Company 7	1
18	Company 6	2
19	Company 7	1
20	Company 2	1
21	Company 8	2
22	Company 3	2

Sheet: Team Skills

Maybe I'm missing something?

[mrteater]

AliGW when replicating this to multiple tabs and expanding the ranges anything specific I need to do? I'm running into #SPILL! errors

[mrteater]

HansD when I expand this option to multiple team tabs the team members will be static. Is there a way to update your formula so it does not pull for the order of the agent names from the 'Skills data' tab? We can auto populate the agent name in that cell then the data will search for the match on the 'Skills data' tab

[AliGW]

AliGW when replicating this to multiple tabs and expanding the ranges anything specific I need to do? I'm running into #SPILL! errors

Make sure there is nothing below and to the right preventing it from spilling correctly.

[mrteater]

Thank you-- I'm golden.