Unique records in List

[haissk]

I have an excel file in which say i have some items in Col A
Apple
Banana
Grapes
Banana
Apple
Grapes
Mango
Mango

now i want a create a list dropdown in Cell B1 with unique values from Col A... any help would be appreaciated

 

[snoopyhr]

The formula provided by Yahya can be improved to return sorted listed regardless of order condition of original list.

here it is:

Excel Workbook
	B	C	D	E
4	List	*	Sorted	Yahya result, not sorted
5	Apple	*	Apple	Apple
6	Mango	*	Cucumber	Mango
7	Mango	*	Grapes	Pineapple
8	Pineapple	*	Mango	Grapes
9	Apple	*	Pineapple	Cucumber
10	Grapes	*	*	*
11	Cucumber	*	*	*
12	Pineapple	*	*	*
13	Mango	*	*	*
Sheet1

Regardless of order in which the list created, the first formula always returns sorted unique list.
Note: I used if(iserror...) instead of Iferror .... I have Excel 2003

 

[GTO]

Hi GTO,

Yes J is oversized then J is resized by count uniques which is contained in n.

Biz

Thank you Biz

I do not believe I knew (or I had most certainly forgotten) that you could plop a bigger array into a smaller range and the excess would just drop out.

Thanks again,

Mark

 

[Biz]

Thank you Biz

I do not believe I knew (or I had most certainly forgotten) that you could plop a bigger array into a smaller range and the excess would just drop out.

Thanks again,

Mark

Hi Mark,

I am still learning. Do you think i should redim array?

Biz

 

[GTO]

Hi Mark,

I am still learning. Do you think i should redim array?

Biz

Hi Biz,

No. I was being sincere in stating that I didn't realize or had forgotten. I see no harm in letting the array 'over-run' harmlessly. Also, since it would be the first dimension that would need reduced, this is not possible (I am presuming you meant Redim Preserve), as only the last dimension can be resized and retain the values in the array.

I had come up with:

Sub exa()
Dim Vals, Val1
    Vals = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)).Value
    If IsArray(Vals) Then
        With CreateObject("Scripting.Dictionary")
            For Each Val1 In Vals
                If Not IsEmpty(Val1) Then .Item(Val1) = Empty
            Next
            Range("J1").Resize(Rows.Count).ClearContents
            Range("J1").Resize(UBound(.Keys) + 1).Value = Application.Transpose(.Keys)
        End With
    End If
End Sub

...which was a few lines shorter and I initially was wondering about any advantage to the "extra" steps yours uses in IF .Exists and building the array. After thinking about it, (and FWIW from a fellow "still learning plenty") if I was going to only do it one way, I would use yours, since worrying about memory limitations (for a 'weaker' laptop for instance) with .Transpose is no worry at all. Nice

 

[Biz]

Hi Biz,

No. I was being sincere in stating that I didn't realize or had forgotten. I see no harm in letting the array 'over-run' harmlessly. Also, since it would be the first dimension that would need reduced, this is not possible (I am presuming you meant Redim Preserve), as only the last dimension can be resized and retain the values in the array.

I had come up with:

Sub exa()
Dim Vals, Val1
    Vals = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)).Value
    If IsArray(Vals) Then
        With CreateObject("Scripting.Dictionary")
            For Each Val1 In Vals
                If Not IsEmpty(Val1) Then .Item(Val1) = Empty
            Next
            Range("J1").Resize(Rows.Count).ClearContents
            Range("J1").Resize(UBound(.Keys) + 1).Value = Application.Transpose(.Keys)
        End With
    End If
End Sub

...which was a few lines shorter and I initially was wondering about any advantage to the "extra" steps yours uses in IF .Exists and building the array. After thinking about it, (and FWIW from a fellow "still learning plenty") if I was going to only do it one way, I would use yours, since worrying about memory limitations (for a 'weaker' laptop for instance) with .Transpose is no worry at all. Nice

Hi Mark,
You r right I meant Redim Preserve. Still learning from people coding techniques. This place like Amazon forest of kool techniques.

Biz