Extract unique values (mixed text/numbers) from multiple columns to new table column

trentt

New Member
Joined
Jun 28, 2018
Messages
7
I have multiple sheets that contain a table. I have another table on a different sheet where I would like to populate one of its columns with only the unique values from each of those other tables for a specific column.

Example:

Tables: Table1, Table2, Table3
Each table contains a list (list1, list2, and list3 respectively), which contains potentially 10,000-20,000 rows each.

I want Table4 to have a column (UniqueValues) to contain all of the unique values from the columns of the aforementioned tables.

Every guide I've look at insists upon using Index, Match, and CountIf. However, the values I am working with can be numerical or text, but must be treated as text. Using CountIf causes values such as "+0000" and "0000000" to be treated as equal rather than unique. Additionally, 0011243 is treated the same as 11243. It is imperative that every cell is treated exactly as it exists as text.

So, how would I do this? I'm fine with using COUNTIF if there is a workaround, but I've had to use SUMPRODUCT for other calculations I've done to get the count of each value when I populate the UniqueValues column manually using a program I wrote in Java. This workbook will be a template for distribution for this, so I would very much prefer to have the values generated from within the excel file itself.

Here's examples of what I have so far:

Code:
=IFERROR(IFERROR(IFERROR(INDEX(list1, MATCH(0, COUNTIF($K$12:K12, list1), 0)), INDEX(list2, MATCH(0, COUNTIF($K$12:K12, list2), 0))), INDEX(list3, MATCH(0, COUNTIF($K$12:K12, list3), 0))), "")
When I attempt to replace the CountIFs I am either doing it wrong or it tells me I'm using the wrong number of arguments.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,979
Welcome to the forum.

Try:

Code:
=IFERROR(INDEX(List1,SMALL(IF(List1<>"",IF(ISNA(MATCH(List1,$K$12:$K12,0)),ROW(List1)-ROW(INDEX(List1,1))+1)),1)),
IFERROR(INDEX(List2,SMALL(IF(List2<>"",IF(ISNA(MATCH(List2,$K$12:$K12,0)),ROW(List2)-ROW(INDEX(List2,1))+1)),1)),
IFERROR(INDEX(List3,SMALL(IF(List3<>"",IF(ISNA(MATCH(List3,$K$12:$K12,0)),ROW(List3)-ROW(INDEX(List3,1))+1)),1)),"")))&""
with Control+Shift+Enter.
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Zx</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">item</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">b</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">d</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">kad</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">x</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">c</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Zx</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">r</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">b</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">d</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">kad</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">c</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">c</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">kad</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">q</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">x</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">d</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">kad</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Zx</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">q</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">r</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p>

Add the following code to your workbook, using Alt + F11:

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003
' Ref: TinyURL.com - shorten that long URL into a tiny URL
Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
For i = LBound(Arg) To UBound(Arg)
Arg(i) = Arg(i)
If IsArray(Arg(i)) Then
For Each Itm In Arg(i)
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Itm
Next Itm
Else
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Arg(i)
End If
Next i
ArrayUnion = TempUnion
End Function

1. Define List using Insert | Name | Define (or Formulas | Name Manager) as referring to:
Rich (BB code):
=arrayunion(Sheet1!$A$2:$A$6,Sheet1!$B$2:$B$6,Sheet1!$C$2:$C$6)
2. Define Ivec as referring to:
Rich (BB code):
=ROW(INDIRECT("1:"&COLUMNS(List)))

3. In G1 control+shift+enter, not just enter:
Rich (BB code):
=SUM(IF(FREQUENCY(IF(1-(List=""),MATCH("~"&List,List&"",0)),Ivec),1))
4. In G3 control+shift+enter, not just enter, and copy down:
Rich (BB code):
=IF(ROWS($G$3:G3)<=$G$1,INDEX(List,SMALL(IF(FREQUENCY(IF(1-(List=""), MATCH(List,List,0)),Ivec),Ivec),ROWS($G$3:G3))),"")
 
Last edited:

trentt

New Member
Joined
Jun 28, 2018
Messages
7
Apparently my work laptop isn't powerful enough to handle your formula @Eric W; as I end up maxing out the CPU for excessive periods of time (It's a hyperthreaded dual-core). I am in the process of acquiring a more powerful machine so I can re-attempt.

In the meantime, would you be willing to explain the pieces of the formula and how they work, so I can not only use it, but also understand why it works?

Also, Aladin, I appreciate the VBA post as well. I did intend to experiment with both options, so thank you. I will post back when I've had an opportunity to test it.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,979
Apparently I overlooked where you said each range has 10,000 - 20,000 rows. That's a lot, and could bog down even a powerful PC.

As far as the formula,

IF(List1<>"" . . . checks to see if each row in List1 is empty or not, if not,

IF(ISNA(MATCH(List1,$K$12:$K12,0)) . . . checks to see if each row exists in the output range above the cell where the formula is, if not,

ROW(List1)-ROW(INDEX(List1,1))+1 . . . returns the offset within the range of the row, then

SMALL(... , 1) . . . finds the smallest value in the array of offsets, and INDEX returns it. If there are no offsets in the array, since they've all been found, then SMALL returns an error, and IFERROR kicks in, and we repeat the process for the next range.


You might also want to look at a different VBA solution. The code Aladin provided is a generic routine for combining ranges, and still requires formulas on the worksheet. It is possible to write a customized macro that should be faster with less setup. For example, try:

Open a copy of your workbook. Press Alt-F11 to open the VBA editor. From the menu, select Insert > Module. In the window that opens, paste this code:

Rich (BB code):
Sub CombineLists()
Dim MyLists As Variant, List As Variant, Output As Range, MyData As Variant
Dim x As Variant, Dict As Object, Ctr As Long, MyArray() As String, i As Long


    MyLists = Array("List1", "List2", "List3")
    Set Output = Sheets("Sheet1").Range("K13")
    
    Set Dict = CreateObject("Scripting.Dictionary")
    
    For Each List In MyLists
        MyData = Range(List).Value
        For i = 1 To UBound(MyData)
            If MyData(i, 1) <> "" Then Dict(CStr(MyData(i, 1))) = 1
        Next i
    Next List
    
    ReDim MyArray(1 To Dict.Count, 1 To 1)
    Ctr = 1
    For Each x In Dict
        MyArray(Ctr, 1) = x
        Ctr = Ctr + 1
    Next x
    Output.Resize(Dict.Count).Value = MyArray
    
End Sub
Change the list names in red to match your sheet. Change the output areas in blue to be where you want the output to start. Switch back to your Excel sheet. Press Alt-F8 to open the macro selector, select CombineLists and click Run. You can set this up on a button if you want if you use it a lot. You can also modify the macro to sort the output list if you want.

Let us know what you end up with.
 

trentt

New Member
Joined
Jun 28, 2018
Messages
7
Apparently I overlooked where you said each range has 10,000 - 20,000 rows. That's a lot, and could bog down even a powerful PC.

As far as the formula,

IF(List1<>"" . . . checks to see if each row in List1 is empty or not, if not,

IF(ISNA(MATCH(List1,$K$12:$K12,0)) . . . checks to see if each row exists in the output range above the cell where the formula is, if not,

ROW(List1)-ROW(INDEX(List1,1))+1 . . . returns the offset within the range of the row, then

SMALL(... , 1) . . . finds the smallest value in the array of offsets, and INDEX returns it. If there are no offsets in the array, since they've all been found, then SMALL returns an error, and IFERROR kicks in, and we repeat the process for the next range.


You might also want to look at a different VBA solution. The code Aladin provided is a generic routine for combining ranges, and still requires formulas on the worksheet. It is possible to write a customized macro that should be faster with less setup. For example, try:

Open a copy of your workbook. Press Alt-F11 to open the VBA editor. From the menu, select Insert > Module. In the window that opens, paste this code:

Rich (BB code):
Sub CombineLists()
Dim MyLists As Variant, List As Variant, Output As Range, MyData As Variant
Dim x As Variant, Dict As Object, Ctr As Long, MyArray() As String, i As Long


    MyLists = Array("List1", "List2", "List3")
    Set Output = Sheets("Sheet1").Range("K13")
    
    Set Dict = CreateObject("Scripting.Dictionary")
    
    For Each List In MyLists
        MyData = Range(List).Value
        For i = 1 To UBound(MyData)
            If MyData(i, 1) <> "" Then Dict(CStr(MyData(i, 1))) = 1
        Next i
    Next List
    
    ReDim MyArray(1 To Dict.Count, 1 To 1)
    Ctr = 1
    For Each x In Dict
        MyArray(Ctr, 1) = x
        Ctr = Ctr + 1
    Next x
    Output.Resize(Dict.Count).Value = MyArray
    
End Sub
Change the list names in red to match your sheet. Change the output areas in blue to be where you want the output to start. Switch back to your Excel sheet. Press Alt-F8 to open the macro selector, select CombineLists and click Run. You can set this up on a button if you want if you use it a lot. You can also modify the macro to sort the output list if you want.

Let us know what you end up with.
This worked incredibly fast, and using a program I wrote in Java, I verified the accuracy of it too. It is very much appreciated. Thank you.
 

Forum statistics

Threads
1,082,042
Messages
5,362,816
Members
400,694
Latest member
Sofie17

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top