Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2012-03-24
25
Medium Priority
?
275 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 50

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 50

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 50

Accepted Solution

by:
Martin Liss earned 1100 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
 

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 50

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 900 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

810 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