[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2012-03-24
25
Medium Priority
?
274 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 6
  • 4
25 Comments
 
LVL 49

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 49

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 49

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 49

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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

656 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