Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Make list of cells in array and checking for duplicates
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Make list of cells in array and checking for duplicates
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. -- Gary''s Student - gsnu200771 "Jenn" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
checking for duplicates | Excel Worksheet Functions | |||
Array Formulas - Unique List from List with Duplicates | Excel Discussion (Misc queries) | |||
Error Checking for Duplicates in List | Excel Worksheet Functions | |||
Checking for duplicates - think this is simple | Excel Discussion (Misc queries) | |||
Make a list that excludes duplicates | Excel Discussion (Misc queries) |