# Unique data sets from two worksheets

##### Board Regular
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.

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

#### Dylan

##### Board Regular
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","")

##### Board Regular
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,"")

Replies
8
Views
98
Replies
3
Views
533
Replies
1
Views
107
Replies
5
Views
203
Replies
6
Views
82

1,190,582
Messages
5,981,783
Members
439,735
Latest member
Tony_P

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back