| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create list of unique entries for use in validation
Is there a function/formula I can put into a series of cells which can look
at a list (with repeat entries) and simply return the unique one. E.g. Tomato Tomato Banana Tomato Pear Banana Apple Pear Apple Tomato Would be displayed as Tomato Banana Pear Apple Then I can use Validation on this 'filtered' selection to create a drop down selection box. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create list of unique entries for use in validation
Say your original list is in Column A, starting in A1.
In B1 enter, =A1 In B2, enter this *array* formula: =IF(ISERR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0 )),"",INDEX(IF(ISBLANK($A*$ 1:$A$200),"",$A$1:$A$200),MATCH(0,COUNTIF(B$1:B1,$ A$1:$A$200&""),0))) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. You *must also* use CSE when revising the formula. *After* the CSE entry, copy down until you get #N/A errors. This tells you that you have exhausted the unique numbers in Column A. I have sized the formula to A200. You can revise that as necessary. As you add additional values to Column A, those errors will change to display additional uniques. You can then remove the formulas, leaving just the data behind by ... selecting the list in Column B, Right click in the selection and choose "Copy", Right click again, and choose "Paste Special", Click on <Values, then <OK, then <Esc. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "raphiel2063" wrote in message ... Is there a function/formula I can put into a series of cells which can look at a list (with repeat entries) and simply return the unique one. E.g. Tomato Tomato Banana Tomato Pear Banana Apple Pear Apple Tomato Would be displayed as Tomato Banana Pear Apple Then I can use Validation on this 'filtered' selection to create a drop down selection box. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create list of unique entries for use in validation
Works a treat. Thanks very much.
"raphiel2063" wrote: Is there a function/formula I can put into a series of cells which can look at a list (with repeat entries) and simply return the unique one. E.g. Tomato Tomato Banana Tomato Pear Banana Apple Pear Apple Tomato Would be displayed as Tomato Banana Pear Apple Then I can use Validation on this 'filtered' selection to create a drop down selection box. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create list of unique entries for use in validation
You're welcome, and thanks for the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "raphiel2063" wrote in message ... Works a treat. Thanks very much. "raphiel2063" wrote: Is there a function/formula I can put into a series of cells which can look at a list (with repeat entries) and simply return the unique one. E.g. Tomato Tomato Banana Tomato Pear Banana Apple Pear Apple Tomato Would be displayed as Tomato Banana Pear Apple Then I can use Validation on this 'filtered' selection to create a drop down selection box. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create list of unique entries for use in validation
Try this:
=IF(ROWS($1:1)<=COUNTA(A1:A20),INDEX(A1:A20,SMALL( IF(A1:A20<"",ROW(A1:A20)-MIN(ROW(A1:A20))+1),ROWS($1:1))),"") This is a CSE function (Ctrl + Shift + Enter) -- RyGuy "raphiel2063" wrote: Is there a function/formula I can put into a series of cells which can look at a list (with repeat entries) and simply return the unique one. E.g. Tomato Tomato Banana Tomato Pear Banana Apple Pear Apple Tomato Would be displayed as Tomato Banana Pear Apple Then I can use Validation on this 'filtered' selection to create a drop down selection box. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create list of unique entries for use in validation
Uh, sorry, that function that I posted removes blank spaces...
Try this (I got it on this DG a while back): Sub Uniques() Dim i As Integer i = 1 Do Until Cells(i, 1).Value = "" '(as long as your data is in column 1) If Cells(i, 1) = Cells(i + 1, 1) Then Else Cells(i, 1).Copy Cells(i, 5).PasteSpecial xlValues '(this pastes into column E) End If i = i + 1 Loop Range("E5:E1000").Sort Key1:=Range("E5"), Order1:=xlAscending Columns("E:E").Select Selection.Sort Key1:=Range("E1"), Order1:=xlAscending Range("A1").Select End Sub -- RyGuy "ryguy7272" wrote: Try this: =IF(ROWS($1:1)<=COUNTA(A1:A20),INDEX(A1:A20,SMALL( IF(A1:A20<"",ROW(A1:A20)-MIN(ROW(A1:A20))+1),ROWS($1:1))),"") This is a CSE function (Ctrl + Shift + Enter) -- RyGuy "raphiel2063" wrote: Is there a function/formula I can put into a series of cells which can look at a list (with repeat entries) and simply return the unique one. E.g. Tomato Tomato Banana Tomato Pear Banana Apple Pear Apple Tomato Would be displayed as Tomato Banana Pear Apple Then I can use Validation on this 'filtered' selection to create a drop down selection box. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to identify unique list of 200 random entries from a list of 3 | Excel Worksheet Functions | |||
| Unique list of entries | Excel Worksheet Functions | |||
| DataValidationList - Unique Entries | New Users to Excel | |||
| Best way to get a list of unique entries in a field | Excel Worksheet Functions | |||
| counting unique entries in a list | Excel Discussion (Misc queries) | |||
Hybrid Mode
