+ Reply to Thread
Results 1 to 18 of 18

compare 2 sheets on one identical column and update another column

  1. #1
    Registered User
    Join Date
    10-06-2011
    Location
    ireland
    MS-Off Ver
    Excel 2003
    Posts
    11

    compare 2 sheets on one identical column and update another column

    Hi, nice to be here, hope someone can help. I have a problem with two worksheets, one is products exported from sage, the second is upload file to website of products and all related info. The only common columns between the 2 worksheets is the SKU column and the PRICE column. Because there are numerous extra rows in the sage export file that i dont need and all the product details are in the upload file i need to find a way to compare the SKU column in both worksheets and if the code in the cells exists in both sheets then update the PRICE column in sheet 2, the upload file, from the price column in sheet 1, the sage export.

    I really dont know how to go about this, i have tried some compare macros i found on google but i am still getting nowhere.

  2. #2
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: compare 2 sheets on one identical column and update another column

    What are the sheet names and which columns are the SKU and PRICE on each sheet?
    Is your code running too slowly?
    Does your workbook or database have a bunch of duplicate pieces of data?
    Have a look at this article to learn the best ways to set up your projects.
    It will save both time and effort in the long run!


    Dave

  3. #3
    Registered User
    Join Date
    10-06-2011
    Location
    ireland
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: compare 2 sheets on one identical column and update another column

    Hi, thanks for the response. The two worksheets are named sage and online.

    The column SKU is 1st column on sage and PRICE is 3rd column on sage.

    The column SKU is 5th column on online and PRICE is 7th column on online.

  4. #4
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: compare 2 sheets on one identical column and update another column

    You can use vlookup to get the answers for both your columns.

  5. #5
    Registered User
    Join Date
    10-06-2011
    Location
    ireland
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: compare 2 sheets on one identical column and update another column

    Hi arlu, thanks for that, i have looked at vlookup and tried it out but was unable to find a solution. Im the one who uses excel at work when necessary as i know more than the others but I don't know anything about it to be honest.

  6. #6
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: compare 2 sheets on one identical column and update another column

    Using vlookup would go like this:

    =VLOOKUP(E2,sage!$A$2:$C$10,3,0)

    Type that into online!G2 and copy down.

  7. #7
    Registered User
    Join Date
    10-06-2011
    Location
    ireland
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: compare 2 sheets on one identical column and update another column

    Hi, i tried this

    =VLOOKUP(E2,sage!$A$2:$C$10,3,0)

    in online!G2

    but all i get back is #N/A

  8. #8
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: compare 2 sheets on one identical column and update another column

    Is the value you are searching for in the first 10 rows? You should adjust the search range down to your last row, ie $C$100 or $C$5000. If you can't make it work, post a sample workbook.

  9. #9
    Registered User
    Join Date
    10-06-2011
    Location
    ireland
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: compare 2 sheets on one identical column and update another column

    Hi, i've figured out the range part and have started getting some results. Some of them still come up as #N?A though, even though the data is there in the referenced cells.

    I will keep trying to figure out why there are missing results, meanwhile i have attached the file.
    Attached Files Attached Files

  10. #10
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: compare 2 sheets on one identical column and update another column

    The errors are being caused by different formatting in the cells. If you change the format of both the cells being searched and the cell whose value you are using to search both to text, it will work.

  11. #11
    Registered User
    Join Date
    10-06-2011
    Location
    ireland
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: compare 2 sheets on one identical column and update another column

    Hi, thats what i have been working on. I have changed the SKU column to text and aligned a to z on both sheets. But the sheets do not order a to z the same on the two sheets even though they should?

    sage sheet orders the column like this

    800
    100
    1000PA
    1000PALANCE
    1000PASEALS
    1000Z
    101
    101ALK



    and online sheet orders the column like this


    48
    49
    50
    52
    92
    94
    96
    97
    100
    101
    102


    what am i missing?

  12. #12
    Registered User
    Join Date
    10-06-2011
    Location
    ireland
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: compare 2 sheets on one identical column and update another column

    oh, and some of the previously #N?A are now showing correctly but a couple of hundred are still not showing correctly.

  13. #13
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: compare 2 sheets on one identical column and update another column

    Can you post an example of some that are still not showing correctly?

  14. #14
    Registered User
    Join Date
    10-06-2011
    Location
    ireland
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: compare 2 sheets on one identical column and update another column

    Hi, yeah i can post an example, do you mean just copy and paste a few rows?

  15. #15
    Registered User
    Join Date
    10-06-2011
    Location
    ireland
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: compare 2 sheets on one identical column and update another column

    Most of the rows that have a problem seem to be of SKU's that are numerical only, would this cause a problem? For instance I changed 48 to 48AAA and the right price appeared instead of #N/A, i still cannot have the columns sorted identically either, which i cant figure out?

  16. #16
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: compare 2 sheets on one identical column and update another column

    It has to still be the result of cells being formatted differently. Check those individual cells that are sorting wrong or are causing #N/A for their cell format. If necessary, you can cause a cell to be read as text by giving it a leading ' (apostrophe).

  17. #17
    Registered User
    Join Date
    10-06-2011
    Location
    ireland
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: compare 2 sheets on one identical column and update another column

    Most of the rows that have a problem seem to be of SKU's that are numerical only, would this cause a problem? For instance I changed 48 to 48AAA and the right price appeared instead of #N/A, i still cannot have the columns sorted identically either, which i cant figure out?

    And now i find that if i double click in the cells in column E (SKU) this makes the price change from #N/A to the correct price. It also comes up in column E cells with error warning that i have numbers stored as text.

  18. #18
    Registered User
    Join Date
    10-06-2011
    Location
    ireland
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: compare 2 sheets on one identical column and update another column

    Hi davegugg thanks very much. I think i have it fixed now. I did as you said and looked up about cleaning the data and it seems to have worked. Except for about 20 or so that is, but after checking 4 or 5 i concluded that these sku's do not exist in the sage master sheet. Which is strange as everything came from it originally..I'll start again now anyway as i know what to do now. This is really powerful and very helpful for me .. thank you so much for the time and effort!

    I've learnt some good lessons from this, i'll check out the rest of the site to learn more.

+ 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