using the Find function to search and identify rows that have specific values

Dear experts,

I am trying to solve a problem in Excel.   Please see my question in the attached word document.    Thanks in advance for your help!

lcha
Question.docx
lchaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Would you be interested in a VBA solution?

And just so I understand the data, in the example you posted H2 now contains 6467, 5429 and 6763 and I2 contains 106, 5 and 1, correct?

Can you attach a sample sheet?
lchaAuthor Commented:
Sure, a VBA solution would be great if that will help me to get what I need.  Thanks!
example.xlsx
Martin LissOlder than dirtCommented:
I'm confused by your cell format, and maybe it's because I'm converting your sheet to Excel 2003, I don't see anything in H2, just in A, B and C, and for example I see this in A3.

2072|5429|6381|6763|6816|6820|100597|100598|101020|101201|101202|950000001|2149|6412|6414|6478|100626|100628|1365|6976|100849|101196|101197
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Saqib Husain, SyedEngineerCommented:
I would go for an approach like this.
Copy-of-example-4.xlsx
lchaAuthor Commented:
I deleted some of the columns.  Now the formula is:

=FIND("|6352|",A2)&FIND("|40|",B2)
lchaAuthor Commented:
what you are seeing is correct
Martin LissOlder than dirtCommented:
Private Sub CommandButton1_Click()
Dim r As Range
Dim strA() As String
Dim strB() As String
Dim i As Long
Dim j As Long
Dim k As Long

Set r = Range("A1").End(xlDown).Offset(0, 0)

For i = 2 To Range("A1").End(xlDown).Offset(0, 0).Row
    strA = Split(Range("A" & i).Value, "|")
    For j = 0 To UBound(strA)
        Select Case strA(j)
            Case "6352", "6353", "101581", "101582", "100626", "100627", "2244", "2172"
                strB = Split(Range("B" & i), "|")
                For k = 0 To UBound(strB)
                    If strB(k) = "40" Then
                        Range("D" & i).Value = "match"
                    End If
                Next
        End Select
    Next
Next
                   

End Sub

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lchaAuthor Commented:
thanks Martin!   I'll try it out and let you know the results.
lchaAuthor Commented:
I created a form with a button on it and copied the code you gave me to it with the vba editor.   After running this, how can I view the results?  Also, could you help to explain what this the code is doing?
Saqib Husain, SyedEngineerCommented:
Would like to comment on my solution also?
lchaAuthor Commented:
thanks, I will try that one too!
Saqib Husain, SyedEngineerCommented:
Do you want to search for |40| in column b as well? The given data does not contain any.
lchaAuthor Commented:
thanks ssaqibh.    I have it up now but I need some help understanding what the numbers mean

51, 95, 85 - are these the position in the string of text that it finds with the value of C1?

and what does the IFERROR DO? =IFERROR(FIND("|"&C$1&"|",$A2),"")
lchaAuthor Commented:
yes, I want to search for |40| as well.  Here's another excel doc with the 40 if you can help with that.  thanks!
lchaAuthor Commented:
here's the file
lchaAuthor Commented:
Saqib Husain, SyedEngineerCommented:
You can try this array formula (remember that array formulas need to be entered by ctrl-shift-enter.
Copy-of-example-40.xlsx
lchaAuthor Commented:
thx, how would I determine which rows have the values?
Saqib Husain, SyedEngineerCommented:
Use this formula instead:

=MIN(IFERROR(FIND({"|6352|","|6353|","|101581|","|101582|","|100626|","|100627|","|2244|","|2172|"},A2),FALSE))*IFERROR(FIND("|40|",B2),0)

Remember this is an array formula.

You can now filter for non-zeros
lchaAuthor Commented:
thanks, would give me some hints or help to understand what this formula is doing?   I'm having trouble understanding.
Martin LissOlder than dirtCommented:
I'm not at home but my code splits up the data in the two columns looking for 40 in one column and the numbers you listed in the other. If there's a match the word 'match' appears in col D. You can change that of course.
Saqib Husain, SyedEngineerCommented:
This formula has two parts; one for column A and the other for column B.

The column B part looks for |40| in B2 and returns its position if found otherwise it returns a zero.

The column A part looks for each of those numbers in A2 and returns a list giving the position if found otherwise a "False" which is essentially zero. From this list the minimum value is returned.

Both these numbers are multiplied with each other.

So if either of the two columns return a zero the result is zero otherwise it will be a number.
lchaAuthor Commented:
Martin, your solution worked for me. thanks!

 ssaqibh, I'll revieiw yours as well soon.
lchaAuthor Commented:
thanks both for your help! :)
lchaAuthor Commented:
Perfect, really fast and helpful responses.  Both solutions will work great for me.   appreciate all the help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.