• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Display all Unique values in cells one by one

Dhamo

New Member
Hi,


I have a huge data sheet where I have many columns. And I want unique values of column 'D' should be listed Row wise in another sheet.


Sample data sheet:

COLUMN A B

ROW 1 123 abcd

ROW 2 223 aDcd

ROW 3 323 aFcd

ROW 4 423 aFcd

ROW 5 523 abcd


OUTPUT IN SUMMARY SHEET:

COLUMN A B C

Row1 abcd aDcd aFcd


PS: I will not know about any details until I download it from a sql database. So the values are not constant.


Please help.
 
Hi Dhamo,


Does column A carry any significance (123,223 etc.)? I think it is not.


Does aFcd, Afcd, afCd, AFCD mean one and the same or case sensitivity matters in terms of uniqueness?


Edit: I am going to bed now so won't be able to reply till tomorrow.

[pre]
Code:
Public Sub GetUniques()
With CreateObject("Scripting.Dictionary")
'If comparison is case sensitive then remove comment from first option
'and comment out second option
'.comparemode = vbBinaryCompare
.comparemode = vbTextCompare
For i = 1 To Range("B" & Rows.Count).End(xlUp).Row
If Not .exists(Range("B" & i).Value) Then .Add Range("B" & i).Value, Range("B" & i).Value
Next i
Sheets("Sheet2").Range("A1").Resize(1, .Count) = .keys
End With
End Sub
[/pre]
Currently the code doesn't look for case sensitivity. Change it as commented if so.
 
In Cell A1 of Sheet2 Ctrl + Shift + Enter and not just Enter following formula and then copy it across:


Code:
=INDEX(Sheet1!$B$1:$B$5,SMALL(IF(FREQUENCY(MATCH(Sheet1!$B$1:$B$5,Sheet1!$B$1:$B$5,0),MATCH(Sheet1!$B$1:$B$5,Sheet1!$B$1:$B$5,0))>0,ROW(Sheet1!$B$1:$B$6)),COLUMNS(Sheet2!$A$1:Sheet2!A1)))


Note the adjustment made [ROW(Sheet1!$B$1:$B$6)] for additional element generated by frequency formula.


Edit: Please note that Array formulas though powerful tend to slow down sheet and affect its performance. So keep a tab on workbook performance. In such case, the macro would be more suitable tool. Not necessary, but just in case.
 
Back
Top