load only one instance(no duplicates)

[white6174]

What I'm trying to do is use either data validation or a list box and have it loaded with text from a range (B4:B100) and have it not load duplicates.

Any ideas would be appreciated

thanks steve w

 

[Chris Davison]

*chuckles*

thanks Aladin

 

[Jay Petrulis]

Hi,

The following takes the entries in a defined range "MyRange" and creates a name called "UniqueValues" which is an array of the unique items in the list.

Sub test()
Dim Rng1 As Range, x As Long
Dim RangeRows As Long
Dim ArrayUnique()
Dim y As Long, lngCount As Long
Dim RngStartCol As Integer, RngStartRow As Long

Dim aaa, bbb, ccc, ddd

Set Rng1 = Range("MyRange")
RangeRows = Rng1.Rows.Count
RngStartRow = Rng1.Row
RngStartCol = Rng1.Column

x = _
Evaluate("=SUM(IF(LEN(" & Rng1.Address & "),1/COUNTIF(" _
& Rng1.Address & "," & Rng1.Address & ")))")

ReDim ArrayUnique(1 To x)

For y = 1 To RangeRows
    aaa = Cells(RngStartRow, RngStartCol).Address
    bbb = Cells(y + RngStartRow - 1, RngStartCol).Address
    ccc = Range(aaa, bbb).Address
    ddd = WorksheetFunction.CountIf(Range(ccc), Range(bbb))
    If ddd = 1 Then
        lngCount = lngCount + 1
        ArrayUnique(lngCount) = Range(bbb)
    End If
Next y
ActiveWorkbook.Names.Add Name:="UniqueValues", RefersToR1C1:=ArrayUnique

End Sub

You can see the results by viewing Insert>Name>Define

There are (at least) two things I haven't been able to resolve, so this is an idea that others may take and improve.

1. Only handles a vertical list in one column.

2. Cannot yet be used in the data validation list, because the results are
{1,2,3,"a","b"} and the like, but the data validation souce requires that these items be separated, and without the braces -- ungrouped list. If this is not possible, then the Advanced filter/Aladin method is definitely the way to go.

BTW, did you try the function/event routine I posted to your other thread?

Bye,
Jay

EDIT: Oops! Sorry, I didn't read Damon's response carefully. He has got a working procedure, I believe. The above might have some value for other purposes, but it doesn't work for this task.
This message was edited by Jay Petrulis on 2002-05-18 07:43

 

[Aladin Akyurek]

On 2002-05-18 07:24, Jay Petrulis wrote:
Hi,

The following takes the entries in a defined range "MyRange" and creates a name called "UniqueValues" which is an array of the unique items in the list.

Sub test()
Dim Rng1 As Range, x As Long
Dim RangeRows As Long
Dim ArrayUnique()
Dim y As Long, lngCount As Long
Dim RngStartCol As Integer, RngStartRow As Long

Dim aaa, bbb, ccc, ddd

Set Rng1 = Range("MyRange")
RangeRows = Rng1.Rows.Count
RngStartRow = Rng1.Row
RngStartCol = Rng1.Column

x = _
Evaluate("=SUM(IF(LEN(" & Rng1.Address & "),1/COUNTIF(" _
& Rng1.Address & "," & Rng1.Address & ")))")

ReDim ArrayUnique(1 To x)

For y = 1 To RangeRows
    aaa = Cells(RngStartRow, RngStartCol).Address
    bbb = Cells(y + RngStartRow - 1, RngStartCol).Address
    ccc = Range(aaa, bbb).Address
    ddd = WorksheetFunction.CountIf(Range(ccc), Range(bbb))
    If ddd = 1 Then
        lngCount = lngCount + 1
        ArrayUnique(lngCount) = Range(bbb)
    End If
Next y
ActiveWorkbook.Names.Add Name:="UniqueValues", RefersToR1C1:=ArrayUnique

End Sub

You can see the results by viewing Insert>Name>Define

There are (at least) two things I haven't been able to resolve, so this is an idea that others may take and improve.

1. Only handles a vertical list in one column.

2. Cannot yet be used in the data validation list, because the results are
{1,2,3,"a","b"} and the like, but the data validation souce requires that these items be separated, and without the braces -- ungrouped list. If this is not possible, then the Advanced filter/Aladin method is definitely the way to go.

BTW, did you try the function/event routine I posted to your other thread?

Bye,
Jay

EDIT: Oops! Sorry, I didn't read Damon's response carefully. He has got a working procedure, I believe. The above might have some value for other purposes, but it doesn't work for this task.
This message was edited by Jay Petrulis on 2002-05-18 07:43

What is wrong with 3 methods I described, which are meant to create a unique list from an area that is subject to change?

Aladin

 

[Jay Petrulis]

On 2002-05-18 08:50, Aladin Akyurek wrote:

---snip--

What is wrong with 3 methods I described, which are meant to create a unique list from an area that is subject to change?

Aladin

Hi Aladin,

Nothing at all. Excellent method(s) offered. I was just trying to see if it could be done entirely in VBA, without using any worksheet cells except the ones already used.

Bye,
Jay

 

[white6174]

Thanks guys for all the help, this is going to work great once I finish it.

steve w

 

[white6174]

Thanks Aladin
that worked great
it was exactly what I was looking for.

Thanks to everyone who helped me and gave some imput it is greatly appreciated

steve w

 

[Ian Mac]

All,

Excellent answer by all here, Top Stuff.

BUT! if I may have a stab at what I believe is a far simpler answer.

Pivot Tables!!

First goto Insert>Name>Define
type in something like ListRange
then in the Refers To: box put =OFFSET(Sheet1!$B$4,0,0,COUNTA(Sheet1!$B:$B),1)

Click Add and OK

Now goto Data>Pivot Tables
Click Next
in 'where is the data you want to use?' type ListRange
Click Next

Now this depend what version of Excel you have.

if 97 in part 3 of 4 place the the name you have there (this should be the Field Title at the top of the column) in the Row AND Data part.
Click finish (check part 4 of 4 (I can't I don't have 97 at home))

If XP (I don't have 200 installed currently, but may be the same)
in Step 3 of 3 choose which ever option you need for where the pivot table will go.
Click finish.
Drag the list to the Row part that is on the sheet (note: you don't seem to need the data part for XP).
now you should have your list.

you can hide the Count column if needed at you should have a unique list of name you can use.

Hope this helps, I personally think this is so much easier and you can refresh the data by Right clicking on the list and 'Refresh Data'

 

[tealeaf]

On 2002-05-17 13:20, Aladin Akyurek wrote:

On 2002-05-17 12:58, white6174 wrote:
Thanks I think it will work.

Do you know how to set it up to do this?

What should the criteria be?
a formula?

Thanks for the help

steve w

Put a label before your data if you don't have one already.

Make the label distinct by formatting it as bold and italic.

Activate the cell immediately underneath the label.
Activate Data|Filter|Advanced Filter.
Check Copy to another location.
Make sure List range shows the whole range of data.
Leave the Criteria range box empty.
Enter a cell ref outside of your data area for Copy to.
Check Unique records only.
Activate OK.

Select all of the cells of the new range except the label, go to the Name Box on the Formula Bar, type List, and hit enter.

You can now use List as Source in data validation to get a duplicate-free dropdown list.

hi i tried to use the advanced filter, but for some reason my list:
888
888
888
OAE
OAE
OMS
OMS

becomes
888
888
OAE
OMS

why is the 888 duplicated? how do i get this to work so it will only display once?

 

[Aladin Akyurek]

On 2002-07-23 08:27, tealeaf wrote:

On 2002-05-17 13:20, Aladin Akyurek wrote:

On 2002-05-17 12:58, white6174 wrote:
Thanks I think it will work.

Do you know how to set it up to do this?

What should the criteria be?
a formula?

Thanks for the help

steve w

Put a label before your data if you don't have one already.

Make the label distinct by formatting it as bold and italic.

Activate the cell immediately underneath the label.
Activate Data|Filter|Advanced Filter.
Check Copy to another location.
Make sure List range shows the whole range of data.
Leave the Criteria range box empty.
Enter a cell ref outside of your data area for Copy to.
Check Unique records only.
Activate OK.

Select all of the cells of the new range except the label, go to the Name Box on the Formula Bar, type List, and hit enter.

You can now use List as Source in data validation to get a duplicate-free dropdown list.

hi i tried to use the advanced filter, but for some reason my list:
888
888
888
OAE
OAE
OMS
OMS

becomes
888
888
OAE
OMS

why is the 888 duplicated? how do i get this to work so it will only display once?

I'll assume that your list starts with a label, say, Items which is distinctly formatted, say, in bold and italic. If this is not case, make a label before the list and format it distinctly and run the procedure anew.

If you have a distict label and did the right thing at every step, and I assume you did, these two 888's must have different underlying formattings. Or, if both are text formatted, then one of them has probably has leading/trailing space(s).

Use

=ISNUMBER(cell)

to check whether an entry is a number.

Use

=LEN(cell)

to check the length of an entry.

Use

=TRIM(cell)

to get rid of the extraneous spaces.

Aladin

 

[tealeaf]

thanx aladin, turns out the problem with what i was doing was that, my column was part of a SQL query, so the label was not obvious.
everytime the table is updated, i want to make a new list with only unique items, and the other column would be the sum of all the duplicates.