join two columns with duplicates over the limit

[excelNewbie22]

hi!
is it possible to join 2 columns, one contain 500,000 rows,
second 600,000 rows,
and between there's duplicates,
so in total the uniques will be 750,000,
now i want to spare several columns and do all in one,
tried

=UNIQUE(TOCOL(A1:B999999,1,1))

but i get a NUM error
i'm assuming because excel don't have enough room in the column (above 1,048,576 rows)
any way to bypass it?


sample only

test.xlsb
	A	B	C
1	1-2-3-4-5-6	1-2-3-4-6-9	#SPILL!
2	1-2-3-4-5-7	1-2-3-4-6-10
3	1-2-3-4-5-8	1-2-3-4-6-11	num error
4	1-2-3-4-5-9	1-2-3-4-6-12	above
5	1-2-3-4-5-10	1-2-3-4-6-13	not spill
6	1-2-3-4-5-11	1-2-3-4-6-14
7	1-2-3-4-5-12	1-2-3-4-6-15
8	1-2-3-4-5-13	1-2-3-4-6-16
9	1-2-3-4-5-14	1-2-3-4-6-17
10	1-2-3-4-5-15	1-2-3-4-6-18
11	1-2-3-4-5-16	1-2-3-4-6-19
12	1-2-3-4-5-17	1-2-3-4-6-20
13	1-2-3-4-5-18	1-2-3-4-5-11
14	1-2-3-4-5-19	1-2-3-4-5-12
15	all the way to	1-2-3-4-5-13
16	500,000	1-2-3-4-5-14
17		1-2-3-4-5-15
18		1-2-3-4-5-16
19		all the way to
20		600,000
test

Cell Formulas
Range	Formula
C1	C1	=UNIQUE(TOCOL(A1:B999999,1,1))

 

[shinigamilight]

Unless dictionary has an upper limit this should work fine.

Sub southern()
        Dim dic As Object
        Set dic = CreateObject("Scripting.Dictionary")
        Dim lr, lc As Long
        Dim k As Integer
        lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlWhole, xlByRows, xlPrevious, False).Row
        lc = Cells.Find("*", Cells(1, 1), xlFormulas, xlWhole, xlByRows, xlPrevious, False).Column
       
        For i = 1 To lc
                For k = 1 To lr
                            If dic.Exists(Cells(k, i).Value) = False And Cells(k, i) <> "" Then
                                dic(Cells(k, i).Value) = 1
                            End If
                Next k
        Next i
       
        Range("C1").Resize(dic.Count) = WorksheetFunction.Transpose(dic.Keys)
       
       
End Sub

 

[excelNewbie22]

thanks shinigamilight,
but i don't get how to reference to columns a and b ?
(i have lot's of other data on the file which i don't need to touch)

also, anyway to make it as an udf?

 

[mumps]

Try:

Sub RemoveDups()
    Application.ScreenUpdating = False
    Dim v As Variant, v2 As Variant, i As Long, dic As Object
    Dim vKey As Variant, vKeys As Variant
    v = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
    v2 = Range("B1", Range("B" & Rows.Count).End(xlUp)).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v) To UBound(v)
        If Not dic.exists(v(i, 1)) Then
            dic.Add v(i, 1), Nothing
        End If
    Next i
    For i = LBound(v2) To UBound(v2)
        If Not dic.exists(v2(i, 1)) Then
            dic.Add v2(i, 1), Nothing
        End If
    Next i
    Range("C1").Resize(dic.Count).Value = Application.Transpose(dic.keys)
    Application.ScreenUpdating = True
End Sub

 

[hydraulicwave]

1. Use the Advanced Filter feature to extract the unique values from the combined range of cells and paste them into a new location. This method allows you to filter out duplicates and copy the unique values to a new location, without the need to use a formula.
2. Use a combination of the INDEX and MATCH functions to create a new list of unique values in a separate column. You can use the MATCH function to check if a value exists in the list, and if it doesn't, use the INDEX function to add it to the list.
3. Use a macro to loop through the combined range of cells and add the unique values to a new list. This method requires some knowledge of VBA programming, but it can be a powerful way to get around the limitations of Excel formulas.

It's also worth noting that you can use the Power Query feature in Excel to quickly combine and deduplicate large sets of data. Power Query is a powerful tool that allows you to import, transform, and load data from a wide variety of sources, and it can be used to efficiently deduplicate large datasets.

 

[shinigamilight]

Sub southern_2()
        Dim dic As Object
        Set dic = CreateObject("Scripting.Dictionary")
        Dim lr, lc As Long
        Dim k As Integer
        lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlWhole, xlByRows, xlPrevious, False).Row
        
        For i = 1 To 2
                For k = 1 To lr
                            If dic.Exists(Cells(k, i).Value) = False And Cells(k, i) <> "" Then
                                dic(Cells(k, i).Value) = 1
                            End If
                Next k
        Next i
       
        Range("C1").Resize(dic.Count) = WorksheetFunction.Transpose(dic.Keys)
       
       
End Sub

This has been limited to first 2 columns and data will be spilled in the column C.

 

[excelNewbie22]

shinigamilight, appreciate the help, but didn't worked
mumps, it worked, thank you
hydraulicwave - thanks too, but 1-i'm was aiming only at formula 2-don't know how but it sound like more then one column and that i already learned 3-shinigamilight and mumps helped

if anyone have solution in the shape of a formula/udf that will be best,
till then, mumps solution works

 

[mumps]

You are very welcome.