• 0

[VBA] VLOOKUP Comma Separated Values in Excel 2007?


Question

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?

0 answers to this question

Recommended Posts

There have been no answers to this question yet

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • QOwnNotes 25.6.0 by Razvan Serea QOwnNotes is a open source (GPL) plain-text file notepad with markdown support and todo list manager for GNU/Linux, Mac OS X and Windows, that (optionally) works together with the notes application of ownCloud (or Nextcloud). So you are able to write down your thoughts with QOwnNotes and edit or search for them later from your mobile device (like with CloudNotes) or the ownCloud web-service. The notes are stored as plain text files and you can sync them with your ownCloud sync client. Of course other software, like Dropbox, Syncthing, Seafile or BitTorrent Sync can be used too. Features: the notes folder can be freely chosen (multiple note folders can be used) sub-string searching of notes is possible and search results are highlighted in the notes application can be operated with customizable keyboard shortcuts external changes of note files are watched (notes or note list are reloaded) older versions of your notes can be restored from your ownCloud server trashed notes can be restored from your ownCloud server differences between current note and externally changed note are showed in a dialog markdown highlighting of notes and a markdown preview mode notes are getting their name from the first line of the note text (just like in the ownCloud notes web-application) and the note text files are automatically renamed, if the the first line changes compatible with the notes web-application of ownCloud and mobile ownCloud notes applications compatible with ownCloud's selective sync feature by supporting an unlimited amount of note folders with the ability to choose the respective folder on your server manage your ownCloud todo lists (ownCloud tasks or Tasks Plus / Calendar Plus) or use an other CalDAV server to sync your tasks to encryption of notes (AES-256 is built in or you can use custom encryption methods like Keybase.io (encryption-keybase.qml) or PGP (encryption-pgp.qml)) dark mode theme support theming support for the markdown syntax highlighting all panels can be placed wherever you want, they can even float or stack (fully dockable) support for freedesktop theme icons, you can use QOwnNotes with your native desktop icons and with your favorite dark desktop theme support for hierarchical note tagging and note subfolders support for sharing notes on your ownCloud server portable mode for carrying QOwnNotes around on USB sticks Evernote import QOwnNotes is available in many different languages like English, German, French, Polish, Chinese, Japanese, Russian, Portuguese, Hungarian, Dutch and Spanish Changes in QOwnNotes 25.6.0: QOwnNotes now builds with with Botan 3, when built with the system Botan library (cmake build parameter -DBUILD_WITH_SYSTEM_BOTAN=ON), because of the end of life of Botan 2 (for #2786) Keep in mind that Botan 3 needs C++20, which causes issues on Qt5 QMake and Qt5 will still use the internal Botan 2 amalgamation Creating an amalgamation Botan 3 did work, but caused major issues with various build processes The filename in checksum files of the Qt6 AppImages in the releases on GitHub was fixed (for #3286) Download: QOwnNotes 25.6.0 | 37.3 MB (Open Source) Download: QOwnNotes for Other Operating Systems View: QOwnNotes Home Page | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • There are lots of older PC's that are now going to be unsupported on W10, but for modern systems, you should be able to update to W11. Even so, there are ways to bypass the official hardware requirements as well as the MSA (which imo is the bigger annoyance). My only gripe with W11 is all the telemetry collection / bloatware - yes, it is a problem compared to W10 and Linux.
    • Paying to see ads.. yeah no thanks. I wont even buy the full premium because your get in video sponsored ads. Ill stick to sponsor block. If youtube had its own built in sponsorblock where the uploader had to timestamp sections i might consider it, not at current prices though.
  • Recent Achievements

    • Week One Done
      Prestige Podiatry Care earned a badge
      Week One Done
    • Week One Done
      rollconults earned a badge
      Week One Done
    • One Month Later
      lilred1938 earned a badge
      One Month Later
    • Week One Done
      lilred1938 earned a badge
      Week One Done
    • Dedicated
      Leonard grant earned a badge
      Dedicated
  • Popular Contributors

    1. 1
      +primortal
      148
    2. 2
      Xenon
      132
    3. 3
      ATLien_0
      123
    4. 4
      +Edouard
      103
    5. 5
      snowy owl
      95
  • Tell a friend

    Love Neowin? Tell a friend!