T
Travis T
In an attempt to make my job easier, I am having to review data from one web
site against data from another web site. This also means translating data
from the data on Website A to match the appropriate data on Website B. For
example (just pretend UPCs are that short).
Worksheet A
UPC SiteAID Name Associated
001 954 Fun 137,321
002 137 Happy 954,321
003 862 Sad
004 432 Mad 554
005 684 Bored 874
006 321 Silly 954,137
008 554 Angry 432
010 874 Mellow
Worksheet B
UPC SiteBID Name
001 ABD Fun
002 TYF Happy
006 MSD Silly
007 IUE Weird
009 WQT OK
Worksheet C
UPC AllID Name Associated
001 ABD Fun TYF,MSD
002 TYF Happy ABD,MSD
006 MSD Silly ABD,TYF
007 IUE Weird
009 WQT OK
As you can see, Worksheet A shows the associations with its own SiteID.
Worksheet B doesn't show any associations, but would like to use the
associations in Worksheet B with its own SiteIDs (as shown in Worksheet C,
the final product).
A regular VLOOKUP won't work here, so after some searching, I did find some
code that would do a MultiVLOOKUP, essentially putting the data from the cell
into an array, performing a VLOOKUP on each value in that cell, and output
its values separated by commas.
Option Explicit
Function MultiVLOOKUP(LookUpVal, LookUpRng As Range, LookUpCol As Long)
Dim v, w, i, rng As Range
v = Split(LookUpVal, ",")
ReDim w(UBound(v, 1))
For i = LBound(v, 1) To UBound(v, 1)
w(i) = WorksheetFunction.VLookup(Val(v(i)), LookUpRng, LookUpCol, False)
Next i
MultiVLOOKUP = Join(w, ",")
End Function
From what I understand, I had to put these data as a new module in the
Workbook (which I did), but every time I attempt to run the code, I get a
#NAME? or #VALUE! error (depending on if I'm doing a VLOOKUP for the needed
LookUpVal or using straight data for the LookUpVal).
From what I can tell, it's hitting some issues when it doesn't find matching
data, but that's just my guess. Can anyone offer any insight into this issue?
site against data from another web site. This also means translating data
from the data on Website A to match the appropriate data on Website B. For
example (just pretend UPCs are that short).
Worksheet A
UPC SiteAID Name Associated
001 954 Fun 137,321
002 137 Happy 954,321
003 862 Sad
004 432 Mad 554
005 684 Bored 874
006 321 Silly 954,137
008 554 Angry 432
010 874 Mellow
Worksheet B
UPC SiteBID Name
001 ABD Fun
002 TYF Happy
006 MSD Silly
007 IUE Weird
009 WQT OK
Worksheet C
UPC AllID Name Associated
001 ABD Fun TYF,MSD
002 TYF Happy ABD,MSD
006 MSD Silly ABD,TYF
007 IUE Weird
009 WQT OK
As you can see, Worksheet A shows the associations with its own SiteID.
Worksheet B doesn't show any associations, but would like to use the
associations in Worksheet B with its own SiteIDs (as shown in Worksheet C,
the final product).
A regular VLOOKUP won't work here, so after some searching, I did find some
code that would do a MultiVLOOKUP, essentially putting the data from the cell
into an array, performing a VLOOKUP on each value in that cell, and output
its values separated by commas.
Option Explicit
Function MultiVLOOKUP(LookUpVal, LookUpRng As Range, LookUpCol As Long)
Dim v, w, i, rng As Range
v = Split(LookUpVal, ",")
ReDim w(UBound(v, 1))
For i = LBound(v, 1) To UBound(v, 1)
w(i) = WorksheetFunction.VLookup(Val(v(i)), LookUpRng, LookUpCol, False)
Next i
MultiVLOOKUP = Join(w, ",")
End Function
From what I understand, I had to put these data as a new module in the
Workbook (which I did), but every time I attempt to run the code, I get a
#NAME? or #VALUE! error (depending on if I'm doing a VLOOKUP for the needed
LookUpVal or using straight data for the LookUpVal).
From what I can tell, it's hitting some issues when it doesn't find matching
data, but that's just my guess. Can anyone offer any insight into this issue?