+ Reply to Thread
Results 1 to 9 of 9

Match, Copy and paste from another spreadsheet

  1. #1
    Registered User
    Join Date
    04-29-2011
    Location
    Seligman, MO
    MS-Off Ver
    Excel 2007
    Posts
    5

    Match, Copy and paste from another spreadsheet

    Please help me figure out if this is do-able.

    I am looking for a way to streamline my inventory update process, I currently spending
    about 2 hrs each morning updateing my inventory from a daily feed....not to mention the
    cost of printing the sheets for comparison.

    I want to update my current inventory sheet with the values from another spreadsheet...I want it to find the matching ID# on the feed, and copy the qty and cost figures to the product list

    Inventory Feed (Info I need):
    Column A:ID# (same, doesn't change)
    Column D:Qty
    Column K:Cost

    Product List (website update):
    Column A:ID# (same, doesn't change)
    Column E:Qty
    Column Z:Cost

    My hope is that I can make a new workbook, and have sheet 1 be the code sheet (never changes), and then just copy the inventory feed and product list into the same workbook....once it makes the changes, I just copy the product list to a seperate sheet and upload it to my site......this might be a stretch.
    I figured setting functions on the product list page wouldn't be a good idea because it will be changing (add / subtract items) frequently, and I pull a fresh copy of the product list everyday from my site.


    I currently mess with about 200 Select items of the 8000 items I could have, but I would be stuck at my desk all day if I had to do all those items.

    Any help or suggestion would be appreciated....just getting started with excel, and have learned alot from this site so far, I made a great profit sheet to track my sales, and I keep finding something to do in excel.

    Thanks, Brian
    Attached Files Attached Files
    Last edited by DaPro; 04-29-2011 at 04:27 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Match, Copy and paste from another spreadsheet

    Hi Brian, welcome to the forum.

    find the matching ID# on the feed, and copy the qty and cost figures to the product list

    This would typically be done with VLOOKUP using the ID# to match against the downloaded data.

    You could record a macro whilst carrying out the manual actions to get the basis for the code required. This would need to be tweaked in order to generalise it.

    Post a sample workbook with detailed steps and examples of the before and after scenarios .

    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    04-29-2011
    Location
    Seligman, MO
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Match, Copy and paste from another spreadsheet

    I added a sample file to my original post, and here in just a bit I will have a chance to learn up on VLOOKUP.....then play with it

    Thanks for the directions

  4. #4
    Registered User
    Join Date
    04-29-2011
    Location
    Seligman, MO
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Match, Copy and paste from another spreadsheet

    I consolidated the workbook to a single page to get the basic idea down before applying it to several sheets...and I am starting with cell
    J2....=VLOOKUP(I2,A3:B16,B:B)......but it returns #VALUE

    This is all greek to me, but I am trying

    Also the info listed is only a small portion of the overall data feed, otherwise it would be a simple copy and paste....I only want to mess with about 1000 of the 8000 items on the feed....relevant or not

    Thanks
    Attached Images Attached Images
    Attached Files Attached Files

  5. #5
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Match, Copy and paste from another spreadsheet

    Give this a try:

    Please Login or Register  to view this content.
    Cheers,
    Last edited by ConneXionLost; 04-29-2011 at 11:55 AM.
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  6. #6
    Registered User
    Join Date
    04-29-2011
    Location
    Seligman, MO
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Match, Copy and paste from another spreadsheet

    Quote Originally Posted by ConneXionLost View Post
    Give this a try:

    Please Login or Register  to view this content.
    Cheers,
    That nailed it, I am fixing to apply it across the sheets....but what is it about the $ that corrects it? Cell format?

    and what is the 2,0.....guessing 2,0 is a number format for Column B?
    Last edited by DaPro; 04-29-2011 at 12:06 PM.

  7. #7
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Match, Copy and paste from another spreadsheet

    The #VALUE error was caused by B:B instead of 2. The $ are only used to make it easier to copy the formula to other cells.

    Cheers,
    Last edited by ConneXionLost; 04-29-2011 at 12:10 PM.

  8. #8
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Match, Copy and paste from another spreadsheet

    A "2" tells VLOOKUP to return the corresponding value from the "second" column of the indicated array. The "0" tells VLOOKUP that only an exact match is acceptable.

    Cheers,
    Last edited by ConneXionLost; 04-29-2011 at 12:11 PM.

  9. #9
    Registered User
    Join Date
    04-29-2011
    Location
    Seligman, MO
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Match, Copy and paste from another spreadsheet

    Well all in all is going to work for me, I did a complete update in a matter of 10 minutes versus a couple hours....Its not streamlined, nor did I get it to work via different sheets, but I can simply copy and paste the list onto the same sheet as the feed to get the job done, and then copy and paste it back...so ultimately its good.

    I'm gonna take a break and get back at it later, I have some more reading to do.

    Thanks for the help, its truly appreciated.

    Brian

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1