1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to return all rows with matching wildcard criteria

Discussion in 'Ask an Excel Question' started by Finny99, Apr 19, 2017.

  1. Finny99

    Finny99 New Member

    Messages:
    5
    I've been trying to get a formula to return all rows from a tab in which multiple criteria are met, including a wildcard criteria. I've attached the sample excel in terms of what I'm trying to achieve. It's very basic and not the actual dataset, but you get the idea. I can get it when the cells are exact matches, but not if the cell contains it.

    Please help if you can and thanks.

    Attached Files:

  2. AlanSidman

    AlanSidman Active Member

    Messages:
    117
    try this VBA solution:
    Code (vb):

    Option Explicit

    Sub FindText()
        Dim i As Long, lr As Long, lr2 As Long
        lr = Range("A" & Rows.Count).End(xlUp).Row
        Dim sFind As String
        sFind = Range("G2")
        Application.ScreenUpdating = False
        For i = 2 To lr
            lr2 = Range("F" & Rows.Count).End(xlUp).Row
            If InStr(1, Range("B" & i), sFind) > 0 Then
                Range("A" & i & ":C" & i).Copy Range("F" & lr2 + 1)
            End If
        Next i
        Application.CutCopyMode = False
        Application.ScreenUpdating = True

    End Sub
  3. PaulF

    PaulF Active Member

    Messages:
    203
    I hope this helps you,

    I added a helper column with: =IF(AND(ISNUMBER(SEARCH($F$2,A2)),ISNUMBER(SEARCH($G$2,B2))),MAX($D$1:D1)+1,"")

    Then a lookup value built on an expanding array: =INDEX(A$2:A$5,MATCH(ROWS($F$6:F6),$D$2:$D$5,0))

    See attached.

    Respectfully,
    PaulF

    Attached Files:

  4. Nebu

    Nebu Excel Ninja

    Messages:
    2,030
    Hi:

    Use the following formula.

    =INDEX($A$2:$A$5,AGGREGATE(15,6,(ROW($A$2:$A$5)-MIN(($A$2:$A$5))-1)/MMULT(($F$2=$A$2:$A$5)*(IF(ISNUMBER(SEARCH("*"&$G$2,$B$2:$B$5))>0,1)),1),ROW(A1)))

    Thanks

    Attached Files:

    PaulF likes this.
  5. PaulF

    PaulF Active Member

    Messages:
    203
    I need to save this and rip it apart to learn and reply "I'm not worthy... " :)
  6. Nebu

    Nebu Excel Ninja

    Messages:
    2,030
    Hi:

    A small Correction

    =INDEX($A$2:$A$5,AGGREGATE(15,6,(ROW($A$2:$A$5)-MIN(($A$2:$A$5))-1)/MMULT(($F$2=$A$2:$A$5)*(ISNUMBER(SEARCH("*"&$G$2,$B$2:$B$5))),1),ROW(A1)))

    Yon can take out the If condition from the Isnumber/ search condition.


    Thanks
  7. Finny99

    Finny99 New Member

    Messages:
    5
    Thanks everyone for the suggestions. I'll give them a shot. You all rock.
  8. Finny99

    Finny99 New Member

    Messages:
    5
    how do I enter this formula? I was trying to edit it and then drag the formula across, but it doesn't return the same values as are in the cells. Sorry for the stupid question. Also, I switched the selection from New to Old and it returned both of the rows for OLD but it shouldnt' do that.
  9. Nebu

    Nebu Excel Ninja

    Messages:
    2,030
    Hi:

    It works fine at my end here the file where I switched the selection from New to Old and it returned only one row for OLD.

    Thanks

    Attached Files:

  10. Finny99

    Finny99 New Member

    Messages:
    5
    How do I manage this formula? I try dragging across to more cells and it ceases to work properly. Is there something special?
  11. Finny99

    Finny99 New Member

    Messages:
    5
    nevermind. I noticed the formula had locked cells in the first piece which weren't moving when being dragged. THanks again.
  12. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,150
    In addition to remove the "$" sign near INDEX, MMULT function and "*" also can take out.

    and, become >>

    =INDEX(A$2:A$5,AGGREGATE(15,6,(ROW($A$2:$A$5)-MIN($A$2:$A$5)-1)/($F$2=$A$2:$A$5)*ISNUMBER(SEARCH($G$2,$B$2:$B$5)),ROW(A1)))

    Regards
  13. Nebu

    Nebu Excel Ninja

    Messages:
    2,030
    Hi:

    Its ok to take out "*", but if you take out MMULT , the order in which the Output are shown will be in reverse.

    Thanks
  14. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,150
    Hi,

    1] The output reverse problem can be fixed by changing a "*" into "/" as in :

    =IFERROR(INDEX(A$2:A$5,AGGREGATE(15,6,(ROW($A$2:$A$5)-MIN($A$2:$A$5)-1)/($F$2=$A$2:$A$5)/ISNUMBER(SEARCH($G$2,$B$2:$B$5)),ROW(A1))),"")

    2] A comparison formula file is herein attached

    Regards

    Attached Files:

    Thomas Kuriakose likes this.

Share This Page