vlookup? instr? if?

[shakethingsup]

Hi again everyone,

Q: what's the most efficient way to search for partial matches and populate results?

I'm fairly beginner at vba but learning quickly.

I have two very disorganized workbooks. Wb2 is where I will make all my edits.

• each workbook when exported has varying lines of code.
• each are downloaded from two different systems and so the raw data looks like this:

Raw data 1 or wb1:

Raw data 2 or wb2:

wb1 cell I10 is similar to wb2 cell C2 because of the J1 - the unique identifier is J1 which is also buried in string. There could be J1 to J100 so I'm sure I could use a loop at some point.


Output: In Cell F2 in wb2, I want to pull L10 from wb1 i.e. F2 wb1 = $5


1. I've learned about the instr function in vba

2. I've learned how to open workbooks so from wb1 I open wb2

3. I'm getting better at loops



Q: what's the most efficient way to search for partial matches (in this case "J1") and populate results?

thank you!

 

[inactiveuserps07]

@shakethingsup, if your first sheet were actually called "wb1," you could enter the following formula into wb2!E2 and drag-copy down as far as needed:

=IFERROR(IF(ISERROR(SEARCH("~~~",C2)),"",INDEX('wb1'!L:L,MATCH("*"&LEFT(TRIM(MID(C2,FIND(" ",C2)+1,50)),FIND("~",TRIM(MID(C2,FIND(" ",C2)+1,50)))-1)&"*",'wb1'!I:I,0))),"NOT FOUND")

 

[shakethingsup]

yikes - I'll have to try and understand that. thank you for your response!

I guess I should have been more explicit. The important info is not always located on the same row. And I wanted to automate it because there are 80 instances of this each month. I'll keep trying.

thanks!

 

[inactiveuserps07]

@shakethingsup, if you copy the formula I provided into the first possible row where "important" data may occur (for instance, E2) and drag-copy the formula down the column to wherever the last "important" item may reside, it will fill in the dollar amounts from wb1 only​ for the important rows, leaving the others blank.