Solved

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

Posted on 2013-01-28
19
210 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

747 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

13 Experts available now in Live!

Get 1:1 Help Now