2 Sheets - Unique Values add and delete

[horizonflame]

Hi All,

- My source data is Sheet2 Columns A-C
- My destination is Sheet1 Columns A-C

I need to perform two functions:

- Find unique values in Sheet2 Column B against Sheet1 Column B. Copy unique rows Columns A-C into next blank row in Sheet1 Column A.
- Find unique values in Sheet1 Column B against Sheet2 Column B. Then delete those rows.

Many thanks!

 

[DanteAmor]

I think it's 2 laps:
1. Remove from sheet1 everything that is not on sheet2.
2. And then add to sheet1 what is on sheet2 but is not on sheet1.


It looks like a pun.


In the end what you want is to leave the 2 sheets the same.
It would be enough to erase everything from sheet1 and pass everything from sheet2 to sheet1.

Let's review:

Sheet1 Before
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:78.89px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">Area</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Name</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Number</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >A2</td><td >B2</td><td >C2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >A3</td><td >B3</td><td >C3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >A4</td><td >B4</td><td >C4</td></tr></table>

Sheet2 Before
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">Area</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Name</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Number</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >A2</td><td >B2</td><td >C2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >A4</td><td >B4</td><td >C4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >A5</td><td >B5</td><td >C5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >A6</td><td >B6</td><td >C6</td></tr></table>

Sheet1 After (After running the Fluff macro)
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:78.89px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">Area</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Name</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Number</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >A2</td><td >B2</td><td >C2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >A4</td><td >B4</td><td >C4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >A5</td><td >B5</td><td >C5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >A6</td><td >B6</td><td >C6</td></tr></table>





Sheet2 After
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">Area</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Name</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Number</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >A2</td><td >B2</td><td >C2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >A4</td><td >B4</td><td >C4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >A5</td><td >B5</td><td >C5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >A6</td><td >B6</td><td >C6</td></tr></table>


------------------------------------------
As I see it, just copy sheet2 on sheet1.


Try this:

Sub CopyData()
  Sheets("Sheet2").Range("A:C").Copy Sheets("Sheet1").Range("A1")
End Sub

In fact, I don't think you need a macro for that, it was just clarifying what you had. An apology if I'm wrong.

 

[Fluff]

As I see it, just copy sheet2 on sheet1.

If you're right (and I think you are) my code is definitely overkill

 

[DanteAmor]

If you're right (and I think you are) my code is definitely overkill

That is why my insistence that the OP push the final result. Because the explanations pointed to that end. And I think your macro gave me clarity.

 

[horizonflame]

@DanteAmor There is project data in Columns D-G so I could not just paste the new list because the data wouldn't line up with the right rows. Thank you for your help during the discussion.
@Fluff perfect solution, thank you!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[DanteAmor]

@DanteAmor There is project data in Columns D-G so I could not just paste the new list because the data wouldn't line up with the right rows.

Try this

Sub Sheets_Unique_Values()
  Dim lr As Long, i As Long, r1 As Range, r2 As Range
  Dim sh1 As Worksheet, sh2 As Worksheet, f As Range
  Application.ScreenUpdating = False
  Set sh1 = Sheets("Sheet1")
  Set sh2 = Sheets("Sheet2")
  lr = sh1.Range("A" & Rows.Count).End(xlUp).Row
  Set r1 = sh1.Range("A" & lr + 1)
  Set r2 = sh2.Range("A" & sh2.Range("C" & Rows.Count).End(xlUp).Row + 1)
  For i = 2 To sh1.Range("C" & Rows.Count).End(xlUp).Row
    Set f = sh2.Range("C:C").Find(sh1.Cells(i, "C"), , xlValues, xlWhole)
    If f Is Nothing Then Set r1 = Union(r1, sh1.Range("A" & i))
  Next
  r1.EntireRow.Delete
  For i = 2 To sh2.Range("C" & Rows.Count).End(xlUp).Row
    Set f = sh1.Range("C:C").Find(sh2.Cells(i, "C"), , xlValues, xlWhole)
    If f Is Nothing Then Set r2 = Union(r2, sh2.Range("A" & i))
  Next
  r2.EntireRow.Copy sh1.Range("A" & sh1.Range("C" & Rows.Count).End(xlUp).Row + 1)
  Application.ScreenUpdating = True
  MsgBox "End"
End Sub

 

[horizonflame]

Thanks @DanteAmor.

@Fluff, could you show me what I need to do for some column changes please

- The project number for comparison is now in Column D on Sheet 2 and in Column B on Sheet 1. (rather than column C on both sheets)
- I need to copy sheet 2 Column A & D to sheet 1 Column A & B (rather than A-C to A-C)

Many thanks

 

[Fluff]

How about

Sub horizonflame()
    Dim Cl As Range, Rng As Range
    Dim Dic As Object
    
    Set Dic = CreateObject("scripting.dictionary")
    With Sheets("sheet2")
        For Each Cl In .Range("D2", .Range("D" & Rows.Count).End(xlUp))
            Dic(Cl.Value) = Array(Cl.Offset(, -3).Value, Cl.Value)
        Next Cl
    End With
    With Sheets("sheet1")
        For Each Cl In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
            If Dic.exists(Cl.Value) Then
                Dic.Remove Cl.Value
            Else
                If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
            End If
        Next Cl
        If Not Rng Is Nothing Then Rng.EntireRow.Delete
        If Dic.Count > 0 Then
            .Range("B" & Rows.Count).End(xlUp).Offset(1, -1).Resize(Dic.Count, 2).Value = Application.Index(Dic.items, 0)
        End If
    End With
End Sub

 

[DanteAmor]

Thanks @DanteAmor.

Youre welcome!