Excel formula help

Copper Contributor

Hi all - I have a list of 180 offices across the Uk and I have a list of 85 tasks that need to be assigned to to each office for example

 

Office                   Task

Manchester            Task 1

Manchester            Task 2

 

And so on

 

Is there a formula that can do this to save me having to copy and paste the tasks 185 times next to the tasks

2 Replies

@Mark_Pike 

An alternative could be Power Query. In the attached file you can add data to the blue dynamic tables. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

@Mark_Pike 

=LET(location,A2:A181,task,B2:B86,HSTACK(TOCOL(IFNA(EXPAND(location,,COUNTA(task)),location)),TOCOL(IFNA(EXPAND(task,,COUNTA(location)),task),,TRUE)))

 

If you have Office 365 you can apply this formula.