Excel Data Sorting Problem using Index & Match Function

[itsmekarak]

Hello All,

I considered myself a particularly advanced Excel user, that is until now what should be such a simple issue to rectify, has stumped me. File can be downloaded here: http://www.jowdy.com/uploads/Kara/ExcelSortProblem.zip

PROBLEM: When I go to sort column A & B's data using column B data in ascending order, it shifts all the cells with data down to the bottom of the 25 available rows leaving all the blank rows at the top. I'm sure it has to do with the way the Match/Index function is written, but I just don't know how to fix this.

OVERVIEW:
Column A = Staff Member (Range: A4:A28)

Column B = Team they've been assigned (Range: B4:B28)
NOTE: This value is obtained through an Index/Match function:
=IF(ISNA(MATCH(A4,$D$4:$D$28,0)),"",INDEX($E$4:$E$28,MATCH(A4,$D$4:$D$28,0)))

Column D = Staff Listing for data validation list (Range: D4:D28)

Column E = Team Listing for each staff member (Range: E4:E28)


NEED: How do I sort the range (A3:B27) using column B's data alphabetically in ascending order where the data remains at the top of the table?


Any and all help you can provide is appreciated!

 

[krishnap2w2]

How to Sort in Excel


Sorting is used to reorder our data in excel. We can easily reorder the data based on the type of sorting that we choose. We have two methods to sort the data.


Below are the steps to sort the given data in Excel:


1 Select a single cell anywhere in the range that you want to sort.
2 Select Data menu and select Sort from Sort & Filter group.
3 In the Sort by list, select the first column on which you want to sort. (You can click on Add Level to include additional number of columns to sort).
4 In the Sort On list, select the desired field from the drop down (Values, Cell Color, Font Color, or Cell Icon).
5 In the Order list, select the order that you want to apply to the sort operation (A to Z or Z to A for text, lower to higher or higher to lower for numbers).
6 Check the field My data has headers if your data has headers included.
7 Click OK.

 

[itsmekarak]

I appreciate the effort Krishnap2w2, but the issue is within the macro and yet to be solved.

I know how to sort even the most complex sheets of data outside of a macro. Its the sorting of multiple sections within a macro that has me stumped.

Thank you for your efforts anyway.