# Excel Data Sorting Problem using Index & Match Function

#### itsmekarak

##### New Member
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?

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### skywriter

##### Well-known Member
I moved the data to the top and then highlighted just the data, B column first, none of the blank rows underneath and then sorted A to Z. It works fine.

#### itsmekarak

##### New Member
Thank you kindly for your quick response. Unfortunately, I forgot to mention that my sorting problem is actually part of a large macro created to sort a much larger and more complex file. I took all that out so it wouldn't confuse everyone. Sorry!

Having said that, the problem with your solution is the human element of seeing which of the 25 rows need to be sorted and choosing only those rows. The macro doesn't have the ability to sort only rows with data. It's a set range of: A4:B28. Any ideas on how to work around this?

#### skywriter

##### Well-known Member
Thank you kindly for your quick response. Unfortunately, I forgot to mention that my sorting problem is actually part of a large macro created to sort a much larger and more complex file. I took all that out so it wouldn't confuse everyone. Sorry!

Having said that, the problem with your solution is the human element of seeing which of the 25 rows need to be sorted and choosing only those rows. The macro doesn't have the ability to sort only rows with data. It's a set range of: A4:B28. Any ideas on how to work around this?

Not sure what you're looking for. You mention you left the macro out so as not to confuse, but then you are talking about the sorting ability of the macro.
If you are looking for help in coding your macro so that it sorts correctly you must include the macro code.
If this is not the case I think you should state the issue in more detail unless it's clear by your post what you want and I'm just brain dead today.

#### itsmekarak

##### New Member

Since the problem existed outside of the macro I assumed the macro wasn't the issue - but I may be wrong. In an effort to not complicate my request, it appears as though I've complicated my request. Therefore, no more shortcuts. Here's the actual macro enabled file I'm dealing with http://www.jowdy.com/uploads/Kara/Game_Day_DCR.zip If you run the one and only macro in the workbook "AL_DCR_Sort" you will see the names listed in column J & K shift to the bottom as explained in my initial email. How do I keep the data from shifting to the bottom during the "sort" portion of my macro?

#### skywriter

##### Well-known Member
Since the problem existed outside of the macro I assumed the macro wasn't the issue - but I may be wrong. In an effort to not complicate my request, it appears as though I've complicated my request. Therefore, no more shortcuts. Here's the actual macro enabled file I'm dealing with http://www.jowdy.com/uploads/Kara/Game_Day_DCR.zip If you run the one and only macro in the workbook "AL_DCR_Sort" you will see the names listed in column J & K shift to the bottom as explained in my initial email. How do I keep the data from shifting to the bottom during the "sort" portion of my macro?

The problem is in your macro. I changed the part of the code that is sorting the area in question.
Make a copy of your spreadsheet, open the spreadsheet delete all the macro code and replace it with the code below. Then run your macro and see what you think.

Code:
``````Sub AL_DCR_Sort()
'
' AL_DCR_Sort Macro
'
Dim LR As Long

LR = Cells(30, 10).End(xlUp).Row

'
Columns("J:L").Select
Range("K2").Activate
Selection.EntireColumn.Hidden = False
Range("Y6:AA39").Select
ActiveSheet.Sort.SortFields.Clear
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveSheet.Sort
.SetRange Range("Y6:AA39")
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.UnMerge
Range("J5:W" & LR).Select
ActiveSheet.Sort.SortFields.Clear
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("J5:W" & LR)
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("M5:O5").Select
Selection.Merge True
Range("M5:O29").Select
Application.CutCopyMode = False
Selection.Merge True
Range("J5:J29").Select
With Selection.Interior
.Pattern = xlNone
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
End With
Range("J34:N44").Select
ActiveSheet.Sort.SortFields.Clear
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("J34:N44")
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("J34:J44").Select
With Selection.Interior
.Pattern = xlNone
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
End With
Range("J49:N52").Select
ActiveSheet.Sort.SortFields.Clear
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("J49:N52")
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("J49:J52").Select
With Selection.Interior
.Pattern = xlNone
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
End With
Range("B3").Select
ActiveWindow.SmallScroll Down:=6
Range("B3:W54").Select
ActiveSheet.PageSetup.PrintArea = "\$B\$3:\$W\$54"
Range("C3:E3").Select

End Sub``````

#### itsmekarak

##### New Member

Bruce,

YOU'RE A GENIUS!!!

It worked perfectly and I can't thank you enough for all your help and patience. You've truly made my day!

#### skywriter

##### Well-known Member
Bruce,

YOU'RE A GENIUS!!!

It worked perfectly and I can't thank you enough for all your help and patience. You've truly made my day!

Okay cool!!! My pleasure.

#### itsmekarak

##### New Member
OK, so I"m back! I went to run the this report to today and discovered the same problem with a few rows down (J34:N44) and then again on the third set of (J49:N52). I see how you applied the LR = Cells (30,10)... but how do I do the same thing for different references (45,10) and (53,10)?

#### itsmekarak

##### New Member
Hello all,

Using the latest spreadsheet listed in my prior reply post on Oct 1st...

When I went to sort the group (J4:W29) using column "K" data in ascending order, it shifted all the cells with data down to the bottom of the 25 available rows leaving all the blank rows at the top. I posted this problem and was told the problem was with my macro.

The macro was “repaired” (see below) and sent back to me working very well with one small problem….

The fix only “fixed” the top section of data (J5:W29).

NEED: Can anyone help me fix the macro to
extend the same functionality fixed in the top data group to the second and third sections as well? (J34:N44) and (J49:N52)

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,651
Messages
5,838,577
Members
430,557
Latest member
MK15

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back