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

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!

CodePudding user response：

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.

CodePudding user response：

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

CodePudding user response：

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.