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!

 

[queuesmef]

Look up Advanced Data Filtering.....there you can extract Unique values (and place them in a different location, thus keeping original data)

 

[horizonflame]

@queuesmef, thank you. I tried filtering but encountered an error that I couldn't paste the unique values in a different sheet.

From research I am thinking a VBA solution will be best, could something adapted from the below help my result?

https://www.mrexcel.com/forum/excel...act-unique-values-across-multiple-sheets.html

Thanks

 

[DanteAmor]

You can explain, let's say with 4 records on each sheet what you have and what you expect from the result.
That is, it shows the 2 sheets with 4 records on each sheet before the process. Show the 2 sheets again after the process.

 

[horizonflame]

@DanteAmor, thank you for the suggestion

Part 1 - Find unique values in Sheet1 Column C (Range C3:C7000) vs Sheet2 Column C (Range C3:C7000) and delete those rows in Sheet1

[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]Sht1 ColC[/TD]
[TD]Sht2 ColC[/TD]
[TD]New Sht1 ColC[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]Red[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]Green[/TD]
[TD]Yellow[/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD]Yellow[/TD]
[TD]White[/TD]
[/TR]
[TR]
[TD]Purple[/TD]
[TD]Brown[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]White[/TD]
[TD]White[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]










Part 2 - Then find unique values in Sheet2 Column C (Range C3:C7000) vs Sheet1 Column C (Range C3:C7000) and copy columns A-C of those matching rows into next empty rown in Sheet1 Column A.

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Sht2 ColC[/TD]
[TD]Sht1 ColC[/TD]
[TD]New Sht2 Colc[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]Red[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD]Yellow[/TD]
[TD]Yellow[/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD]White[/TD]
[TD]White[/TD]
[/TR]
[TR]
[TD]Brown[/TD]
[TD][/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]White[/TD]
[TD][/TD]
[TD]Brown[/TD]
[/TR]
</tbody>[/TABLE]

Many thanks

 

[DanteAmor]

Well, I expected a more real example and I fell short with 4 records.
I still don't understand what you mean by "Find unique values", it means that in column C you have duplicate values.
In your explanation of part 2 you say "copy columns A-C of those matching rows into next empty rown in Sheet1", but in the example the modified column is "sheet2"

You could explain it again but with more real records.

 

[horizonflame]

@DanteAmor, apologies let me explain in more real life terms:
I have two lists of projects - a NEW LIST (Sheet2) and a CURRENT LIST (Sheet1). In both lists:

• Column A is Project Area
• Column B is Project Name
• Column C is Project Number (I am doing the unique values check on this column)

I need:

• In the NEW LIST some projects have finished and will not be in that list. However they remain in the CURRENT LIST and I want to delete them. (This is why I was trying to compare the two columns for unique values and delete the unique values only found in CURRENT LIST)
• In the NEW LIST there will be new projects that are not yet in the CURRENT LIST - I wanted to add these projects to the CURRENT LIST. I need to take Column A-C for each row being copied over to the bottom of CURRENT LIST. (This is why I was trying to compare the two columns for unique values and copy over the unique values found in NEW LIST to CURRENT LIST.)

Hope that makes more real sense of my spreadsheet.

Thanks

 

[Fluff]

Can a project number exist multiple times in either sheet?

 

[horizonflame]

Hi @Fluff, they are always unique

 

[Fluff]

Ok, how about

Sub horizoneflame()
    Dim Cl As Range, Rng As Range
    Dim Dic As Object
    
    Set Dic = CreateObject("scripting.dictionary")
    With Sheets("sheet2")
        For Each Cl In .Range("C2", .Range("C" & Rows.Count).End(xlUp))
            Dic(Cl.Value) = Cl.Offset(, -2).Resize(, 3).Value
        Next Cl
    End With
    With Sheets("sheet1")
        For Each Cl In .Range("C2", .Range("C" & 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("C" & Rows.Count).End(xlUp).Offset(1, -2).Resize(Dic.Count, 3).Value = Application.Index(Dic.Items, 0)
        End If
    End With
End Sub