By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,538 Members | 875 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,538 IT Pros & Developers. It's quick & easy.

Merging two excel files, joined by ID

P: 3
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
Jul 3 '07 #1
Share this Question
Share on Google+
1 Reply


kadghar
Expert 100+
P: 1,295
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...

Expand|Select|Wrap|Line Numbers
  1. sub DontUseThis()
  2. dim OldID() as double
  3. dim NewID() as double
  4. dim i as long
  5. dim j as long
  6.  
  7. OldItems = 100 'You can do this with an eof
  8. NewItems = 100 'And this 2, but i dont know you DB
  9.  
  10. redim oldid(1 to olditems)
  11. redim newid(1 to newitems)
  12.  
  13. 'lets say you have in column A and B the ID and old price
  14. 'and you have E and F with ID and new prices
  15.  
  16. for i = 1 to olditems
  17. oldid(i)= cells(i,1).value
  18. next
  19. for i = 1 to newitems
  20. newid(i)= cells(i,5).value
  21. next
  22.  
  23. 'lets write the new prices in column C
  24.  
  25. for i = 1 to olditems
  26. j=1
  27. do
  28.  if oldid(i) = newid(j)  then
  29.    cells(i,3).value = cells(j,6).value
  30.    exit do
  31.  else
  32.    j=j+1
  33.  end if
  34. loop
  35. next
  36. next
  37. 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
Jul 3 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.