List unique values from a named range

Andrew_K99

New Member
Joined
Dec 4, 2007
Messages
26
I am trying to find a way to create a list of unique items from a named range. I have found a few solutions using filters for ranges that are contained in the one column but not named ranges that contain multiple rows and multiple columns.

Is there any way to copy all of the unique items from the named range to another location? Lets call the named range Table1 and it spans from B2 to J100.

Thanks in advance!!
Andrew
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
So you are saying you want to look in a range that spans across rows and columns and return the unique values to another location, like:
Book1
ABCDE
1121634
223816
339112
447349
555128
67
75
84
93
102
111
Sheet1


To do this you would need to download the morefunc add-in at this site:

http://xcell05.free.fr/english/

The formula in column E is an array formula and is confirmed with control + shift + enter. You can replace the range with a named range as your example specifies.

The formula would be:

Code:
=UNIQUEVALUES(Table1)

In my example Table1 is A1:C5. If you need any help with array formulas, just post back.
 
Upvote 0
Thanks for the quick reply.

Is there a way to do this using VBA or other? Being a work computer I don't know if I want to download and install anything. The file would also be used by multiple users.

Andrew
 
Upvote 0
This would alphabetically list Table1's unique values in column L, considering column L is available and Table1 really does only occupy up to column J with column K being empty. Can be modified for more dynamic column availability but this is one option:

Code:
Sub Test1()
Application.ScreenUpdating = False
Dim cell As Range, x&, varCell As Variant
x = 2
Columns(12).Clear
Range("L1").Value = "Unique entries in Table1"
For Each cell In Range("Table1")
varCell = Application.Match(cell.Value, Columns(12), 0)
If IsError(varCell) Then
Err.Clear
Cells(x, 12).Value = cell.Value
x = x + 1
End If
Next cell
Range("L1").CurrentRegion.Sort Key1:=Range("L2"), Order1:=xlAscending, Header:=xlYes
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Unfortunately I cannot think of a way using a formula outside of uniquevalues in morefunc. It would be possible to do through VBA, but it would be intensive code and would probably take a long time on a large range of data the way I am thinking. Here is my thought, try it out:

Code:
Sub listUnique()
Application.ScreenUpdating = False
Dim c As Range
Dim counter As Long
counter = 1
For Each c In Range("Table1")
If WorksheetFunction.CountIf(Range("K:K"), c.Value) = 0 Then
Cells(counter, "K").Value = c.Value
counter = counter + 1
End If
Next c
Application.ScreenUpdating = True
End Sub
This is putting the unique list in column K on the same sheet, but you can have it put on another sheet easily.
 
Upvote 0
Thats awesome Tom! Thanks

Would I be pushing my luck if I asked that that could take the unique items from multiple named ranges? IE table1 table2 etc.

Thanks again!
 
Upvote 0
If Tom's works like mine, which it looks like it does. run the macro for Table1 and then run the macro for the other tables. You could use an input box to put in the named range or create a list for it to loop through. One question I have is that will the list only continue building unique values. Being that if a value in Table2 was already listed from Table1 then you would not want it listed again? If so both approaches should work.
 
Upvote 0
Thats awesome Tom! Thanks

Would I be pushing my luck if I asked that that could take the unique items from multiple named ranges? IE table1 table2 etc.

Thanks again!
Assuming column L (column 12) is still available and not occupied by those other tables, then yes, it's simple with one run through the macro by substituting this codeline in what I first posted:
For Each cell In Range("Table1")

with this:
For Each cell In Range("Table1, Table2")

If you add more named ranges, just follow that syntax, example
For Each cell In Range("Table1, Table2, Table3")
 
Upvote 0
Might be a bit over the top, but works with multi-dimensional arrays and range selections...




<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Sub</SPAN> RtlMoveMemory <SPAN style="color:#00007F">Lib</SPAN> "kernel32" ( _
                          Destination <SPAN style="color:#00007F">As</SPAN> Any, _
                          Source <SPAN style="color:#00007F">As</SPAN> Any, _
                          <SPAN style="color:#00007F">ByVal</SPAN> Length <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>)

<SPAN style="color:#00007F">Sub</SPAN> testUL()
<SPAN style="color:#007F00">'Originally written by firefytr</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> x, y, s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Selection.Cells.Count = 1 <SPAN style="color:#00007F">Then</SPAN>
        s = Selection
        <SPAN style="color:#00007F">GoTo</SPAN> OnlyOneItem
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    x = UniqueList(Selection)
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> y <SPAN style="color:#00007F">In</SPAN> x
        s = s & y & vbNewLine
    <SPAN style="color:#00007F">Next</SPAN> y
OnlyOneItem:
    MsgBox s
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> testUL2()
<SPAN style="color:#007F00">'Originally written by firefytr</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    x = UniqueList(Array("a", "a", "b", "b", "B", 1, 2, 3))
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> y <SPAN style="color:#00007F">In</SPAN> x
        s = s & y & vbNewLine
    <SPAN style="color:#00007F">Next</SPAN> y
    MsgBox s
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Function</SPAN> UniqueList(varInput <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
<SPAN style="color:#007F00">'Originally written by firefytr</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> Dic <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, dicItem <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, blnArray <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> aTemp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, arrUnique() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, vItem <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> iRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, iCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, iDim() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, iCnt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = vbTextCompare
    <SPAN style="color:#00007F">If</SPAN> TypeName(varInput) = "Range" <SPAN style="color:#00007F">Then</SPAN> aTemp = varInput.value
    <SPAN style="color:#00007F">If</SPAN> IsEmpty(aTemp) <SPAN style="color:#00007F">Then</SPAN> aTemp = varInput: blnArray = <SPAN style="color:#00007F">True</SPAN>
    iDim = Split(CountNumOfElement(aTemp), ",")
    iCnt = 1
    <SPAN style="color:#00007F">For</SPAN> iCol = 1 <SPAN style="color:#00007F">To</SPAN> iDim(UBound(iDim))
        <SPAN style="color:#00007F">If</SPAN> blnArray = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Dic.exists(aTemp(iCol)) <SPAN style="color:#00007F">Then</SPAN>
                <SPAN style="color:#00007F">If</SPAN> IsEmpty(aTemp(iCol)) = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN>
                    Dic.Add aTemp(iCol), iCnt
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
                iCnt = iCnt + 1
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">Else</SPAN>
            <SPAN style="color:#00007F">For</SPAN> iRow = 1 <SPAN style="color:#00007F">To</SPAN> iDim(LBound(i<SPAN style="color:#00007F">Dim</SPAN>)) / iDim(UBound(i<SPAN style="color:#00007F">Dim</SPAN>))
                <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Dic.exists(aTemp(iRow, iCol)) <SPAN style="color:#00007F">Then</SPAN>
                    <SPAN style="color:#00007F">If</SPAN> IsEmpty(aTemp(iRow, iCol)) = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN>
                        Dic.Add aTemp(iRow, iCol), iCnt
                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
                    iCnt = iCnt + 1
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
            <SPAN style="color:#00007F">Next</SPAN> iRow
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> iCol
    iCnt = 1
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> vItem <SPAN style="color:#00007F">In</SPAN> Dic
        <SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> arrUnique(1 <SPAN style="color:#00007F">To</SPAN> iCnt)
        arrUnique(iCnt) = vItem
        iCnt = iCnt + 1
    <SPAN style="color:#00007F">Next</SPAN> vItem
    UniqueList = arrUnique
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Function</SPAN> CountNumOfElement(<SPAN style="color:#00007F">ByVal</SPAN> buf) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    Dim iDims <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, iInt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, iRows <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, iCols <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lb <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    Dim DummyArr() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
    iDims = 1: iRows = 1
    <SPAN style="color:#00007F">If</SPAN> TypeName(buf) <SPAN style="color:#00007F">Like</SPAN> "Variant()" <SPAN style="color:#00007F">Then</SPAN>
        DummyArr = buf
        i<SPAN style="color:#00007F">Dim</SPAN>s = GetNumberOfDim(DummyArr)
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">For</SPAN> iInt = 1 <SPAN style="color:#00007F">To</SPAN> iDims
        lb = <SPAN style="color:#00007F">LBound</SPAN>(buf, iInt)
        iCols = <SPAN style="color:#00007F">UBound</SPAN>(buf, iInt)
        iRows = iRows * (iCols - lb + 1)
    <SPAN style="color:#00007F">Next</SPAN>
    CountNumOfElement = <SPAN style="color:#00007F">CStr</SPAN>(iRows & "," & iCols)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Function</SPAN> GetNumberOfDim(<SPAN style="color:#00007F">ByRef</SPAN> vMultiDimArr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#007F00">'http://puremis.net/excel/code/076.shtml</SPAN>
    Dim i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, k <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">If</SPAN> VarType(vMultiDimArr) & vbArray = 0 <SPAN style="color:#00007F">Then</SPAN>
        GetNumberOfDim = -1
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    RtlMoveMemory i, <SPAN style="color:#00007F">ByVal</SPAN> VarPtr(vMultiDimArr) + 8, 4
    RtlMoveMemory j, <SPAN style="color:#00007F">ByVal</SPAN> i, 4
    RtlMoveMemory k, <SPAN style="color:#00007F">ByVal</SPAN> j, 2    <SPAN style="color:#007F00">'Get Number of Dimensions</SPAN>
    GetNumberOfDim = k
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>
</FONT>




HTH
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top