Merging two excel files, joined by ID

[adamrace]

Hi,

I've got two excel files, one has a list of products and their current prices and they all have a product ID, I have another file with a list of price's that need updating. I was wondering what the best way of going about merging the two files together, with the old price and the new price using the ID's. The second file has a list of all the products on their system, the first has just a few of these which are on the other system. If anyone has any ideas of how to either write a macro or use scenario>merge? The problem i was having with the merge is that you can only have 32 cells at a go.

Cheers for any help.

Adam

[kadghar]

Hi,

I've got two excel files, one has a list of products and their current prices and they all have a product ID, I have another file with a list of price's that need updating. I was wondering what the best way of going about merging the two files together, with the old price and the new price using the ID's. The second file has a list of all the products on their system, the first has just a few of these which are on the other system. If anyone has any ideas of how to either write a macro or use scenario>merge? The problem i was having with the merge is that you can only have 32 cells at a go.

Cheers for any help.

Adam

using a excel, the easiest way is with FindV, that shouldnt take more than 1 minute. (dont forget to put 0 in the las parameter, that fixes 90% of the data mistakes) (yeah, i've just come with that 90% so what?)

if you want to make it via code you can use with an sql, and just sort them by id

if you dont have any sql, use arrays and IF's this is a very bad and unefficient method but it works...

sub DontUseThis()
dim OldID() as double
dim NewID() as double
dim i as long
dim j as long
 
OldItems = 100 'You can do this with an eof
NewItems = 100 'And this 2, but i dont know you DB
 
redim oldid(1 to olditems)
redim newid(1 to newitems)
 
'lets say you have in column A and B the ID and old price
'and you have E and F with ID and new prices
 
for i = 1 to olditems
oldid(i)= cells(i,1).value
next
for i = 1 to newitems
newid(i)= cells(i,5).value
next
 
'lets write the new prices in column C
 
for i = 1 to olditems
j=1
do
 if oldid(i) = newid(j)  then
   cells(i,3).value = cells(j,6).value
   exit do
 else
   j=j+1
 end if
loop
next
next
end sub

well hope that helps. Sometimes is faster to put the prices into arrays, create a result array and then write it down to excel..

Good Luck