Data sort

[Mlee5]

Hi, I have column A that is a list of Team Leaders and column B which is a list of team members. The teams are of various sizes. However, the team leader is only listed in column A next to the first member of their team in column B and the remaining cells in column A are blank, next to team member 2, 3, 4, 5, 6 etc  until you come to the next team leader's name and the first member of their team. Again the 2nd team member in the 2nd team has no team leader next to their name in column A. I want to sort column B into alphabetical order but it will then remove them from their relevant team leader. If I include both column A and B in the sort then it will keep the team leader name only next to the singular team member still. If I try and fill Column A it will only fill with the first team leaders name an override team leaders b, c, d etc. I want to sort column B into Alphabetical order but have a way to confirm who their Team leader is.  

[#2]

@Mlee5 I suggest you get rid of the blank rows and fill the Team Leader names down. Than you can sort col B as desired. See attached.

[#3]

@Riny_van_Eekelen  Hi, I've already consider that but the spreadsheet supplied is only a snapshot of the volume of teams involved. There are over 1000, hence why I didn't just use the CTRL D function for the space under each team leaders name. Thanks for the suggestion though. :0)

[#4]

@Mlee5 

You can speed that up quickly with this method:

https://www.ablebits.com/office-addins-blog/2014/05/02/fill-blanks-excel/

[#5]

@Patrick2788thanks for the advice but I tried it and, whilst it works fantastically on the static data, as soon as I try and sort column B into alphabetical order the team leaders change as the formula suggested just copies the cell above it so when the cell changes it replaces the new corresponding cell also

I.e. Column A - cell 3 = Amanda, cell 4-7  = Blank and cell 9 = Joseph and 10= blank. If I use the first formula cell A4-7 becomes Amanda, and cell A10 becomes Joseph, as each blank cell directly corresponds to the cell above it. The corresponding cells in column B would reflect the first 6 agents are in the Amanda's team and the last two agents are in Joseph's. However, if I was to sort column B into alphabetical order then the first two agents no longer have team managers, and only Cheryl stays in Amanda's team and everyone alphabetically after Jodie is now in Joseph's team. .

The other option appears only possible if I purchase the software which I can't do through my work.

Thanks though as it was a neat thing to learn, just not sufficient for what I need. :) 

[#6 · Accepted solution]

@Mlee5 

Copy/Paste special- values and that sort problem goes away.

[#7]

@Patrick2788. I had to pay about it with it a little as you have to do the first part and then, once completed, do the copy feature and re-paste Everything on top of itself using the paste value feature but it worked. thank you so much. I can't wait to try this out on Monday with the actual Data. You've been amazing. Kind regards.