To concatenate with VBA just use "&".

For example:

Sub Sample1()
Cells(5, 5).Value = Cells(5, 6).Value & Cells(5, 7).Value
End Sub

To put a space and comma between the two use this:

Sub Sample2()
Cells(5, 5).Value = Cells(5, 6).Value & _
", " & Cells(5, 7).Value
End Sub

To sort the data, just record a macro while sorting and
study the programming language. To do this for many cells
you will have to loop through....

I'm not sure exactly what you are trying to do, but I hope
that gets you started.

-----Original Message-----
This is really bugging me but I'm sure it can be done...

I want to concantenate all the values from one column
that match a
given criteria - into one cell. I have tried using
worksheet
functions but it's getting too complicated - how would I
do this using
VBA?

e.g.

Column A - Column B
1)oranges - box3
2)apples - box2
3)pears - box2
4)bananas - box1
5)peaches - box2

using the above example I would like Column C to show
what else is in
the box with the item already shown on that row, e.g.

Column A - Column B - Column C
1)oranges - box3 - sole item
2)apples - box2 - pears, peaches
3)pears - box2 - apples, peaches
4)apples - box1 - sole item
5)peaches- box2 - apples, pears

At this stage I'm not concerned about the order items are
shown in
Column C, although if it is possible to sort them from
left to right in
alpha order this would be a bonus (e.g. C2 above would
then read
"peaches, pears").

Any Suggestions?


---
Message posted from http://www.ExcelForum.com/

.