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!

 

[skywriter]

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]

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]

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]

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]

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.

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
    ActiveSheet.Sort.SortFields.Add Key:=Range("Y6"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveSheet.Sort
        .SetRange Range("Y6:AA39")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.UnMerge
    Range("J5:W" & LR).Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("K5:K29"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("J5:W" & LR)
        .Header = xlGuess
        .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
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Range("J34:N44").Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("K34:K50"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("J34:N44")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("J34:J44").Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
        Range("J49:N52").Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("K34:K50"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("J49:N52")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("J49:J52").Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    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]

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]

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]

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]

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)

Any and all help you can provide is appreciated!