Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have an issue with referencing an excel spreadsheet:

I have data in columns A and C that's (mostly) equal, I want to isolate and create a separate list for the rows that are not equal.

For example, I have :

             A                B                C
    1-    5 John Lane     Joe Bloggs      5 John Lane
    2-    6 John Lane     Mary Lamb       35 Woopwoop Court

For the rows where A and C are equal, for this particular exercise, I don't care about. The rows where A and C are not equal, (row 2 above) I would like to compile a list of these, somewhere else.

I began with =IF(a:a<>c:c,c:c=f:f,f:f=0) This should have returned as column f (otherwise blank) returning either a value of 0 or of the contents of column c for that particular row, it didn't and I'm wondering what I'm doing wrong.

I would like to end up with a truncated list of all rows (columns A, B and C) in a separate worksheet for where A and C are not equal so I can list those properties where the owners live somewhere else.

Any thoughts or suggestions on how to achive this would be appreciated

share|improve this question
basic idea =IF(A1=C1, 1,0) use this down Colum D and then use filter to identify it. note: in this statment if A* is equal to C* it will display 1 if not equal it will display 0 – 50-3 Sep 18 '13 at 4:13
Hi, thanks for the response, how would I then take that list of 1s and 0s as a reference and copy all the data from each column somewhere else? – Nick Murray Sep 18 '13 at 4:49
My suggestion was to use row 1 as a filter… or you could do what @TDrabas has said – 50-3 Sep 18 '13 at 5:10
up vote 1 down vote accepted

Try this.

In your cell F2 (I'm assuming your row 1 is a header) type =IF(A2=C2,0,C2). Then drag this down by using this tiny square located in bottom-right corner of the cell F2. You should see 0s and values from the column C populating column F.

All you have to do right now is to copy the contents of column F, paste special (Ctrl+Alt+V) as values, and sort.

Hope this answers your question.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.