What formual to use in order to have tasks written in front of corresponding name

[Hunbat]

Hi everyone,

I have a group work, and I prepared a "list A". First column contains list of tasks that should be performed, and other columns are coloured according to names of group members, showing the task they should do at a specific dates. Now I want to create a "list B" from "list A" where I have first columns with group member names and in other columns containing name of the task that each group member should at a precific date. What formula I can use to take data from "list A" to "list B". Another problem's that one person might perform several at one specific date.

Thanks

[quekbc]

This is quite tedious but I think I've managed to get what you want. I needed to create an extra table as a helper section to keep my formula sane.

The helper table basically calculates the number of tasks a particular person has on each date. Then, to generate the list of tasks for each person, I used...

=IF(C18=0,"N/A",INDEX('List A'!$A$7:$A$26,MATCH(SMALL(INDEX((('List A'!B$7:B$26<>$B10)*1000)+ROW('List A'!B$7:B$26),0),1),INDEX(ROW('List A'!B$7:B$26),0),0))) &

IF(C18>1,", " & INDEX('List A'!$A$7:$A$26,MATCH(SMALL(INDEX((('List A'!B$7:B$26<>$B10)*1000)+ROW('List A'!B$7:B$26),0),2),INDEX(ROW('List A'!B$7:B$26),0),0)),"") &

IF(C18>2,", " & INDEX('List A'!$A$7:$A$26,MATCH(SMALL(INDEX((('List A'!B$7:B$26<>$B10)*1000)+ROW('List A'!B$7:B$26),0),3),INDEX(ROW('List A'!B$7:B$26),0),0)),"") &

IF(C18>3,", " & INDEX('List A'!$A$7:$A$26,MATCH(SMALL(INDEX((('List A'!B$7:B$26<>$B10)*1000)+ROW('List A'!B$7:B$26),0),4),INDEX(ROW('List A'!B$7:B$26),0),0)),"") &

IF(C18>4,", " & INDEX('List A'!$A$7:$A$26,MATCH(SMALL(INDEX((('List A'!B$7:B$26<>$B10)*1000)+ROW('List A'!B$7:B$26),0),5),INDEX(ROW('List A'!B$7:B$26),0),0)),"") &

IF(C18>5,"...","")

NOTE: I've also capped the number of tasks to 5 to keep the formulas sane, but it shouldn't be too difficult to extend them to higher numbers.

See attached.

[Hunbat]

This is quite tedious but I think I've managed to get what you want. I needed to create an extra table as a helper section to keep my formula sane.

The helper table basically calculates the number of tasks a particular person has on each date. Then, to generate the list of tasks for each person, I used...

=IF(C18=0,"N/A",INDEX('List A'!$A$7:$A$26,MATCH(SMALL(INDEX((('List A'!B$7:B$26<>$B10)*1000)+ROW('List A'!B$7:B$26),0),1),INDEX(ROW('List A'!B$7:B$26),0),0))) &

IF(C18>1,", " & INDEX('List A'!$A$7:$A$26,MATCH(SMALL(INDEX((('List A'!B$7:B$26<>$B10)*1000)+ROW('List A'!B$7:B$26),0),2),INDEX(ROW('List A'!B$7:B$26),0),0)),"") &

IF(C18>2,", " & INDEX('List A'!$A$7:$A$26,MATCH(SMALL(INDEX((('List A'!B$7:B$26<>$B10)*1000)+ROW('List A'!B$7:B$26),0),3),INDEX(ROW('List A'!B$7:B$26),0),0)),"") &

IF(C18>3,", " & INDEX('List A'!$A$7:$A$26,MATCH(SMALL(INDEX((('List A'!B$7:B$26<>$B10)*1000)+ROW('List A'!B$7:B$26),0),4),INDEX(ROW('List A'!B$7:B$26),0),0)),"") &

IF(C18>4,", " & INDEX('List A'!$A$7:$A$26,MATCH(SMALL(INDEX((('List A'!B$7:B$26<>$B10)*1000)+ROW('List A'!B$7:B$26),0),5),INDEX(ROW('List A'!B$7:B$26),0),0)),"") &

IF(C18>5,"...","")

NOTE: I've also capped the number of tasks to 5 to keep the formulas sane, but it shouldn't be too difficult to extend them to higher numbers.

See attached.

Thanks QUEKBC a lot. You did a great work and helped me a lot to progress my work further. Highly appreciate that

[quekbc]

That's great to hear, Hunbat.

One tiny request, can you please set the thread to solved by clicking on Thread Tools above your first post, select "Mark your thread as Solved"