Question for Excel experts please

[philhancox]

I'm at my real job and I have an Excel question for any experts here please

I have a spreadsheet of several thousand records organised by product ID but with lots of other columns too including a price column. We've now got a new spreadsheet with new records and changes in price.
Is there an easy way of replacing old records with new records despite the records on the new sheet not being in order and with new records added on? I don't fancy going through changing each record manually
Hope that makes some sense...

[Paul Wright]

Not 100% sure if this is what you are after but have you tried the lookup function?

Excel: Lookup Function

Cheers
Paul

[morleymouse]

i do this a fair bit and use vlookup - Excel: VLookup Function

might be the same as what paul mentioned!

[tbp]

I don't really use Excel that much, so if I did it, I would export the data from the excel file into a CSV and import into Access. Then do the same with the new file. You can then create an Update Query to amend the data, and then export back to excel.

Sounds a lot of work, but isn't really, although its not as nice a solution as doing it all in excel

EDIT: Actually thinking about it, you probably wouldn't need to do the imports and exports, I think you can access an Excel spreadsheet directly in Access as a data source, so would be even easier.

[mike_01]

I think you may give ASAP Utilities a try... ASAP Utilities - The essential add-in for Excel software users. FREE excel tools and macros - Excel software not really sure if you have a solution for it there but still very useful.

[Donk]

Be carefull when using the vlookup function when the table data is not consecutive numbers.

For instance if you do a vlookup for the price of item 2 in the following table:

1,£5
3,£6
4,£8

It will take the next available item and give you the answer £6.

[nim-b]

But if you want it to look up only the exact value, you just make sure the range value is FALSE, surely? Then it won't go to the next highest value, it will only lookup the exact number. (Apologies if that doesn't make sense to anyone!)

As long as the product IDs remain the same for both sets of data, I think VLOOKUP is the easiest way of doing it.

[Donk]

Originally Posted by philhancox with new records added on

and I guess over the course of time old products could be deleted.

If both sets of data had exactly the same products a simpler method would be sort both tables by product id and copy the new price column to the orinal data.

[Graham@Buyagift]

Originally Posted by nim-b But if you want it to look up only the exact value, you just make sure the range value is FALSE, surely? Then it won't go to the next highest value, it will only lookup the exact number. (Apologies if that doesn't make sense to anyone!) As long as the product IDs remain the same for both sets of data, I think VLOOKUP is the easiest way of doing it.

Yep, this is correct. VLOOKUP will do you're after, and very easy to use.

http://office.microsoft.com/en-us/ex...093351033.aspx

Graham