Nice code Sandy;
Using the example I Inserted 5 rows at top of sheet
before showing the table (with 3 header columns on Row 6, data begins on
Row7 (A:C)
Where your code shows:
For x = 1 To EndRow
I would have thought that I would need to substitute "7" for your 1
but it doesn't seem to matter...
I'm sure I'm missing something here, but what?
TIA,



"Sandy Mann" wrote in message
...
Simon,

Would a custom function do?
This is a simplified example that assumes that the data is in columns A:C
starting in Row 1.

Functions cannot change the environment so you would have to select to
wrap
text and expand the row height yourself.

Public Function Test(TruckNo) As String
Dim EndRow As Long
Dim NN As String
Dim x as Long

EndRow = Cells(Rows.Count, 1).End(xlUp)

For x = 1 To EndRow
If Cells(x, 1).Value = TruckNo Then NN = NN & Cells(x, 2).Value _
& " : " & Cells(x, 3).Value & Chr(10)
Next x

NN = Left(NN, Len(NN) - 1)

Test = NN

End Function

HTH

Sandy

--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"Simon Shaw" wrote in message
...
Is there a function like SUMIF for text values that concatenates rather
than
sums?

I have a set of data as follows:

Truck No. Product Comments
----------- --------- ------------------
123 R-Gas Fully Reconciled
456 R-Gas Tank Dried
123 G-Gas Refilled

There are other columns of numbers. I then have a summary row for each
truck
number using sumif for the number columns, but I need to concatenate the
comments in the summary row for say truck 123 to be:
"R-Gas"&":"&" Fully Reconciled"&", "&Char(10)&"R-Gas"&":"&" Refilled"
with the results in one text wrapped cell displayed as follows:
R-Gas: Fully Reconciled
R-Gas: Refilled

Thanks