Comparing contents in 2 columns

I have a list in column A and another list in column B. I need to compare what's in each column and if something's in B but not in A i need to write 'not needed' in column C beside it but if something's in column A but not in column B i need that copied and pasted into column D.

Any suggestions? Is this taking Excel formulas too far and should i be posting in the macro section?

Many thanks
Jo

Hello try this.

In Column C copy and paste the below code, from the beginning of your data in column C to the end

Please Login or Register  to view this content.

In Column D copy and paste the below code, from the beginning of your data in column D to the end

Please Login or Register  to view this content.

If you like a blank cell instead of the number of times cell B1 is found in column A, you should replace ,COUNTIF($A:$A,B1) with of rvasquez' first formula with "".

Thanks both, that's great, but i should have mentioned the lists in columns A and B are different lengths. So i end up with zeros at the bottom of my column D as there are fewer items in column A than in column B. Any way of getting these to show as blanks?
Thanks
Jo

I'm not sure if you want to check if cell A1 and B1 are equal or B1 equals any value in column A. I think the latter is the case, if so then:

Formula:

Please Login or Register  to view this content.

This formula is as replacement for the second formula of rvasquez

Thanks - that's spot on.

One further question though - i now get several blank cells in column D as it compares the 2 lists. Is there any way to get it to compare what's in the 2 lists and return what's in column B but not in column A without blank cells?

Thanks
Jo