Unique data sets from two worksheets

[maldonadocj]

Hello,

I am trying to create a formula that will create a comprehensive listing of unique data points from two different sheets.


For example:

Sheet 1: "Apple" in Cell A1, "Pear" in Cell A2, "Banana" in Cell A3

Sheet 2: "Apple" in Cell B1, "Orange" in Cell B2, "Pineapple" in Cell B3


On Sheet 3:
The formula in Column A should do the following:

If "Pear" exists in Column A of Sheet1 and Column B of Sheet2, then note "Pear" as the output.

If "Pear" exists in Sheet 1 but does not exist in Sheet 2, then note "Pear" also in the output.

If "Pear" exists in Sheet 2 but does not exist in Sheet 1, then note "Pear"
also in the output.

I hope I was able to explain this clearly. Your help is greatly appreciated.

Thank you.

 

[Dylan]

Let say you range in sheet1 is A1 to A19 and sheet2 is B1 to B19,
then you may enter below formula to sheet3.

=IF(OR(COUNTIF(Sheet1!A1:A19,"Pear"),COUNTIF(Sheet2!B1:B19,"Pear")),"Pear","")

 

[maldonadocj]

Hello Dylan,

Thank you for your response. I made several revisions to the formula you sent me. I bought in the data from column A in Sheet1 and column B in Sheet2 into columns A and B in a different spreadsheet. Additionally, I substituted "Pear" for cell $B2.

Unfortunately, the output I received was all the data in Column A and none of the data from Column B. The formula should compare the data points in Column A and Column B and the unique data points from both columns should be noted in Column C. Can you pls. tell me what I did wrong?

In the new spreadsheet cell C2 Formula:
=IF(OR(COUNTIF($A2:$A65536,$B2),COUNTIF($B2:$B65536,$B2)),$B2,"")