+ Reply to Thread
Results 1 to 18 of 18

Best way to Compare Data

  1. #1
    Registered User
    Join Date
    12-23-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    2007,2013
    Posts
    11

    Best way to Compare Data

    Hello, and Thank you in Advanced for your reply's.
    I am trying to compare two sheets of data to find differences between them,
    Below are screen shots of each the sheets of the serial numbers from the equipment we rent, the names have been removed for security.

    Below is a screenshot of the data we receive from our provider
    RAWImage.png

    And this is the data that we exported from our system.. much neater
    ManImage.png

    eXAMPLE.xlsx

    So my Goal is to be able to compare between the two, in laymans terms I would like the data from sheet2 to pull data from sheet1 and display it next to the serial.
    Like say Sheet2D4 Cell does a check on its contents and looks at sheet1 to see if it exist and reply with the copy of the serial,

    If you also know a way to make Sheet1s data be alot more presentable without having to manually move the cells that would be awesome.

    Any information would be appreciated, Merry Christmas and have a good day.
    Last edited by redex; 12-23-2014 at 09:00 PM. Reason: Adding Attachment

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Best way to Compare Data

    I tried,

    I don't understand what you are trying to achieve.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Best way to Compare Data

    Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
    Also, not all members can upload picture files (Company firewalls and stuff)

    Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    12-23-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    2007,2013
    Posts
    11

    Re: Best way to Compare Data

    Okay I will, Ill make a mock up one and get back to you asap, Thanks.

  5. #5
    Registered User
    Join Date
    12-23-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    2007,2013
    Posts
    11

    Re: Best way to Compare Data

    Hi Thanks, so to reiterate I am looking for a way to make our records search the data that we receive from our provider to double check that each piece of equipment (serial) we are paying for matches up with a serial in our records. Because in the past this provider has double charged and also charged us for equipment we have retired and sent back.

    Attached is a mock up of the data
    eXAMPLE.xlsx

  6. #6
    Registered User
    Join Date
    12-23-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    2007,2013
    Posts
    11

    Re: Best way to Compare Data

    I would never have expected them to retype my data, thanks bud

  7. #7
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Best way to Compare Data

    Paste your suppliers data into sheet2 and then click on the button on sheet1.

    the button runs this macro:=

    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    12-23-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    2007,2013
    Posts
    11

    Re: Best way to Compare Data

    WOW thank you,
    How long did that take you too make, evidently I know nothing about Macros lol.
    Can you explain how to resolve why if you press the button twice is errors out, it seems the code is set to create a new work sheet to sort the data which is great. But then you press it again and it errors because worksheet already exists.

  9. #9
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Best way to Compare Data

    You are correct.
    but that is easily solved.

    Inserting this code will delete sheet3 if it exists:

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by mehmetcik; 12-23-2014 at 11:06 PM.

  10. #10
    Registered User
    Join Date
    12-23-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    2007,2013
    Posts
    11

    Re: Best way to Compare Data

    Thanks for your effort mehmetcik but unfortunately this macro does not seem to be 100% accurate. There are Serial Numbers In Both Sheets, and being sorted but then returning with a FALSE which is wrong. The real Data I wish to manipulate is of 400 rows if thats important to you.

  11. #11
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Best way to Compare Data

    The macro will work with a large number of rows.

    There were 23 nonmatching seial numbers on your test data, I checked two differrent ways

    The macro found them all.

    Please post your full data set.
    Attached Files Attached Files
    Last edited by mehmetcik; 12-23-2014 at 11:20 PM.

  12. #12
    Registered User
    Join Date
    12-23-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    2007,2013
    Posts
    11

    Re: Best way to Compare Data

    Thanks Mehmetcik, I do appreciate this. I wish I could learn more haha but if your happy to help then I welcome it.

    Let me know if you need anything else because Im still getting used to this, i.e this is my first Macro lol

    Example.xlsm

  13. #13
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Best way to Compare Data

    This seems to work.

    Can you let me know which serial it doesn't match?
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    12-23-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    2007,2013
    Posts
    11

    Re: Best way to Compare Data

    Well there is 299 Provider records.
    Which means that there should be close to 299 TRUE values, i.e 299 records that match up with our records.
    But there are only 114, some examples are Serial 15865,18570,12104. 15865 is in the Provider record and in our record so should be true, but it in false

  15. #15
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Best way to Compare Data

    Ahhh

    I've found it.


    Sorry about that.
    Attached Files Attached Files
    Last edited by mehmetcik; 12-24-2014 at 12:00 AM.

  16. #16
    Registered User
    Join Date
    12-23-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    2007,2013
    Posts
    11

    Re: Best way to Compare Data

    duplicate post
    Last edited by redex; 12-23-2014 at 11:58 PM. Reason: duplicate post

  17. #17
    Registered User
    Join Date
    12-23-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    2007,2013
    Posts
    11

    Re: Best way to Compare Data

    Thanks ill have a look, Are you able to describe to me what you have been changing?

  18. #18
    Registered User
    Join Date
    12-23-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    2007,2013
    Posts
    11

    Re: Best way to Compare Data

    WOW Thanks! It Worked, Unfortunately for me this is just the beginning lol.
    I have a follow up question, which is which part of the macro does the first step which is sorts the data. Because we here at work have found what you just did to be very useful. We gets lots of BS Data from Companys who try to make it difficult for us to organise there data and for us to be able to quickly sort them ascending would be kick ****! Coould you possibly reply back with the part of the macro that does that?

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. BOM Compare - compare between tow versions of data
    By dispro14 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-29-2014, 02:45 PM
  2. pivot table compare current data with refresh data and format new values
    By verynewatthis in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-18-2014, 08:29 AM
  3. Compare cell data and Column data and retrieve corresponding data
    By bharath.duraiswamy in forum Excel General
    Replies: 1
    Last Post: 11-06-2013, 05:59 AM
  4. Replies: 1
    Last Post: 01-15-2013, 01:23 PM
  5. Replies: 3
    Last Post: 06-22-2012, 03:54 PM

Tags for this Thread

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