cell compare between two files

[patsuganda]

Hi,

I am faced with an extreme ugly task of making a pricelist update every month. I extract my inventory items from an application exported in to excel. I clean up the data form in excel and it is usually in appopriate format. Two things makes me re-export, one we may have a new item in a group that may not be in my last excel. Secondly, we may have an item has changed some description. I need some assistance to compare two excel files with Column A having all the inventory items. My existing excel spreadsheet may have prices in Column F, and i would some how would like to do the following

1) If 'orginalfile' column A, cell 1 matches to 'newfile' column A (anywhere) copy the column F from 'original' file which is my price row to the 'newfile' with the exact match in column F and place it in the same row.

2) If the 'newfile' has an row in column A that doesn't exist in 'original file' highlight the item with yellow color so that i can manually update the price after costing is looked up.

Below is an example of what my file looks after it is extracted, and at the end it has a price. Although due to text mode, the indentation of groups seems not to look presentable here. i hope i am clear enough, if not please drop me the questions so i may try to answer them correctly.

CHANGE OVERS AND ISOLATORS 1890 PCS
016A CHANGE OVER 120 PCS
HAVELLS CH/OV OFF/LF/P 0016 A CFFE0016 120 PCS 181,250
032A CHANGE OVER 72 PCS
HAVELLS CH/OV OFF/LF/P 0032 A CFFE0032 72 PCS 102,250
063A CHANGE OVER 32 PCS
HAVELLS CH/OV OFF/LF/P 0063 A CFFE0063 25 PCS 200,750
HAVELLS CH/OV ON/LF/P 040 A CFFE0040 4 PCS 168,750
HAVELLS CH/OV ON/LF/P 063 A CFFE0063 1 PCS 181,250
KATKO CH/OVER 40A 2 PCS 140,000
100-1000 CHANGE OVER 40 PCS
HAVELLS CH/OV OFF/LF/P 0100 A CFFE0100 6 PCS 369,000
HAVELLS CH/OV OFF/LF/P 0200 A CFFE0200 3 PCS 951,000
HAVELLS CH/OV OFF/LF/P 0400 A CFFE0400 8 PCS 1,520,500

[mrice]

It would be easier if you could provide some files as an attachment.

[patsuganda]

Thanks for your quick response, attached is the two files. Sample original file is my file with pricelist after i have cleaned up the data. Sample new file is the newly export file from Db in excel format. As you view the groups and indentation of the subgroups. The idea is to copy A4 cell match in the new file and copy the pricing in the new file from the old file. Thanks for your assistance.

Thanks

[patsuganda]

Someone please help?!

[ZENITH]

To do what you want you always need to know the filename, but here is what I have

=LOOKUP(A2,'[sampleoriginal.xls]original file'!$A:$A,'[sampleoriginal.xls]original file'!$C:$C)

A better way of doing this is to create a template file that compares 2 data sets on one sheet, and pulls the new set with prices to a seperate sheet, then just copy that out to its own workbook and save it out every time you work it.

[patsuganda]

Please elaborate more on your suggestion, it seems valuable. Would i use the originalfile as the template and copy the new extraction into the same sheet as the one with price. I am sort of getting lost. May be an excel file would help. I agree with you comparing two files is an issue.