comparing cell values in separate books and sheets

rorschach

New Member
Joined
Oct 21, 2010
Messages
20
I have 2 seperate worksheets and i need to compare 2 ranges of single columns. and then out put the data onto a new work book. I will need to make it so that if any of the 2 Cells match then it is not visible. But if there is a cell that is not matching a cell in the other book/sheet then it is visible in the new sheet

for example---off the top of my head

Workbook.Book1.Worksheet.Sheet 1.Range A1:A5000.Value = Workbook.Book2.Worksheet.Sheet 1.Range A1:A5000.Value = False
Then
Output.Workbook.Book3.Worksheet.Sheet 1.Range A1:A5000.Value
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

bryonwoods30

Board Regular
Joined
Feb 24, 2011
Messages
120
i would copy your column a from sheet 2 to sheet 3. then in column b of sheet 3, do a countif on column a
=countif(sheet1:column a,sheet3 column a value)
then you can just filter by the ones that don't return a 0 count and get rid of them,leaving you with the ones that only appear in your 2nd sheet.
 

rorschach

New Member
Joined
Oct 21, 2010
Messages
20
Thank you for your help.

I copied the column with all possiblities into column A and the filter possibilities into Column C. In column E i would like it to show any numbers which are not in both columns.

I attempted your Fx and it doesnt work. Please advise.

Thank you
 

bryonwoods30

Board Regular
Joined
Feb 24, 2011
Messages
120
ok so you have 3 work books. the first 2 you need to find the numbers that are not in both columns.
the example i am using below is a single workbook with 3 sheets, the 3rd sheet will give you your results.
1. Open a 3rd workbook.
2. copy all the values from the 2nd book to the 3rd in column a
3. in cell b1 use the formula =COUNTIF(Sheet1!A:A,Sheet3!A1)
4. take that formula all the way down to your last value in column a
5. select both columns a and b and copy, then paste select values over the formulas.
6. do filter auto filter and select values that are 0, this means it didn't find your value in column a in sheet 1.
 

Forum statistics

Threads
1,144,162
Messages
5,722,847
Members
422,460
Latest member
VBA_Noob01

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top