Solved: Concatenate If in an Array

[JimS]

I’m trying to concatenate several cells that have the same Order Number.

Column A has Order Numbers (range name = OrdN) and Column B contains part names (range name = PartN) that are on the order.

I use Dynamic Range Names because the number of data rows can be very long, and changes daily.

An order can have 1 to several rows depending on how many parts are on the order.

I would like to Concatenate (on a different sheet) the Part Names (ie: Nut, Bolt) where Cell A2 would be the first Order Number and Cell B2 would be the concatenated Part Names for that order, then fill in the next order in row 3.

Attached is an example showing the data and the results that I'm trying to get.

I have seen several formulas and code on this subject but cannot find any that work.

Thanks for any and all help.

JimS

[mancubus]

you need a UDF.

copy the following in a standard module of your workbook.


Public Function FindSeries(TRange As Range, MatchWith As String)
'http://www.ozgrid.com/forum/showthread.php?t=25239&page=1

For Each cell In TRange
If cell.Value = MatchWith Then
x = x & cell.Offset(0, 1).Value & ", "
End If
Next cell

FindSeries = Left(x, (Len(x) - 2))

End Function



formula in cell B2 in sheet Summary, copied down:

=FindSeries(Data!A:A,Summary!A2)


or

=FindSeries(OrdN,Summary!A2)

or

=FindSeries(OrdN,11589)

[JimS]

Excellent, works perfect.

THANK YOU VERY MUCH!!!

[mancubus]

wellcome.

credits to ozgrid.com