Solved

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

Posted on 2012-03-24
25
269 Views
Last Modified: 2012-03-24
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
0
Comment
Question by:lcha
  • 15
  • 6
  • 4
25 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 37761704
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?
0
 

Author Comment

by:lcha
ID: 37761747
Sure, a VBA solution would be great if that will help me to get what I need.  Thanks!
example.xlsx
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 37761755
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
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37761762
I would go for an approach like this.
Copy-of-example-4.xlsx
0
 

Author Comment

by:lcha
ID: 37761777
I deleted some of the columns.  Now the formula is:

=FIND("|6352|",A2)&FIND("|40|",B2)
0
 

Author Comment

by:lcha
ID: 37761780
what you are seeing is correct
0
 
LVL 46

Accepted Solution

by:
Martin Liss earned 275 total points
ID: 37761802
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
0
 

Author Comment

by:lcha
ID: 37761824
thanks Martin!   I'll try it out and let you know the results.
0
 

Author Comment

by:lcha
ID: 37761854
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?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37761865
Would like to comment on my solution also?
0
 

Author Comment

by:lcha
ID: 37761869
thanks, I will try that one too!
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37761884
Do you want to search for |40| in column b as well? The given data does not contain any.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:lcha
ID: 37761893
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),"")
0
 

Author Comment

by:lcha
ID: 37761900
yes, I want to search for |40| as well.  Here's another excel doc with the 40 if you can help with that.  thanks!
0
 

Author Comment

by:lcha
ID: 37761902
here's the file
0
 

Author Comment

by:lcha
ID: 37761904
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37761926
You can try this array formula (remember that array formulas need to be entered by ctrl-shift-enter.
Copy-of-example-40.xlsx
0
 

Author Comment

by:lcha
ID: 37761936
thx, how would I determine which rows have the values?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37761958
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
0
 

Author Comment

by:lcha
ID: 37761999
thanks, would give me some hints or help to understand what this formula is doing?   I'm having trouble understanding.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 37762027
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.
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 225 total points
ID: 37762144
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.
0
 

Author Comment

by:lcha
ID: 37762147
Martin, your solution worked for me. thanks!

 ssaqibh, I'll revieiw yours as well soon.
0
 

Author Comment

by:lcha
ID: 37762160
thanks both for your help! :)
0
 

Author Closing Comment

by:lcha
ID: 37762165
Perfect, really fast and helpful responses.  Both solutions will work great for me.   appreciate all the help!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now