# 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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### 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)

Replies
3
Views
73
Replies
4
Views
219
Replies
3
Views
90
Replies
7
Views
144
Replies
3
Views
160