[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

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

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
Dominator1025
Asked:
Dominator1025
  • 9
  • 7
  • 3
1 Solution
 
jpaulinoCommented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
Dominator1025Author Commented:
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
Industry Leaders: 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!

 
Saqib Husain, SyedEngineerCommented:
Did you try the formula I proposed?
0
 
Dominator1025Author Commented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
Dominator1025Author Commented:
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
 
jpaulinoCommented:
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
 
Dominator1025Author Commented:
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
 
jpaulinoCommented:
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
 
Dominator1025Author Commented:
JPaulino,
Please check out the file I attached in my previous post, labeled VBA 2.0.
0
 
jpaulinoCommented:
Check the lastest version with some commets in code
Test--1-barry3.1VBA.xlsm
0
 
Dominator1025Author Commented:
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
 
jpaulinoCommented:
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
 
Dominator1025Author Commented:
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
 
jpaulinoCommented:
Check now with a small change
Test--1-barry3.2VBA.xlsm
0
 
Dominator1025Author Commented:
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
 
Dominator1025Author Commented:
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
 
jpaulinoCommented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now