Solved

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

Posted on 2012-03-24
25
268 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 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
Sure, a VBA solution would be great if that will help me to get what I need.  Thanks!
example.xlsx
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
I would go for an approach like this.
Copy-of-example-4.xlsx
0
 

Author Comment

by:lcha
Comment Utility
I deleted some of the columns.  Now the formula is:

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

Author Comment

by:lcha
Comment Utility
what you are seeing is correct
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 275 total points
Comment Utility
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
Comment Utility
thanks Martin!   I'll try it out and let you know the results.
0
 

Author Comment

by:lcha
Comment Utility
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
Comment Utility
Would like to comment on my solution also?
0
 

Author Comment

by:lcha
Comment Utility
thanks, I will try that one too!
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
Do you want to search for |40| in column b as well? The given data does not contain any.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:lcha
Comment Utility
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
Comment Utility
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
Comment Utility
here's the file
0
 

Author Comment

by:lcha
Comment Utility
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
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
Comment Utility
thx, how would I determine which rows have the values?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
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
Comment Utility
thanks, would give me some hints or help to understand what this formula is doing?   I'm having trouble understanding.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
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
Comment Utility
Martin, your solution worked for me. thanks!

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

Author Comment

by:lcha
Comment Utility
thanks both for your help! :)
0
 

Author Closing Comment

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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
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…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

763 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

8 Experts available now in Live!

Get 1:1 Help Now