Vlookup between 2 workbooks, taking into account duplicates on source.

[zookeepertx]

I've got 3 columns of formulas that end up doing what I need, but I'm thinking it could probably be done easier with VBA.

I have 1 book with a sheet I'll call Log & another book with a sheet I'll call Source. On the Log, column E has the first 9 characters of vendor names & /vendor number (ex: EDMUND FI/00250), along with other data out through column P. On Source, the vendor names are in C & a short code for their name is in A.

I need to compare the first 9 characters in Log col E with the first 9 characters in Source col C. When a match is found, I need to return the short code from Source col A. BUT - if there's a duplicate in either Source col C or A, instead I need to return the word VERIFY, preferably with the cell highlighted in red.

Right now, I have, on Source col L:

=MID(C2,1,9)

and in source M:

=IF(OR(A2=A1,A2=A3,L2=L1,L2=L3),"VERIFY",A2)

This gives me the first 9 characters of the vendor name in L & the short code OR VERIFY in M

Then, in Log col R, I have an array formula:

=VLOOKUP(MID(E4,1,9),'[PRETICKET P ADDRESSES working.xls]Paddress'!$L:$M,2,FALSE)

This gives me either the short code or VERIFY from Source M. Also, sometimes it doesn't find a match & it returns #N/A. Then I have to do Conditional Formatting to make the VERIFY cells red & I thought I'd make the error cells be yellow (although I haven't figured out the CF for that yet)

Is there a better way to do this with VBA? I'm working with Excel 2010; just got it & still figuring it out.

Thanks!!

 

[zookeepertx]

Here is a short example of the Source sheet:

[TABLE="width: 273"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 102pt; mso-width-source: userset; mso-width-alt: 4973" width=136><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><TBODY>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]Col A[/TD]
[TD="class: xl66, width: 136, bgcolor: transparent"]Col C[/TD]
[TD="class: xl66, width: 93, bgcolor: transparent"]Col L[/TD]
[TD="class: xl66, width: 71, bgcolor: transparent"]Col M[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]WIDS[/TD]
[TD="class: xl65, bgcolor: transparent"]Widgets and Stuff[/TD]
[TD="class: xl65, bgcolor: transparent"]Widgets a[/TD]
[TD="class: xl65, bgcolor: transparent"]WIDS[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]BUCO[/TD]
[TD="class: xl65, bgcolor: transparent"]Bungee Cords Intl[/TD]
[TD="class: xl65, bgcolor: transparent"]Bungee Co[/TD]
[TD="class: xl65, bgcolor: transparent"]BUCO[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]WNST[/TD]
[TD="class: xl65, bgcolor: transparent"]Wires N Strings[/TD]
[TD="class: xl65, bgcolor: transparent"]Wires N S[/TD]
[TD="class: xl65, bgcolor: transparent"]WNST[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]DICT[/TD]
[TD="class: xl65, bgcolor: transparent"]Dictionaries R Us[/TD]
[TD="class: xl65, bgcolor: transparent"]Dictionar[/TD]
[TD="class: xl65, bgcolor: transparent"]VERIFY[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]DICR[/TD]
[TD="class: xl65, bgcolor: transparent"]Dictionaries R Us[/TD]
[TD="class: xl65, bgcolor: transparent"]Dictionar[/TD]
[TD="class: xl65, bgcolor: transparent"]VERIFY[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]JUST[/TD]
[TD="class: xl65, bgcolor: transparent"]Just A Dollar[/TD]
[TD="class: xl65, bgcolor: transparent"]Just A Do[/TD]
[TD="class: xl65, bgcolor: transparent"]VERIFY[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]JUST[/TD]
[TD="class: xl65, bgcolor: transparent"]Justin Morgan[/TD]
[TD="class: xl65, bgcolor: transparent"]Justin Mo[/TD]
[TD="class: xl65, bgcolor: transparent"]VERIFY[/TD]
[/TR]
</TBODY>[/TABLE]


and the Log sheet result desired:

[TABLE="width: 157"]
<COLGROUP><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5083" width=139><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><TBODY>[TR]
[TD="class: xl66, width: 139, bgcolor: transparent"]Col E[/TD]
[TD="class: xl66, width: 70, bgcolor: transparent"]Col R[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Justin Mo/27584[/TD]
[TD="class: xl67, bgcolor: red"]VERIFY[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Wires N S/13579[/TD]
[TD="class: xl65, bgcolor: transparent"]WNST[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Dictionar/14893[/TD]
[TD="class: xl67, bgcolor: red"]VERIFY[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Dictionar/14893[/TD]
[TD="class: xl67, bgcolor: red"]VERIFY[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Northern /98347[/TD]
[TD="class: xl68, bgcolor: yellow"]#N/A[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Widgets a/12345[/TD]
[TD="class: xl65, bgcolor: transparent"]WIDS[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Bungee Co/24680[/TD]
[TD="class: xl65, bgcolor: transparent"]BUCO[/TD]
[/TR]
</TBODY>[/TABLE]


Thanks for any help!

 

[zookeepertx]

I just realized that the array formula in Log col R should say (if following the information I gave):

=VLOOKUP(MID(E4,1,9),'[Source]Sheet1'!$L:$M,2,FALSE)

Sorry about that!