Excel 2000 Comparing Columns

June 4, 2009 at 10:42:14
Specs: N/A
I would really appreciate if someone could help me with the following problem:

Here is what I have:
Two different files in Excel 2000;
Each file contains information about students’ accounts - each row in the file relates to different accounts with columns like account number, balance, name of the bank etc.
One of the files contains a complete list of all student accounts while the other file has only those accounts that were used recently. Also the balances for the same account number can be different in each file because of the timing differences. What I need to do is to find the accounts that are represented in both files and whose balances differ from file to file. And I need those balances to be highlighted in the file that contains the complete list of accounts. If the balance for a particular account represented in both files is the same, I don’t need it to be highlighted.

I am trying to use a conditional formatting and VLOOKUP function for this purpose, but I don’t know how to set up the final formula.

I would really appreciate any help.

Thank you in advance.


See More: Excel 2000 Comparing Columns

Report •

June 4, 2009 at 12:04:08
Two different files in Excel 2000
Do you mean different "sheets" or "workbooks"?

Either way, you can't use conditional formatting.
You will get the error message:

You may not use references to other worksheets or workbooks for Conditional Formatting criteria.

If you can fit them on one sheet......
There is probably a VBA solution, but my skills in this area are just above null.

Also, you will get a better response to Excel question in the Office Software forum.



Report •

June 4, 2009 at 13:22:40
I need those balances to be highlighted in the file that contains the complete list of accounts

I can't give you colors but will true or false work?
You could do something like:


Where A1 is your first account number, on your complete sheet, and column C on sheet number 2 is your recently used list.
Put it into an empty column and copy the formula down as many accounts numbers as you need.
True means a match.

You could also try something using =INDEX with =MATCH.
but again, no colors.



Report •

June 5, 2009 at 12:19:51
Thanks guys,
I did it through the VLOOKUP function and used Named Ranges for the data in different workbooks. It works fine.


Report •

Related Solutions

Ask Question