Sort while keeping information together

[tigerzen]

Looking to sort the information but it needs to stay within the relevant group. It doesn't matter what order the Unit is in ie it could be Sales first, then Finance and it doesn't matter if the Staff names are not in order so Tom can come before Jack for example. They key thing is that the information is consolidated properly. Required output is in Cols D & E. It would be handy that once it's in the format in columns D & E that if the Unit is moved around that the individual names follow the Unit.

Book4
	A	B	C	D	E
1	Unit	Staff		Unit	Staff
2	HR	Tom		HR	Tom
3		Jan			Jan
4	Finance	Fred			Virgil
5		Sarah			Bert
6	Sales	Jack		Finance	Fred
7		Ken			Sarah
8	HR	Virgil		Sales	Jack
9		Bert			Ken
10	Sales	Ted			Ted
11		Peter			Peter
Sheet1

 

[Joe4]

It would be much easier if you populated the blank cells in the Unit column with the appropriate Unit value.
Then sorting becomes very easy.

You can populate those values very quickly and simply using the method here: Fill in blanks in Excel with value above/below, fill empty cells with 0.

If you really like the look of only showing the Unit on the first entry of each section, you can easily use Conditional Formatting to make the ones appear invisible (by formatting the font color to match the white background color).

 

[tigerzen]

It would be much easier if you populated the blank cells in the Unit column with the appropriate Unit value.
Then sorting becomes very easy.

You can populate those values very quickly and simply using the method here: Fill in blanks in Excel with value above/below, fill empty cells with 0.

If you really like the look of only showing the Unit on the first entry of each section, you can easily use Conditional Formatting to make the ones appear invisible (by formatting the font color to match the white background color).

Thanks Joe, like the conditional formatting tip.

 

[Joe4]

You are welcome.