• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 148
  • Last Modified:

Excel 2010 - Show Items Missing In One Column Compared to Another Column

I have to columns in Excel Column A and B which contain thousands of rows. I need to list all items that are missing from columns B using Column A as a reference.  I was hoping all missing items could be listed in column C.

I setup Conditional Formating which works my is no easy to go though. I have read online that you can setup a formatting rule as well but no exactly what I am looking for.

I am not the excel as advanced Excel items an great full for any help provided.
0
compdigit44
Asked:
compdigit44
1 Solution
 
EirmanChief Operations ManagerCommented:
Do you mean ....
If a cell in column B is blank, that you want the contents of cell A to be copied to cell C?
0
 
SimonCommented:
In Column C, row 2
=countif(B:B,A2)
then double click on bottom right hand corner to fill down.

Filter for column C=0

If required, copy the filtered list from col A into a new column
0
 
compdigit44Author Commented:
Stupid question but the above syntax will show all item missing in COlumn B when compared to Column A correct
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
SimonCommented:
Yes, but looking back I've not suggested the best place to put the formula unless you drag it down to the last row used in column A. It would be best to insert a col between A & B and use formula
=countif(C:C,A2)
which means count the number of times the value from this row in column A is found in column C (which was originally column B).

Then clicking the fill down square in bottom right corner of the cell will autofill it in column B for every cell in column A that has a value in (assuming no gaps in column A).

Filtering the formula column for zero values will show all entries in col A that don't appear in col B.
EE-SampleCountif.xlsx
0
 
QlemoDeveloperCommented:
If you use a formula like this in column C (drag down as you would do with Simon's), it will contain empty cells if there is a match in B, and the value of A otherwise. You'll only have to "collect" the non-empty cells then.
 =IF(ISERROR(LOOKUP( A1, B:B )), A1,"")
0
 
compdigit44Author Commented:
Thank you very much for all the help everyone.. I learned something in the process... :-)
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now