comparing cell values in separate books and sheets

[rorschach]

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

 

[bryonwoods30]

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]

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]

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.