Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP Comma Separated Values in Excel 2007?
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to I create a comma separated values file | Excel Discussion (Misc queries) | |||
Fixing Comma Separated Values (.csv) | Excel Discussion (Misc queries) | |||
summing comma separated values in a range | Excel Programming | |||
summing comma separated values in a range | Excel Programming | |||
CSV (Comma Separated Values) delimiter ? | Excel Programming |