Removing Duplicate values from 2 ranges and creating a new range with the output

[Mark McInerney]

Hi,

I have data in range B1:C100. The data in b1:b100 has no duplicates. The data in c1:c100 has no duplicates. However there is some data in b1:b100 that is also contained in c1:c100

I'm looking for a formula to place in d1, and copy down, that will give me the unique values from each column b and c.

Is this possible?

Thanks - Mark.

 

[PCL]

Without formula, you could copy b1:b100 in D1 and c1:c100 in D101 and use
Data > Remove Duplicates

else use a macro

 

[Mark McInerney]

Hi PCL - Thanks for taking a look.

When I use the duplicate function and record my actions on a macro it does not work correctly - there are still some duplicates. Is there a formula that might work instead??

 

[PCL]

A formula will leave some cells empty, I think
Try next code

Sub Treat2()
   Range("D1").EntireColumn.ClearContents
   Range("B1:B100").Copy Destination:=Range("D1")
   Range("C1:C100").Copy Destination:=Range("D" & Rows.Count).End(3).Offset(1, 0)
   Range("D1:D" & Range("D" & Rows.Count).Row).RemoveDuplicates Columns:=1, Header:=xlNo
End Sub

 

[PCL]

Oups, here better

Sub Treat2()
   Range("D1").EntireColumn.ClearContents
   Range("B1:B" & Range("B" & Rows.Count).End(3).Row).Copy Destination:=Range("D1")
   Range("C1:C" & Range("C" & Rows.Count).End(3).Row).Copy Destination:=Range("D" & Rows.Count).End(3).Offset(1, 0)
   Range("D1:D" & Range("D" & Rows.Count).End(3).Row).RemoveDuplicates Columns:=1, Header:=xlNo
End Sub

 

[Mark McInerney]

Thanks PCL - appreciated.