Excel - Matching one to many from one sheet to another, then copying

[bluegnu]

Difficult one for me to create a catchy subject. Basically I have 2 worksheets that look like this:

Sheet1:

Reference    Description

SAA1         E001, E003
SAA2         E005
SAA3         E001, E003, E005, E004

Sheet2:

Number       Xref

E001
E002
E003
E004
E005

What I would like to do is take the description column in sheet one and match each of the numbers with the Number column in sheet 2 and then populate the xref column with the appropriate Reference number. ie:

 Sheet2 would look like this:

Number       Xref

E001         SAA1, SAA3
E002         
E003         SAA1, SAA3
E004         SAA3
E005         SAA2, SAA3

Not sure if that makes sense, but I hope you can help. Many thanks

 

[Bong]

Using split you can get an array, which is really a collection. Let's say in cell(4,2) you have "E001,E002,E003"
Then a=split(cells(4,2),",") returns a 3-element array.
Then you can look up each element in turn: for each i in a... and append the corresponding reference value to the cell you want.

_________________
Bob Rashkin

 

[MajP]

I do not do much work in excel so someone can use this as a start. This kind of works except I can not drag copy the formula. I have to paste the formula in each cell.

 Public Function getReference(inputRange As Range) As String
  Dim strNumber As String
  Dim strReference As String
  Dim strDescription As String
  Dim aNumbers() As String
  Dim myRow As Range
  strNumber = ActiveCell.Offset(0, -1).Value
  For Each myRow In inputRange
    
    If Not InStr(1, myRow.Value, strNumber) = 0 Then
       getReference = getReference & myRow.Offset(0, -1).Value & ", "
    End If
  Next myRow
  If Len(getReference) > 0 Then
    getReference = Left(getReference, Len(getReference) - 2)
  End If
End Function

The input range is the description column on sheet 1. Then change the range to an absolute reference by changing adding $ signs (ex =getReference(Sheet1!$B$2:$B$4)). Now Paste individually in each cell.

I will need someone to show me how to change the input range into an absolute reference through code.