Make list of cells in array and checking for duplicates

[Jenn]

I have an array B2:F11, that has text in each cell. I need to make a
concantenated list in H2 where cell values are separated by commas. I also
need to make sure that the values in the compiled list are all unique,
because there is a chance that the same value will exist in more than one
cell.

The last thing I need to do is make sure the compiled list is in descending
order.

Can anyone help?

Thanks!

 

[Gary''s Student]

Hi Jenna:

Try this little macro as a start:

Sub jena()
Set r1 = Range("B2:F11")
Set r2 = Range("Z1")
j = 0
For Each r In r1
r2.Offset(j, 0).Value = r.Value
j = j + 1
Next
Range("Z1:Z50").Sort Key1:=Range("Z1"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

For i = 50 To 2 Step -1
If Cells(i, "Z").Value = Cells(i - 1, "Z").Value Then
Cells(i, "Z").Delete Shift:=xlUp
End If
Next

v = ""
n = Cells(Rows.Count, "Z").End(xlUp).Row
For i = 1 To n
v = v & Cells(i, "Z").Value & ","
Next

Range("H2").Value = v
End Sub

It uses Z1 thru Z50 as helper cells to simplify the sort.