Unduplicate values in two columns; unique values in column C

[jerseyboy]

Hello

I am trying to unduplicate two columns, each with over a half million values so stacking them is impractical. I want to create a third column containing the unique values from the first two.

_A_ _B_ _C_
123 456 123
456 789 325
325 623 456
___ ___ 623
___ ___ 789

Thanks!

 

[hiker95]

jerseyboy,

doing a production test on the above macro with 50K plus in column A and 130K plus in column B yielded the error message --

run time error '13';
type mismatch

I would like to see the actual workbook for testing.

You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[jerseyboy]

will do...

fyi this seems to be a capacity issue, the macro maxes out at about 41,000 records.

jerseyboy,



I would like to see the actual workbook for testing.

You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[jerseyboy]

Here is the file for testing. 150K + rows. Actual data could run 250K to 500K rows.

https://app.box.com/s/5gui5mn3dehdonex7fe8

thanks!

 

[hiker95]

jerseyboy,

Thanks for the workbook.

Your workbook, Sheet1, contains 155,787 rows of raw data in columns A, and, B.

The output in column C will be 163,453 rows sorted ascending.

The macro run time on my Lenovo P500 laptop computer, with Windows 8.1, and, Excel 2007, was 8.031 seconds.

Sample raw data in worksheet Sheet1 (only 34 rows are shown for brevity):

Excel 2007
	A	B	C
1	column A	column B
2	0100000US54	0100000US54
3	0100000US541	0100000US541
4	0100000US5411	0100000US5411
5	0100000US54111	0100000US54111
6	0100000US541110	0100000US541110
7	0100000US5411101	0100000US54119
8	0100000US5411102	0100000US541191
9	0100000US54119	0100000US541199
10	0100000US541191	0100000US5412
11	0100000US541199	0100000US54121
12	0100000US5412	0100000US541211
13	0100000US54121	0100000US541213
14	0100000US541211	0100000US541214
15	0100000US541213	0100000US541219
16	0100000US541214	0100000US5413
17	0100000US5412141	0100000US54131
18	0100000US5412142	0100000US541310
19	0100000US541219	0100000US54132
20	0100000US5413	0100000US541320
21	0100000US54131	0100000US54133
22	0100000US541310	0100000US541330
23	0100000US54132	0100000US54134
24	0100000US541320	0100000US541340
25	0100000US54133	0100000US54135
26	0100000US541330	0100000US541350
27	0100000US54134	0100000US54136
28	0100000US541340	0100000US541360
29	0100000US54135	0100000US54137
30	0100000US541350	0100000US541370
31	0100000US54136	0100000US54138
32	0100000US541360	0100000US541380
33	0100000US54137	0100000US5414
34	0100000US541370	0100000US54141
Sheet1

After the macro:

Excel 2007
	A	B	C
1	column A	column B	0100000US54
2	0100000US54	0100000US54	0100000US541
3	0100000US541	0100000US541	0100000US5411
4	0100000US5411	0100000US5411	0100000US54111
5	0100000US54111	0100000US54111	0100000US541110
6	0100000US541110	0100000US541110	0100000US5411101
7	0100000US5411101	0100000US54119	0100000US5411102
8	0100000US5411102	0100000US541191	0100000US54119
9	0100000US54119	0100000US541199	0100000US541191
10	0100000US541191	0100000US5412	0100000US541199
11	0100000US541199	0100000US54121	0100000US5412
12	0100000US5412	0100000US541211	0100000US54121
13	0100000US54121	0100000US541213	0100000US541211
14	0100000US541211	0100000US541214	0100000US541213
15	0100000US541213	0100000US541219	0100000US541214
16	0100000US541214	0100000US5413	0100000US5412141
17	0100000US5412141	0100000US54131	0100000US5412142
18	0100000US5412142	0100000US541310	0100000US541219
19	0100000US541219	0100000US54132	0100000US5413
20	0100000US5413	0100000US541320	0100000US54131
21	0100000US54131	0100000US54133	0100000US541310
22	0100000US541310	0100000US541330	0100000US54132
23	0100000US54132	0100000US54134	0100000US541320
24	0100000US541320	0100000US541340	0100000US54133
25	0100000US54133	0100000US54135	0100000US541330
26	0100000US541330	0100000US541350	0100000US54134
27	0100000US54134	0100000US54136	0100000US541340
28	0100000US541340	0100000US541360	0100000US54135
29	0100000US54135	0100000US54137	0100000US541350
30	0100000US541350	0100000US541370	0100000US54136
31	0100000US54136	0100000US54138	0100000US541360
32	0100000US541360	0100000US541380	0100000US54137
33	0100000US54137	0100000US5414	0100000US541370
34	0100000US541370	0100000US54141	0100000US54138
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub GetUniquesSD()
' hiker95, 06/21/2014, ME785906
Dim lra As Long, lrb As Long
Dim arng As Range, brng As Range, c As Range
Dim k, i As Long
Application.ScreenUpdating = False
Columns(3).ClearContents
lra = Cells(Rows.Count, 1).End(xlUp).Row
Set arng = Range("A2:A" & lra)
lrb = Cells(Rows.Count, 2).End(xlUp).Row
Set brng = Range("B2:B" & lrb)
ReDim k(1 To lra + lrb, 1 To 1)
With CreateObject("Scripting.Dictionary")
  .CompareMode = vbTextCompare
  For Each c In arng
    If c <> "" Then
      If Not .Exists(c.Value) Then
        .Add c.Value, c.Value
        i = i + 1
        k(i, 1) = c.Value
      End If
    End If
  Next c
  For Each c In brng
    If c <> "" Then
      If Not .Exists(c.Value) Then
        .Add c.Value, c.Value
        i = i + 1
        k(i, 1) = c.Value
      End If
    End If
  Next c
End With
Range("C1:C" & UBound(k)) = k
Range("C1:C" & UBound(k)).Sort key1:=Range("C1"), order1:=1
Columns(3).AutoFit
Application.ScreenUpdating = True
End Sub

You may have to add the Microsoft Scripting Runtime to the References - VBA Project.

With your workbook that contains the above:

Press the keys ALT + F11 to open the Visual Basic Editor

In the VBA Editor, click on:
Tools
References...

Put a checkmark in the box marked
Microsoft Scripting Runtime

Then click on the OK button.

And, exit out of the VBA Editor.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetUniquesSD macro.

 

[billszysz]

The simplest method to do that is

https://drive.google.com/file/d/0B6UlMk8OzUrxNmlzSko2OWZhRGs/edit?usp=sharing

(non-english video but easy to understanding)

Regards

 

[jerseyboy]

Outstanding. This works just great. Very quick, and accurate.

Thanks for the help.

 

[hiker95]

jerseyboy,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.