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

[trentt]

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:

=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]

Welcome to the forum.

Try:

=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.

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G
1							8
2	x		x		Zx		item
3	b		d		kad		x
4	c		Zx		r		b
5	d		kad		c		c
6	kad		q		x		d
7							kad
8							Zx
9							q
10							r
11
Sheet1

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:

=arrayunion(Sheet1!$A$2:$A$6,Sheet1!$B$2:$B$6,Sheet1!$C$2:$C$6)

​

2. Define Ivec as referring to:

=ROW(INDIRECT("1:"&COLUMNS(List)))

3. In G1 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(List=""),MATCH("~"&List,List&"",0)),Ivec),1))

4. In G3 control+shift+enter, not just enter, and copy down:

=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))),"")

 

[trentt]

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]

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:

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]

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:

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.

 

[Eric W]

You're welcome. Thanks for the update.