• 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

    • Microsoft Defender XDR gets TITAN-powered Security Copilot recommendations by Paul Hill Guided Response, a Copilot-powered capability in Microsoft Defender XDR that guides analysts through step-by-step investigation and response flows, is getting a big upgrade with the introduction of TITAN recommendations. With TITAN, Microsoft wants to give security analysts real-time, threat-intel-driven recommendations so they can better prepare against attacks, before they even happen. TITAN is an adaptive threat intelligence graph that uses data from first and third-party telemetry and employs guilt-by-association techniques to warn analysts about unknown IP addresses that could pose a threat, due to their association with known malicious addresses. The primary benefit of TITAN is that security analysts get faster warnings about potential threats before they even have a chance to cause a problem. TITAN is an enhancement of Security Copilot Guided Response, rather than a replacement to it. With this extra tool, security analysts will be able to better keep up with evolving threats. Understanding TITAN's AI-powered threat intelligence The Redmond giant said that TITAN “represents a new wave of innovation” built upon its threat intelligence capabilities that introduces a real-time, adaptive threat intelligence graph. It takes telemetry from first and third-party sources such as Microsoft Defender for Threat Intelligence, Microsoft Defender for Experts, and customer feedback. The graph uses guilt-by-association techniques to mark unknown devices as threats, if they’re associated with known malicious entities. This gives security analysts a window of opportunity to take action and prevent harm. To identify potential threats, Microsoft uses a semi-supervised label propagation technique that assigns reputation scores to nodes based on the score of their neighbors. These reputation scores allow Microsoft’s unified security operation platform to implement containment and remediation actions via attack disruption. Practical impact and future outlook The new TITAN suggestion now appears within Guided Response as triage and containment recommendations. When a suspicious IP is detected, a Guided Response recommendation is automatically generated. These can help security analysts deal with various threats including IP addresses, IP ranges, and email senders. Microsoft said in early testing its TITAN recommendations have shown good results. TITAN boosted Guided Response triage accuracy by 8%, it reduced the time needed to investigate and respond to incidents, and its explainable recommendations gave analysts more confidence in the actions they take. As threats become more sophisticated, Microsoft’s TITAN will help to tackle threats before they even become an issue.
    • China wants the tech... if they were to invade, TSMC would destroy it's fabs and other critical information first. Plus, you can bet they have backups stored NOT in Taiwan.
    • Malware website host in China in 3….2…1
    • You totally missed what I was saying. Yes, the DHCP server software is largely unchanged since the Windows 2000 Server version. My statement was somewhat of a joke, but I was saying that *IF* the DHCP server was written using today's Windows 11 *ERA* design philosophy, it would be a mess. Yes, even on the latest version of Windows Server it still uses the old software, but that wasn't really the point.
    • Yeah no, they are nowhere at this same level. Whatsapp did survive a lot of time being "free" it is just meta/facebook wanting more money.
  • Recent Achievements

    • Enthusiast
      Motoman26 went up a rank
      Enthusiast
    • Mentor
      M. Murcek went up a rank
      Mentor
    • Explorer
      treker_ed went up a rank
      Explorer
    • Apprentice
      CHUNWEI went up a rank
      Apprentice
    • Veteran
      1337ish went up a rank
      Veteran
  • Popular Contributors

    1. 1
      +primortal
      673
    2. 2
      ATLien_0
      267
    3. 3
      +FloatingFatMan
      176
    4. 4
      Michael Scrip
      171
    5. 5
      Steven P.
      139
  • Tell a friend

    Love Neowin? Tell a friend!