Putting Managers underneath areas

Hi All,

Apologies if this is really easy to do, i've been sitting here for hours trying to work this out but have got no where

I have a list of canteen managers and the areas they belong to next to it, for a report i am trying to create either a formula or macro that will take each manager and copy it to the right column for the area. I've attached an example to show you what i'm trying to do.

Does anyone know if you can create a macro that will go to each cell and assign it to the relevant column?

Thank you for all your help in advance,

Sunny

Assuming your Headers are in Row 1
and your data as shown on the example, are in columns A, B, C & D

in C2 enter (as an array Ctrl-Shift-Enter)
=IF(ROWS($1:1)<=COUNTIF($A$2:$A$8,C$1),INDEX($B$2:$B$8,SMALL(IF($A$2:$A$8=C$1,ROW(B$2:B$8)-ROW(B$2)+1),ROWS($1:1))),"")

then copy & paste down column C and across into D (& E if needed for Mrs C)

Your column Headings need to be the same as the data in column A. "District-Mr A" , not "Mr A".