Extract values unique to 2 separate columns and paste in separate rows on different worksheet

[Tomcoll67]

Team,
I have a spreadsheet with around 500 values in each column. I am looking to discovery how to determine all unique values in the entire range. (If a values is in column B then it can be no where else in column B or C).
I have researched and found examples of determining unique values in a row, a single column, or a combination of a row in multiple columns. Unfortunately I have not been able to adapt these for my needs.
I am unsure of how to proceed.
Thank you for any assistance.

 

[JLGWhiz]

Here is one way. Assuming headers in row 1 and only columns A and B contain data.

Sub t()
With ActiveSheet
    .Range("A1", .Cells(Rows.Count, 1).End(xlUp)).AdvancedFilter xlFilterCopy, , .Range("D1"), True
    .Range("B2", .Cells(Rows.Count, 2).End(xlUp)).AdvancedFilter xlFilterCopy, , .Cells(Rows.Count, 4).End(xlUp)(2), True
    .Range("D1", .Cells(Rows.Count, 4).End(xlUp)).AdvancedFilter xlFilterCopy, , .Range("C1"), True
    .Range("D1", .Cells(Rows.Count, 4).End(xlUp)).ClearContents
End With
End Sub

 

[Tomcoll67]

I seem to be getting a 1004 error- "This cannot be applied to the selected range" when using the code above.
Thank you for your assistance.

 

[JLGWhiz]

I seem to be getting a 1004 error- "This cannot be applied to the selected range" when using the code above.
Thank you for your assistance.

The code worked as intended in the test set up where only columns A and B contain data. The code is run from standard code module1, not the sheet or ThisWorkbook modules. If your data is located in columns other than A and B then the code would need to be adjusted accordingly. There was no clear explanation of the sheet layout in the OP. We cannot see your worksheet, so it is imperative that your provide details of data latyout or post mock-up examples or links to a share sever with a sample file.

 

[Tomcoll67]

The code worked as intended in the test set up where only columns A and B contain data. The code is run from standard code module1, not the sheet or ThisWorkbook modules. If your data is located in columns other than A and B then the code would need to be adjusted accordingly. There was no clear explanation of the sheet layout in the OP. We cannot see your worksheet, so it is imperative that your provide details of data latyout or post mock-up examples or links to a share sever with a sample file.

The 1004 error was my mistake.(placed the data into Columns B:C)
I thank you for the explanation. I see the code moves all of the items from rows 1 and 2 into row 3.

I also apologize for not being clearer in what I am trying to accomplish.

Object: Obtain the unique values in the entire range (columns B:C). I have discovered how to do this for a single column or single row, but not in a multi-column range.

Outcome:
unique values from the range in column B should be placed into column D
unique values from the range in column C should be placed into column E
move the values in E:F into a different worksheet for display purposes.(target column does not matter)

Thank you for all assistance.

 

[JLGWhiz]

this will do columns B and C.

Sub t2()
With ActiveSheet
    .Range("B1", .Cells(Rows.Count, 2).End(xlUp)).AdvancedFilter xlFilterCopy, , .Range("D1"), True
    .Range("C1", .Cells(Rows.Count, 3).End(xlUp)).AdvancedFilter xlFilterCopy, , .Range("E1"), True    
End With
End Sub

move the values in E:F into a different worksheet for display purposes.(target column does not matter)

I don't know what this means. Where did these values come from? Column C unique values will be in Column E. This statement is confusing. Take your time, think it through.