join data in multiple cells when adjoining cells match criteria ??

[angiec50]

This is DRIVING ME MAD.....
I have two workbooks. Workbook 1 is the final document which holds all the
information about our IT equipement from different areas. Workbook 2 shows
the PC number and the printers that it is assigned to.
What i am needed to do is show the printers that are shown in Workbook 2 in
Workbook 1 - but its not a simple concatenate.
In Workbook 2. Column A shows PC numbers, Column B shows the printers. If
more than one printer is assigned to a PC than the PC number will show
multiple times in Column A with the different printers in Column B. I am
needing to do a formula in Workbook 1 that says if the PC number in Workbook
1 matches any of the PC numbers in Workbook 2 then collect the printer data
in column B and concatenate to show in one cell.
Any ideas??????????

 

[Jacob Skaria]

Thought I have responded to this post earlier...Try this UDF (User Defined
function). From workbook launch VBE using Alt+F11. From menu Insert a Module
and paste the below function.Close and get back to workbook and try the below
formula.

In your case the formula would be

Suppose sheet1 A1 is PC number
=VLOOKUP_CONCAT(Sheet2!A1:B1000,A1,2)

OR with delimiter char(10) so as to display each printer in separate line
within the cell
=VLOOKUP_CONCAT(Sheet2!A1:B1000,A1,2,char(10))


Syntax:
=VLOOKUP_CONCAT(rngRange,strLookupValue,intColumn,strDelimiter)

rngRange is the Range
strLookupValue is the lookup string or cell reference
inColumn is the column to be concatenated
strDelimiter Optional . Default is space

Examples:
'1. To vlookup 'jacob' and concatenate all entries of 2nd column
=VLOOKUP_CONCAT(A1:B10,"jacob",2)

'2. with lookup value in cell C1
=VLOOKUP_CONCAT(A1:B10,C1,2)

'3. with delimiter as comma
=VLOOKUP_CONCAT(A1:B10,C1,2,",")

Function VLOOKUP_CONCAT(rngRange As Range, _
strLookupValue As String, intColumn As Integer, _
Optional strDelimiter As String = " ")
Dim lngRow As Long
For lngRow = 1 To rngRange.Rows.Count
If CStr(rngRange(lngRow, 1)) = strLookupValue Then _
VLOOKUP_CONCAT = VLOOKUP_CONCAT & strDelimiter & _
rngRange(lngRow, intColumn)
Next
VLOOKUP_CONCAT = Mid(VLOOKUP_CONCAT, len(strDelimiter)+1)
End Function


If this post helps click Yes