Forum Discussion

Matt_VDW's avatar
Matt_VDW
Copper Contributor
Aug 23, 2022

Excel 2010 workaround for 365 FILTER & SORT functions.

Windows 10 – 64 bit

MS Office 2010 – Version: 14.0.7268.5000 (32-bit)

 

I am looking for a workaround for the FILTER and SORT functions on 365 so that I can have comparable features in the outdated Excel 2010 my workplace still has.

 

I want selected data extracted from a master sheet into three separate sheets based on certain conditions that will automatically update when the master sheet is updated and sorted into an order if possible.

 

I want selected data from each row extracted from the ‘MASTER’ sheet for each area (Column I of MASTER sheet) into their own respective sheets, however only if Column A from the MASTER sheet (Active) = “Y”.

If possible for each area’s sheet to then be sorted from high>low priority as defined by column B in the MASTER sheet.

 

Master pt1Master pt2

 

 

Columns wanted for each area

 

excel
Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Aug 23, 2022

    Matt_VDW 

    Without dynamic arrays, one of the go-tos is INDEX with a nested SMALL-IF array to determine row positions to pick through relevant rows from the top-down.  It's not recommended for large data sets because the formula would have to be applied to cells in anticipation of data maybe being returned.  Sorting on top of this using an array might be theoretically possible but one might expect many computers to sound like a cement mixer trying to perform the  calculation. Not recommended in 2010.

     

    Here's an example of the INDEX-SMALL array (Ctrl+Shift+Enter to define formula as an array):

     

     

    =IFERROR(INDEX(A$1:A$11,SMALL(IF(Active="Y",ROW(Active)),ROW(A1)),1),"")

     

     

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Aug 23, 2022

    Matt_VDW 

    Sub area()

Dim i As Long
Dim k As Long
Dim l As Long
Dim ws As Worksheet

l = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To l
If Cells(i, 1).Value = "Y" Then

k = Worksheets(Cells(i, 9).Value).Range("A" & Rows.Count).End(xlUp).Row
Range(Cells(i, 1), Cells(i, 18)).Copy Destination:=Worksheets(Cells(i, 9).Value).Cells(k + 1, 1)
      
Else
End If

Next i

For Each ws In Worksheets
ws.Range("A:R").Sort key1:=ws.Range("B1"), order1:=xlAscending, Header:=xlYes
Next

End Sub

    Maybe with this code. In the attached file you can click the cutton in cell T2 to run the macro. I've entered random numbers in columns C-H and J-R for this example.

Resources