Solved

I need to find a string within a string and return a value from a different spreadsheet.

Posted on 2013-01-28
19
212 Views
Last Modified: 2013-02-01
I had this question going a week or two ago and I have an updated request.

Please see the attached file and help me find a way to have B22 and B23 return the same value.
 

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27992921.html

Thanks!
Test--1-barry3.1.xlsx
0
Comment
Question by:Dominator1025
  • 9
  • 7
  • 3
19 Comments
 
LVL 48

Expert Comment

by:jpaulino
ID: 38834421
Do you want to try using VBA?

Public Function LookupCode(code As String) As String
Dim result() As String
Dim rng As Range
Dim c As Variant

result = Split(code, ",")

For Each c In result
    Set rng = shPercentages.Range("A2:A15000").Find(c, LookIn:=xlValues)
    If Not rng Is Nothing Then
        LookupCode = FormatPercent(rng.Offset(ColumnOffset:=1).Value)
        Exit Function
    End If
Next

LookupCode = "Missing Data"

End Function

Open in new window

Test--1-barry3.1.xlsm
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38834925
Try this formula in B2

=IFERROR(IFERROR(LOOKUP(2^15,SEARCH(SUBSTITUTE(","&Percentages!$A$2:$A$15000&","," ",""),","&A2&","),Percentages!$B$2:$B$15000),LOOKUP(2^15,SEARCH(SUBSTITUTE(","&LEFT(Percentages!$A$2:$A$15000&"$$$$",4)," ",""),","&A2),Percentages!$B$2:$B$15000)),INDIRECT(ADDRESS(ROW()-1,COLUMN())))
0
 

Author Comment

by:Dominator1025
ID: 38836328
JPaulino,   If VBA would account for any combination of the Objects in either tab, I would be very open to learning more about it. I also imagine it would not be as big of a memory hog as the large formulas, correct?

That said, how can I get the VBA code to work for all cells in column B?  When I copy the =LookupCode(B22) to the other cells everything turns into this " #NAME? "
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38836526
Did you try the formula I proposed?
0
 

Author Comment

by:Dominator1025
ID: 38836527
ssaqibh, With the formula you provided, it does give the same value for B21 and B22, but it also is providing false data in other locations.  Please check out the new file to see what I mean. It looks like your file is simply returning the valu from the previously found cell.

Thanks!
Test--1-barry3.2.xlsm
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38836568
Dominator1025

That is exactly what I had understood and attempted.

Now with the given example how would one know which of the objects could use the previous value and which of them should look for their own?
0
 

Author Comment

by:Dominator1025
ID: 38836669
ssaqibh, I'm sorry I wasn't more clear. I had already gone through this in much greater detail with Barry Houdini in a previously submitted question and came across a new wrinkle that I needed to iron out.

In that last file I added, I want the formula in B21 of the Formulas tab to find a string of text in Column A of the Percentages tab that matches, at least the first 4 characters of one of the sets of 4 or 5 character strings in the text in A21 of the Formulas tab, and then return the value in column B of the Percentages tab. Does that make sense?

The first file I included with my initial question above, handles this perfectly, except for the case of A542 on the percentages tab. I was hoping that would be an easy adjustment.

Thanks for your help!
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 38837216
That said, how can I get the VBA code to work for all cells in column B?  When I copy the =LookupCode(B22) to the other cells everything turns into this " #NAME? "

Have you activated the macros? It works if you copy the formula to other rows.
0
 

Author Comment

by:Dominator1025
ID: 38837344
JPaulino,

Ok, got the Macros fired up. It is having a problem with the objects that are missing the 5th character in the string, which is a possibility I need included.

I will eventually teach myself VBA, but could you please explain the code that you have given me? This seems like a great solution, but I will need to expand it to other worksheets and ranges and would like to make sure I understand what I am doing.

Thanks!
Test--1-barry3.1VBA.xlsm
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 38837363
Ok, got the Macros fired up. It is having a problem with the objects that are missing the 5th character in the string, which is a possibility I need included.

Can you explain this? Maybe with an example
0
 

Author Comment

by:Dominator1025
ID: 38837372
JPaulino,
Please check out the file I attached in my previous post, labeled VBA 2.0.
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 38837465
Check the lastest version with some commets in code
Test--1-barry3.1VBA.xlsm
0
 

Author Comment

by:Dominator1025
ID: 38837561
JPaulino,

Thanks, this looks like it gets everything I need.

If I wanted to change the lookup to a different sheet with a bigger range how would I change the code?  Lets say from the last file, the "Formulas" tab stays the same, but I want to look at a different tab called "New Sheet" and the object I am looking for is still in column A on "New Sheet", but the values on "New Sheet" that I want returned are in column AH.

Can you please show me how the code would look with those new parameters:
"New Sheet" and column AH.

Thanks again, I appreciate it!
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 38837576
You can change the sheet name like this:

Set rng = Sheets("Percentages").Range("A2:A15000") ....

Just change Percentages with the correct name and in the offset value (ColumnOffset:=1) to the offset columns you need,
0
 

Author Comment

by:Dominator1025
ID: 38839716
Jpaulino,

I am having trouble editing the code to rollout to other files. I am still getting the  #NAME? error again and I am sure I have macros enabled. I think I might be missing something.  

Is 'LookupCode' the name you gave or a specific VBA function command? I was hoping to name each function something different as I have to create various lookups in a workbook. So could this new one be 'LookupNewSheet'?

Can you please add code to the file attached that will pull the values from the 'New Sheet' from column AH?

Thanks!
Test--1-barry3.2VBA.xlsm
0
 
LVL 48

Accepted Solution

by:
jpaulino earned 500 total points
ID: 38843889
Check now with a small change
Test--1-barry3.2VBA.xlsm
0
 

Author Comment

by:Dominator1025
ID: 38844422
Jpaulino,

I can't seem to get the code running on my big file. Can you please walk me through how to implement the code in a macro-enabled workbook that has no code when you open Visual Basic?

I am fairly new to VBA, but I pick things up quickly and just need a little extra help to get going here, thanks!
0
 

Author Comment

by:Dominator1025
ID: 38844514
I think I got it, I didn't have the code in a module, it was in a worksheet. This should close out the question, thank you so much!!!

Can you please recommend some learning material for someone that is already pretty handy with Excel and Access but wants to take things to the next level with VBA?
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 38845276
You have some nice articles here in EE and you can also look in youtube for VBA tutorials ... you have a lot there.

Glad I could help!
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Formula 5 45
Auto Populate Day Month  2 digit Date 4 19
Excel VBA - UserForm Label Caption to show recordset data dynamically 12 18
Manipulate Range in Excel VBA 6 22
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

773 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