0

I have one worksheet Relationships which contains data entries specifying a relationship between A and B and some related data.

In another worksheet, I have rows for each A and would like a cell that effectively queries the Relationships data and summarizes all B who have a relationship with A (and some of the related data).

I have written a VBA lookup to do this, but I imagine it is the least efficient approach possible, as it loops through the entire range in Relationships, determines if the subject row is one relating to A, and if it is, it starts concatenating a string which it ultimately returns.

Just wanted to know if anyone can point me in the right direction for an approach to this problem that is hopefully more efficient? VLOOKUP doesn't seem to be very helpful because of the "multiple match" issue.

As requested, an example of what I used - it works fine, but I fear it is slow and will become crippling as the data grows.

Function oLookup(lookupval, lookuprange As Range, entity As Long, addlbylines As Long, name As Long, title As Long, Optional count As Variant)
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange.Rows
    If (x.Cells(1, entity).Value = lookupval And lookupval <> "") Then
        result = result & x.Cells(1, name) & " (" & x.Cells(1, title) & ")"
        If Not IsMissing(count) Then result = result & " [Freq: " & x.Cells(1, count) & "]"
        result = result & Chr(10)
    End If
Next x

If result = "" Then result = "No data" Else result = Left(result, Len(result) - 1)
oLookup = result
End Function
user973066
  • 33
  • 4
  • Please include the vba you tried. – Scott Craner Feb 25 '21 at 23:01
  • Added it to the post. – user973066 Feb 25 '21 at 23:08
  • 1
    take a look at this: https://stackoverflow.com/questions/56858571/merge-values-of-column-b-based-on-common-values-on-column-a – Scott Craner Feb 25 '21 at 23:08
  • This is a bit beyond my level and will probably take a bit of time to pick apart and understand. I can see from the output that it can probably be adapted to what I am trying to do - is it faster as well? – user973066 Feb 25 '21 at 23:16
  • 1
    much as it uses variant arrays instead of the actual range. And in your case you would just use: `=TEXTJOINIFS(Relationships!B:B,CHAR(10),Relationships!A:A,A2)` – Scott Craner Feb 25 '21 at 23:18
  • Thanks, I think I will need to study it - as you can see from the above code I am trying to pull from different columns in the row to construct a slightly more involved concatenation, like: Item1Field 1 (Item1Field 2), Item2Field1 (Item2Field2) , ... – user973066 Feb 25 '21 at 23:31
  • 1
    Then look how I moved the ranges into variant arrays and looped those. do not loop ranges it is slow. – Scott Craner Feb 25 '21 at 23:54
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/229226/discussion-between-user973066-and-scott-craner). – user973066 Feb 26 '21 at 00:01

0 Answers0