Find unique values then copy to different column

[Tomcoll67]

Thank you for your help.
I apologize for not having valid code, but I cannot seem to get my head around this problem's solution.
I am trying to determine a way to check each number in column B verses all of column C.

I believe I must loop within a loop to scan the 500 rows of Column C for each cell in Column B.
For each value in column B, if a unique value is found I need to copy it to a new column.

The alternate version of this, I believe, is if a duplicate value is found, to delete both duplicate values. (preferred)

Dim Rows as Range
Second_Row as Range


For Each Row In Range("B2:B502")
    For Each Second_Row in Range ("C2:C502")
       If Row.Value=Second_Row.Value then 
         Row.Cell.ClearContents
       Else
          Continue


       Second_Row = Second_Row + 1
    
  Next Second_Row

Row=Row+1
Next Row

 

[yky]

I believe I must loop within a loop to scan the 500 rows of Column C for each cell in Column B.
For each value in column B, if a unique value is found I need to copy it to a new column.

For the second loop, you can do a range.find instead.

 

[Peter_SSs]

I am trying to determine a way to check each number in column B verses all of column C.

For each value in column B, if a unique value is found I need to copy it to a new column.

See if this does what you want without any looping. I have assumed that row 1 contains headings and columns G & H are empty.

Sub New_List()
  Range("G2").Formula = "=ISERROR(MATCH(B2,C$2:C$502,0))"
  Range("B1:B502").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("G1:G2"), CopyToRange:=Range("H1"), Unique:=True
  Range("G2").ClearContents
End Sub

If this does not produce the results you want, please explain more clearly and/or give a small set of sample data and the expected results.

 

[Tomcoll67]

See if this does what you want without any looping. I have assumed that row 1 contains headings and columns G & H are empty.

Sub New_List()
  Range("G2").Formula = "=ISERROR(MATCH(B2,C$2:C$502,0))"
  Range("B1:B502").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("G1:G2"), CopyToRange:=Range("H1"), Unique:=True
  Range("G2").ClearContents
End Sub

If this does not produce the results you want, please explain more clearly and/or give a small set of sample data and the expected results.

Thank you, This works for column B.
I do apologize for forgetting to put in the requirement of doing the same for Column C, which should obtain the unique values and place in the row after the column B copied items.
I tried to use the code but change values for Column C but it errors, does nothing, or copies the entire column to the column after the items copied from Column B.
Could you please help?
Thank you,

 

[Peter_SSs]

.. the requirement of doing the same for Column C, which should obtain the unique values ..

Do you mean that column C values should be checked against column B values and put in the new list if they do not appear in column B?

If so, try

Sub New_List_v2()
  Range("G2").Formula = "=ISERROR(MATCH(B2,C$2:C$502,0))"
  Range("B1:B502").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("G1:G2"), CopyToRange:=Range("H1"), Unique:=True
  Range("G2").Formula = "=ISERROR(MATCH(C2,B$2:B$502,0))"
  Range("C1:C502").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("G1:G2"), CopyToRange:=Range("I1"), Unique:=True
  Range("G2").ClearContents
End Sub

If not, please explain more clearly and/or give a small set of sample data and the expected results.

 

[Tomcoll67]

I apologize for not being clear. I need to find the unique values in Both B and C. Comparing B to C and C to B, but never B to B or C to C.
Does this help explain the issue?
I do thank you for taking the time to help me.

 

[Peter_SSs]

I need to find the unique values in Both B and C. Comparing B to C and C to B, but never B to B or C to C.

So, did you try my last code?
If it does not do what you want, please give a small set of sample data and the expected results.

 

[Tomcoll67]

Works perfectly.
Sorry for any confusion I caused.

Thank you!!! for your help. I never could have thought of this solution.

 

[Peter_SSs]

Works perfectly.
Sorry for any confusion I caused.

Thank you!!! for your help. I never could have thought of this solution.

Good news. Thanks for the update.