Unduplicate values in two columns; unique values in column C

J

jerseyboy

New Member
Joined
Jun 20, 2014
Messages
15
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!
 
Last edited:
Sort by date Sort by votes
Hi,

With that many rows, a formula-based solution (which would most likely have to involve array formulas) may struggle.

I have such a solution, which is actually perhaps a touch overkill for your set-up, since it is designed to perform this over any number of columns, not just two (though of course it works equally well for that number of columns) and also has a clause to ignore any blank cells within the ranges.

I can offer you this if you want, though I would still recommend that you go down a VBA route with that large a dataset, and I hope that someone with experience in that area will pick up on this thread shortly.

Regards
 
Upvote 0
XOR - sure, what is your solution? I'm willing to try anything.

Also I have no familiarity with VBA = but am wiling to learn if it will do the trick.

Thanks
 
Upvote 0
Hmmm.

Just tested it with a range of A1:B500000 (i.e. one million cells) and it crashed Excel! So apologies - obviously not a feasible solution over that large a range.

Regards
 
Upvote 0
jerseyboy,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?

Sample raw data:


Excel 2007
ABC
1123456
2456789
3325623
4
5
6
Sheet1


After the macro:


Excel 2007
ABC
1123456123
2456789325
3325623456
4623
5789
6
Sheet1


Code: 
Sub GetUniques()
' hiker95, 06/20/2014, ME785906
Dim lr As Long, rng As Range, c As Range, v() As Variant
lr = Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
Set rng = Range("A1:B" & lr)
With CreateObject("Scripting.Dictionary")
  .CompareMode = vbTextCompare
  For Each c In rng
    If c <> "" Then
      If Not .Exists(c.Value) Then
        .Add c.Value, c.Value
      End If
    End If
  Next c
  v = Application.Transpose(Array(.Keys))
End With
Range("C1").Resize(UBound(v)) = v
Range("C1:C" & UBound(v, 1)).Sort key1:=Range("C1"), order1:=1
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 GetUniques macro.
 
Upvote 0
I am usinf excel '13, so this should be perfect. Thanks!

hiker95 said:
jerseyboy,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?

Sample raw data:

Excel 2007
ABC

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]789[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]325[/TD]
[TD="align: right"]623[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​



After the macro:

Excel 2007
ABC

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]123[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]789[/TD]
[TD="align: right"]325[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]325[/TD]
[TD="align: right"]623[/TD]
[TD="align: right"]456[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]623[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]789[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​



Code: 
Sub GetUniques()
' hiker95, 06/20/2014, ME785906
Dim lr As Long, rng As Range, c As Range, v() As Variant
lr = Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
Set rng = Range("A1:B" & lr)
With CreateObject("Scripting.Dictionary")
  .CompareMode = vbTextCompare
  For Each c In rng
    If c <> "" Then
      If Not .Exists(c.Value) Then
        .Add c.Value, c.Value
      End If
    End If
  Next c
  v = Application.Transpose(Array(.Keys))
End With
Range("C1").Resize(UBound(v)) = v
Range("C1:C" & UBound(v, 1)).Sort key1:=Range("C1"), order1:=1
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 GetUniques macro.
Click to expand...
 
Upvote 0
jerseyboy,

Please answer the following two questions.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?
 
Upvote 0
I am using a windows 8 pc and MS office '13. 16 GB RAM, 64-bit OS

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

haven't tried to do anything with VB yet

hiker95 said:
jerseyboy,

Please answer the following two questions.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

ExcelNewbie2020
  • Solved
filter unique value and transpose the 3rd column base on unique values
Replies
4
Views
282
Fluff
Fluff
B
  • Question Question
warning for duplicate value in a specific column
Replies
2
Views
342
mumps
mumps
Y
  • Solved
Need formula to match
2
Replies
10
Views
827
yukonexcel
Y
L
  • Solved
Sum values(column C) based on unique value(column A) after filtering(column B)
2
Replies
11
Views
921
Fluff
Fluff
A
  • Question Question
Two dimensional dynamic named range over a pivot table not working as I thought
Replies
4
Views
1K
ashleighbrown
A

Share this page

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