CODEWITHSUNDEEP
excelfilterexcel-formula
weizer
weizer

Reputation: 1127

How to filter rows based on column value without using FILTER function in excel?

enter image description here

Hi all,

I want to use a formula to filter the rows based on the name chosen in cell D2. From what I searched in google, I only can see people using FILTER function which is very easy. However, FILTER function is only available if we subscribe to 365 office. May I know is there any way to achieve what I want for non 365 office user? Any help will be greatly appreciated!

Upvotes: 0

Views: 7879

Answers (3)

Chris Strickland
Chris Strickland

Reputation: 3490

enter image description here

Say you have this layout (just the first two columns of your data, moved to a1). Here are two formulas, one that contains FALSES (if you don't care) and one that removes them (because you probably do):

=IF(A4:A13=B1,B4:B13)

=IFERROR(SMALL(IF(A4:A13=B1,B4:B13), ROW(A4:A13)-3), "")

The first one is pretty straightforward. The second one is very similar. It just passes those results to SMALL, which will return the kth smallest value form the array ignoring FALSE values. To get it to evaluate the entire array, you also send it an array of 1 to n, generated with ROW(), and since the range starts in A4 you have to adjust by -3 to make the array start at 1. If you didn't want to have to figure out the offset, you could do this, but we're rapidly losing readability:

=IFERROR(SMALL(IF(A4:A13=B1,B4:B13), ROW(A4:A13)-MIN(ROW(A4:A13))+1), "")

When SMALL gets your list of matching values (with the falses), it will a match for each number in the ROW array you send it, and if it runs out of actual numbers it will start returning errors, which is why you wrap the whole thing in IFERROR.

This will work for numeric values. If you have to support any value, you can still do it:

=IFERROR(
  INDEX(
    B:B, 
    SMALL(
      IF(A4:A13=B1, ROW(A4:A13)),  
      ROW(INDIRECT("1:"&ROWS(A4:A13)))
    ), 
    0
  ), 
"")

In this case, instead of returning the matching values with SMALL, you will return the matching row numbers, then you will pass those to INDEX, wrapping the whole thing in IFERROR. I used a slightly different method to generate the dynamic indexes:

ROW(INDIRECT("1:"&ROWS(A4:A13)))

which will return an array from 1 to the number of rows in the passed range, but any of the methods to generate the sequence will work.

Upvotes: 0

weizer
weizer

Reputation: 1127

enter image description here

Manage to find the solution.

Formula:

G5 = =IFERROR(INDEX($C$5:$C$14,AGGREGATE(15,6,1/($C$5:$C$14=$D$2)*(ROW($C$5:$C$14)-ROW($C$4)),ROW()-ROW($C$4))),"")

H5 = =IFERROR(INDEX($D$5:$D$14,AGGREGATE(15,6,1/($C$5:$C$14=$D$2)*(ROW($C$5:$C$14)-ROW($C$4)),ROW()-ROW($C$4))),"")

I5 = =IFERROR(INDEX($E$5:$E$14,AGGREGATE(15,6,1/($C$5:$C$14=$D$2)*(ROW($C$5:$C$14)-ROW($C$4)),ROW()-ROW($C$4))),"")

Drag down these 3 formula to the cells below and should work.

Upvotes: 0

Ozgun Senyuva
Ozgun Senyuva

Reputation: 466

As far as I understand, hiding the values different than D2 will take care of your need. I am using a similar macro for this task and below I modified it for you to hide the values different than D2. It will start checking values from active cell and loop through until it finds a null value. You can try it and modify it according to your needs. Then you can assign a keyboard shortcut or put a button for it into quick access toolbar, if you are going to use this frequently.

Sub hideByD2()
Dim i, j
i = ActiveCell.Row
j = ActiveCell.Column
k = Cells(2, 4).Value

Do Until Cells(i, j) = ""
    
    If Cells(i, j) <> k Then
    Rows(i).Select
    Selection.EntireRow.Hidden = True
    Else
    End If
    i = i + 1
Loop
MsgBox "hide process completed successfully"

End Sub

Upvotes: 1

Related Questions