• 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...

  • 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

Finny99

New Member
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.
 

Attachments

  • Return Multiple Rows Excel.xlsx
    9.6 KB · Views: 11
try this VBA solution:
Code:
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
 
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
 

Attachments

  • Return Multiple Rows Excel.xlsx
    10.2 KB · Views: 4
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
 

Attachments

  • Return Multiple Rows Excel.xlsx
    10.2 KB · Views: 7
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

I need to save this and rip it apart to learn and reply "I'm not worthy... " :)
 
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
 
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
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.
 
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
 

Attachments

  • Return Multiple Rows Excel.xlsx
    10.2 KB · Views: 9
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
How do I manage this formula? I try dragging across to more cells and it ceases to work properly. Is there something special?
 
How do I manage this formula? I try dragging across to more cells and it ceases to work properly. Is there something special?
nevermind. I noticed the formula had locked cells in the first piece which weren't moving when being dragged. THanks again.
 
nevermind. I noticed the formula had locked cells in the first piece which weren't moving when being dragged. THanks again.
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
 
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
 
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

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
 

Attachments

  • Return Multiple Rows Excel(1).xlsx
    11.3 KB · Views: 11
Back
Top