Question and Answers

Microsoft Excel question, how to display unique value from list with repeated numbers? - Sgt Lee Microsoft Excel question, how to display unique value from list with repeated numbers without manually doing it with the filter everytime? Hello, Is there a function or micro that will designate a cell or group of cells (column) to display the unique numbers from another column or group of cells? For example: Original text: after function to look up value: A1 hello B1 Hello A2 goodbye B2 Goodbye A3 hola B3 Hola A4 hello So the resulting group of cells only display the UNIQUE numbers or in this case text. I know how to use the filter manually every time by selecting the cells, but there has to be an easier way, something automated. Thanks in advance!!

Best Answer: With the list (A1:A4) you provided, put the following formula in B1 =IF(ROW()=1 ,A1,IF( ISNA(MATCH( A1,$A$1:INDIRECT( ADDRESS( ROW()-1, COLUMN()-1,4)) ,0)>0),A1,"")) then copy B1 to B2:B4, 1st occurrence for each unique value found in Column A will show in Column B. - AQuestionMark

The following will work for numbers, but not text: In cell B1, type the following, then hit Enter: =LARGE(A:A,1) then in cell B2, type the following, then hit Ctrl+Shift+Enter: =IF(ISERR(LARGE(A:A,1+ SUM(COUNTIF(A:A,$B$1:B1)))),"", LARGE(A:A,1+SUM(COUNTIF(A:A, $B$1:B1)))) (Sorry for the wraparound, it won't fit on a line) Now, copy B2 down the column as needed. - Wesley M

Function UniqueList() Range("OrderID").AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Range("UniqueList"), Unique:=True Range("UniqueList").Activate UserForm1.cboUniqueList.RowSource = Selection.CurrentRegion.Address UserForm1.Show Selection.CurrentRegion.Clear End Function - Wet Calamari

Report Abuse

Best Answers are selected by the person who asked the question or other Yahoo! users. Find out more at Yahoo! Answers.