Ho to compare 2 columns

Microsoft Excel 2003 (full product)
April 14, 2010 at 10:57:22
Specs: Windows Vista
I have 2 columns in excel but the second
column has some changes. I there a way I can
see where the changes are.
column A column B
345 345
456 456
789 943 here tere is a change
can this be column somehow be noted either
color or any other alert.

Thank you for the help.

See More: Ho to compare 2 columns

Report •

April 14, 2010 at 11:16:11
If the changes are lined up next to each other as in your example, you could use Conditional Formatting.

Select Column B and pull down Format...Conditional Formatting...Formula Is:

Enter: =A1<>B1 in the field.

Click Format...Pattern tab...and choose a color.

For each pair of cells where Column A is not equal to Column B, the Column B cell will change color.

Report •

April 14, 2010 at 11:30:23
Thank you so much. I really appreciate Ur Help it works!

Report •

April 14, 2010 at 14:08:24
Here is another way of doing it:

1. Insert a column between Column A & Column B

2. Type the following formula in cell B1:

=IF(ISERROR(MATCH(A1,$C$1:$C$150,0)),"","Match in Col C")

3. Select cells B1:B150
4. Then "Edit", select "Fill" on the Edit menu, and then click “Down”.

This will compare Column A against Column C and insert the comment
"Match in Col C" next to the cell in Column A matched in Column C. (Reread that last part so you understand what's going on)

The columns do not have to be sorted or lined up next to each other.
I Started at Row 1, change the formula if needed.
I used only 150 rows to check, again change if needed.
I inserted a column, because I find it makes it visually easier to determine the outcome.



Report •

Related Solutions

April 18, 2010 at 10:23:23
Thanks, This was very helpful. I have one more question. I have about 500 products on a sheet and I get a list of about 50 products a week from the manufacturer telling me these 50 products are not in stock anymore about 45 out of the 50 products are listed in the 500 products sheet. Is there a way for me to put these 50 products in the sheet with the 500 products and it should somehow highlight the (any alert would be fine) 45 products that are not in stock anymore.

Thank you so much for your time!

Report •

April 18, 2010 at 12:33:26

Put the list of out of stock products in a column away from your main list.
For this example I had product data in column A and the list of out of stock products in column I (cells I1 to I14)
In an empty column, say column D, enter this formula on the same row as the first product:

=IF(ISNA(VLOOKUP(A1,$I$1:$I$14,1,FALSE)),"","Not in stock")
In this example, my sample data started in row 1 - adjust as appropriate.
Drag the formula down as many rows as you have products in column A.
Note the $ signs in the formula - these are required to make the formula work when dragged, to extend it.

Now column D will be blank for in stock items, and will show "Not in stock" for items that are present in your list in column I

You can make the formula cover a larger range in column I so that additional out of stock items can be added without having to change the formula.
Blanks in column I will not affect the function, so

=IF(ISNA(VLOOKUP(A1,$I$1:$I$100,1,FALSE)),"","Not in stock")
will work just as well.


Report •

Ask Question