filter

[billandrew]

Afternoon

Is there a way to filter (smallest to largest) East Region in column D "Sales" without the rows below altered. The Entire row of the East region will be moved accordingly.

[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Region[/TD]
[TD]Code[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]East[/TD]
[TD]w[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]West[/TD]
[TD]s[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]North[/TD]
[TD]d[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]104[/TD]
[TD]South[/TD]
[TD]w[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]105[/TD]
[TD]East[/TD]
[TD]s[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]106[/TD]
[TD]West[/TD]
[TD]d[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]107[/TD]
[TD]North[/TD]
[TD]w[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]108[/TD]
[TD]South[/TD]
[TD]s[/TD]
[TD]230[/TD]
[/TR]
[TR]
[TD]109[/TD]
[TD]East[/TD]
[TD]d[/TD]
[TD]245[/TD]
[/TR]
[TR]
[TD]110[/TD]
[TD]West[/TD]
[TD]d[/TD]
[TD]225[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]North[/TD]
[TD]w[/TD]
[TD]125[/TD]
[/TR]
[TR]
[TD]112[/TD]
[TD]South[/TD]
[TD]d[/TD]
[TD]180[/TD]
[/TR]
</tbody>[/TABLE]



After


[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Region[/TD]
[TD]Code[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]109[/TD]
[TD]East[/TD]
[TD]d[/TD]
[TD]245[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]West[/TD]
[TD]s[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]North[/TD]
[TD]d[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]104[/TD]
[TD]South[/TD]
[TD]w[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]105[/TD]
[TD]East[/TD]
[TD]s[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]106[/TD]
[TD]West[/TD]
[TD]d[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]107[/TD]
[TD]North[/TD]
[TD]w[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]108[/TD]
[TD]South[/TD]
[TD]s[/TD]
[TD]230[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]East[/TD]
[TD]w[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]110[/TD]
[TD]West[/TD]
[TD]d[/TD]
[TD]225[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]North[/TD]
[TD]w[/TD]
[TD]125[/TD]
[/TR]
[TR]
[TD]112[/TD]
[TD]South[/TD]
[TD]d[/TD]
[TD]180[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

I can't think of any built-in tools that can do that. It would probably be possible to create some formulas to do that, but the result would be a different table with the original table remaining intact.

It sounds like you need a VBA macro to sort the East entries, while leaving the other entries alone. A somewhat easier proposition. But how would you want to define your desires? I notice that the South entries did not move, even though the last row could have moved up. Is it always the East region? Or do you want to select which region?

 

[billandrew]

Eric

Yes it would always be the East Region sorted leaving the other entries alone.

 

[Eric W]

Open a copy of your workbook. Press Alt-F11 to open the VBA editor. From the menu, select Insert > Module. In the window that opens, paste this code:

Sub SortEast()
Dim i As Long, j As Long, r As Long, lr As Long, e As Variant
Dim SortArr As Object, arr1 As Variant, arr2 As Variant, EastRows() As Long

' Macro to sort just elements in a table labeled "East", all others remain in place
' https://www.mrexcel.com/forum/excel-questions/1082051-filter.html
' Eric W. 12/29/2018

    lr = Cells(Rows.Count, "A").End(xlUp).Row
    arr1 = Range("A2:D" & lr).Value
    arr2 = Range("A2:D" & lr).Value
    Set SortArr = CreateObject("System.Collections.ArrayList")
    ReDim EastRows(1 To lr)
    r = 0
    
    For i = 1 To UBound(arr1)
        If arr1(i, 2) = "East" Then
            SortArr.Add Format(arr1(i, 4), "0000000000.00000") & Format(i, "0000000")
            r = r + 1
            EastRows(r) = i
        End If
    Next i
    SortArr.Sort
    i = 0
    For Each e In SortArr
        r = Right(e, 6)
        i = i + 1
        For j = 1 To 4
            arr1(EastRows(i), j) = arr2(r, j)
        Next j
    Next e
    
    Range("A2:D" & lr).Value = arr1
End Sub

Change the values in red to match your requirements. Go back to your Excel sheet with the table in it. Press Alt-F8 to open the macro selector, choose SortEast and click Run.

 

[billandrew]

Incredible!

Thank You Eric..

 

[Rick Rothstein]

Here is another macro that you can consider (roughly the same underlying principle as Eric's macro, but more compact)...

[table="width: 500"]
[tr]
	[td]Sub IndividualRegionSort()
  Dim R As Long, Arr As Variant, Parts As Variant, Rws As New Collection
  With CreateObject("System.Collections.ArrayList")
    For R = 2 To Cells(Rows.Count, "B").End(xlUp).Row
      If Cells(R, "B").Value = "[B][COLOR="#FF0000"]East[/COLOR][/B]" Then
        .Add Format(Cells(R, "D").Value, "0000000000.00000|") & Cells(R, "A").Value & "|" & Cells(R, "C").Value
        Rws.Add R
      End If
    Next
    .Sort
    Arr = .ToArray
    For R = 0 To .Count - 1
      Parts = Split(Arr(R), "|")
      Cells(Rws(R + 1), "A").Resize(, 4) = Array(Parts(1), "[B][COLOR="#FF0000"]East[/COLOR][/B]", Parts(2), Val(Parts(0)))
    Next
  End With
End Sub[/td]
[/tr]
[/table]

 

[billandrew]

That is equally incredible.

Thank You Mr. Rothstein.

The values in "Red" East are interchangeable?

If there are addtional columns i assume this can be amended accordingly as well using the delimiter "|" & cells property.

 

[Rick Rothstein]

The values in "Red" East are interchangeable?

Yes, if you want to sort West, just change the two red "East" to "West" (you can do that on Eric's code as well).

If there are addtional columns i assume this can be amended accordingly as well using the delimiter "|" & cells property.

Yes, but you would need to change the Resize from 4 columns to whatever the new column count is and, of course, add the appropriate Parts array element to the Array function call. If you want to add more columns and the above is too cryptic for you, just post back with complete details and I (or someone else) will be happy to modify the code for you.

 

[billandrew]

Thanks again Mr. Rothstein

I'm trying to learn how to fish, hoping to ask for assistance when absolutely necessary.