[Cybercat]

Anybody here know a lot about Excel 2007?

I have the following problem. I have two excel files. One of them includes a column with unique article numbers. The other one has a column with some of those same article numbers but not all of them and not in the same order, and in another column it has an article description for those articles. I need to merge thse so that the article descriptions are put in a new column in the first file, in the row corresponding to the article number. However, I have no idea how to do this. Any suggestions? I can't just copy&paste because the order is different, and not all articles are in the second file (so I can't just sort them and be done with it).

[Waycos]

Copy and paste one entire sheet from one into the other, then do a resort and eliminate duplicates/what you don't need, then just cut and paste the columns accordingly.

[Cybercat]

I guess I didn't explain it clearly enough.

I have one huge excel file which has all products our hospital ever orders. In one column, there is an unique product code, and another column is currently empty but should get a detailed product description. There's several other columns with stuff like prices and units.

Then I have several small files, one from each supplier. These files have a column with the same unique product code, and a second column has the detailed product description. Since each file is of a different supplier, each file only has a part of the products that are in the big main file.

What I need now is a way to go through each of the small files, take each row, read the product code, get the description of the product from the next column and put that description in the big file, in the empty column, next to the matching product code. I have to repeat it for all these small files to end up with that same huge file I started with but now the formerly empty column has the product descriptions gotten from each of the small files.

Is there a way to do this that doesn't involve me cutting and pasting each product description one at a time? If possible, a way to add all product details from one of those smaller files at once, so I only have to repeat it once for each supplier file?

[Waycos]

Ah yes, if you save them as CSV files, you could do some coding in Access or MySql (which I think is free) to pull the info from the files and organize them all in one nice display. As long as you kept the location of the files and their names the same you could update them all the time without having to change anything in the database.

[linuxelf]

Yeah, you'll have to do some coding to make that work. If it was me, I'd export to CSV, read them into perl, and spit back out another CSV.

[treehugger]

From what I can tell, you need a lookup function.

Create a new sheet in your file to dump data to then copy and paste the lists from all the suppliers onto this sheet and sort it by product number, ascending. In your column that you want fill in on your main sheet, use a vlookup function to read in the data from your new sheet. This will take the product number and look it up in your new supplier table and fill in the descriptions. Once you are all happy with it, just copy and paste special (values and number formats) to get rid of the formula and you can then delete the extra sheet of supplier data, if you like.

Your vlookup should be something like =VLOOKUP(A2,'New Sheet'$A$1:$B$20,2)
where
- A2 is the cell that has the product number in it that you want to look up;
- 'New Sheet'$A$1:$B$20 is the range of the supplier data that you copied in; and
- 2 is the column number that contains the description

[Cybercat]

Thanks, that looks more workable, as I don't have access or mysql at work.

[SaintArthur]

It would be fairly straightforward with access. Pity.
Sounds like a lot of work the other way.
If you do need any help with the formulae or would like me to write you a macro, feel free to drop me an email. Preferably with a couple of sample files.
( I'd only do this for free for LICD people..)
P

[Cybercat]

Thanks, but after some experimenting, I managed to get things done with vlookup.