Unique Item N in an Excel List

[DQR]

Is there an Excel function which will return the Nth unique item in a list please? For example, if I have the names Albert, Bessie, Carrie, Albert, Carrie, Carrie & Davina in cells A1:A7 respectively, I want FX(A1:A7,1) to return the value Albert, FX(A1:A7,2) to return the value Bessie and FX(A1:A7,3) to return the value Carrie etc, for some function FX.

I can sort the list if necessary, and run a set of checks on whether the Nth item equals the (N+1)th item, but this is rather messy. I'm hoping there is an easier way, but I can't see one; also I can't find a keyword that produces any useful response from the online help.

Many thanks for any help received.
DQR

 

[bluedragon2]

Is the ultimate goal to extract all unigue items from the list?



Blue

If I wasn't Blue, I would just be a Dragon...

 

[TLady]

I want
FX(A1:A7,1) to return the value Albert,
FX(A1:A7,2) to return the value Bessie and
FX(A1:A7,3) to return the value Carrie etc, for some function FX.

So, practically you want to loop through every cell and read values ? I don't see "unique" part ...
Or you want to loop through every cell and read values those are different from values already read ???
"xplain

 

[xlbo]

Think you'll need a UDF for this one:

Option Base 1
Function GetUnique(mItem As Integer, rng As Range)
Dim sArr As Variant, uArr() As Variant, loopItem As Integer, isUnique As Boolean
sArr = Range(rng.Address)
ReDim uArr(UBound(sArr))
uArr(1) = sArr(1, 1)
For i = 2 To UBound(sArr)
For x = LBound(uArr) To UBound(uArr)
isUnique = True
If sArr(i, 1) = uArr(x) Then
isUnique = False
Exit For
End If
If uArr(x) = "" Then
loopItem = x
Exit For
End If
Next x

If isUnique = True Then uArr(loopItem) = sArr(i, 1)
Next i
ReDim Preserve uArr(loopItem)
If mItem > UBound(uArr) Then GetUnique = "Greater than total unique values" Else GetUnique = uArr(mItem)

End Function


function would be
=GetUnique(3,A1:A50)

this will get you the 3rd unique item in A1:A50

upper and lower case will be treated as different - if you don't want that then change:
If sArr(i, 1) = uArr(x) Then
to
If upper(sArr(i, 1)) = upper(uArr(x)) Then

HTH

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[DQR]

BlueDragon2, TLady and Geoff, many thanks for your replies and your time. This note is just to summarise both the problem and to my progress with it since the original posting: I think it's now solved.

BlueDragon2 - I actually want to be able to select the Nth ordered entry from the list, ignoring duplicates, and to copy that entry into a cell of my choosing. I originally assumed this would be an easy task, because if I apply a filter to the original list (using the Filter item on the Data menu), the resulting drop-down menu supplies me with exactly the list of choices I want, from which all I'd have to do is pick the 1st, 2nd, 3rd item or whatever. I wasn't however able to find a built-in function that would perform that task.

TLady - my apologies for not making the problem clearer. You are right in saying that I effectively want to loop through every cell and read those values that are different from values already read - except that I want to select the Nth ordered item from that list.

Geoff - many thanks for your GetUnique function: it looks like exactly what I'm after, given that there doesn't appear to be a built-in function to do what I want.

I've been experimenting since placing the original query, and have discovered that setting up a pivot table (on the Data menu) comes very close to what I need, since the row or column headings of that table can be made to contain the list I want. The only problem with it is that I'd like the list I produce to update automatically each time I change the original list - and pivot tables don't automatically update when the original list is changed.

I've also been looking for anything previously posted on Tek-Tips, and found a reference to a website which discusses duplicate and unique items in lists (

http://www.cpearson.com/excel/duplicat.htm).

Under "Extracting Unique Entries" is made the same point as in the previous paragraph about automatic updating, together with an array formula which apparently cracks the problem - so I think my problem might also be solvable this way. I'm now going to do some experimenting...

Again, thank you all for your time and efforts.
Best regards,
DQR

 

[xlbo]

If you just want a list of unique items, have a look at data>filter>advanced filter

This has an option for unique values

Also, with the pivot table, how about coding an auto_refresh.....

you could set up the worksheet change event to only fire when an item in col A is changed:

If target.column <> 1 then exit sub
thisworkbook.refreshall

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244