+ Reply to Thread
Results 1 to 14 of 14

Comparing data from 2 sheets

  1. #1
    Registered User
    Join Date
    10-21-2016
    Location
    Broken Arrow, Oklahoma
    MS-Off Ver
    2010
    Posts
    79

    Comparing data from 2 sheets

    The attached file shows the data that I'm trying to compare. I am trying to use column A as the reference point and row 2. But I don't how this would work since every month that I would be running this file, one month will be missing. The whole point of this file is to compare and contrast the number difference referencing the items on column to their corresponding monthly buckets. Any help would be great.
    Attached Files Attached Files
    Last edited by ahuang3433; 01-31-2017 at 03:58 PM.

  2. #2
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Comparing data from 2 sheets

    Hi -

    I see the two spreadsheets, but I don't see an example of what you are trying to do. Can you provide that?

    Thanks
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.

  3. #3
    Registered User
    Join Date
    10-21-2016
    Location
    Broken Arrow, Oklahoma
    MS-Off Ver
    2010
    Posts
    79

    Re: Comparing data from 2 sheets

    Sorry for the confusion. What I'm trying to do is lookup the DCO PN in both sheets to their corresponding buckets and see the differences between the 2 sheets. I've attached a sample. You can see on the Sheet "New" there are some DCO PN that exist on that sheet but not on Sheet "Old". I would like the code to add that assembly to Difference Page. Sheet "Old" has Jan-17 but Sheet "New" doesn't so need to make sure the alignment is right. At the end I would like the differences highlighted in yellow.
    Attached Files Attached Files

  4. #4
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,605

    Re: Comparing data from 2 sheets

    Look at the attached. I moved a copy of the FEB column from "Trying to do" tab to the "Old Tab" and highlighted the differences.

    Is this what you are wanting the result to be ?

    Do you need the result differences on "New Sheet" ?
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Comparing data from 2 sheets

    Hi -

    Copy and paste this formula in cell B3 of the Trying to do sheet. Then copy down.

    =INDEX(Old!$B$3:$X$40,MATCH('Trying to do'!A3,Old!$A$3:$A$40,0),MATCH('Trying to do'!$B$2,Old!$B$2:$X$2))-INDEX(New!$B$3:$X$40,MATCH('Trying to do'!A3,New!$A$3:$A$40,0),MATCH('Trying to do'!$B$2,New!$B$2:$X$2))

    You will find there are a few DCO PN that aren't exactly the same between the two lists. Those will show up as #N/A.

    Hope this helps.

  6. #6
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Comparing data from 2 sheets

    Hi -

    Sorry, didn't quite read the second half of your request. When you say you want to add the "assembly" to the Difference page, do you mean you want to add another line with the different DCO PN? Not sure I understand that part.

  7. #7
    Registered User
    Join Date
    10-21-2016
    Location
    Broken Arrow, Oklahoma
    MS-Off Ver
    2010
    Posts
    79

    Re: Comparing data from 2 sheets

    I see that it is reflecting the quantity on the old sheet but not difference between the 2 sheets. What I want to see is if I had 4 on the old for a specific DCO PN and 2 on the newsheet for the same DCO PN, I would like it to return -2 because of the deduction.

  8. #8
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Comparing data from 2 sheets

    Hi -

    Please look at the attached spreadsheet. It exactly duplicates the numbers you have except where the two lists don't contain the same DCO PN. Those display as #N/A.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    10-21-2016
    Location
    Broken Arrow, Oklahoma
    MS-Off Ver
    2010
    Posts
    79

    Re: Comparing data from 2 sheets

    disregard the second part. I want to be able to see the (-) or (+) of the DCO PN. If something was decreased by 2 I want a return of (-2) and if something increased by 4 i want a return of (+4). Ofcouse if there is any return (0).

  10. #10
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Comparing data from 2 sheets

    Hm. Still not sure I understand.

    For example. If I look at Cell C14 in Old I get 60. If I look at Cell B14 in New I get 20. If I look at Cell C14 on my posted example sheet Trying to do, I get 60-20=40. Is 40 not the correct answer? I'm taking Old-New. Should it be New-Old? -40?

  11. #11
    Registered User
    Join Date
    10-21-2016
    Location
    Broken Arrow, Oklahoma
    MS-Off Ver
    2010
    Posts
    79

    Re: Comparing data from 2 sheets

    Perhaps new to old would work better for my situation. I've attached another file showing a more extended look to it. So is there also a formula that would show the rolling 24 months? For example, right now we are in Feb-17 and the old file still has Jan-17 data, I want a formula that would start on cell B2 showing the current month we are in and every cell to the right would be a rolling month. Also I want to conditional format the changes within the data region. For example, if a quantity was reduced by 4, I want it to be conditionally formatted to the cell being yellow and text to be in bold and red. Also I forgot to mention that I was hoping the conditional format that is reflected on the difference tab to also reflect on the New tab if possible.
    Attached Files Attached Files
    Last edited by ahuang3433; 02-01-2017 at 11:08 AM.

  12. #12
    Registered User
    Join Date
    10-21-2016
    Location
    Broken Arrow, Oklahoma
    MS-Off Ver
    2010
    Posts
    79

    Re: Comparing data from 2 sheets

    Hi Loginjmor,

    I plugged in your formula and integrated to my workbook but itís seems to not calculate right. Please take a look. Columnn F has the formula. Column G has the vlookup data from thenew forecast and column H has the vlookup data from the old forecast. Column I is a basic subtraction calculation.
    Attached Files Attached Files

  13. #13
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Comparing data from 2 sheets

    Hi -

    The problem with the formula is the MATCH functions did not always have a 0 in the last argument to specify an exact match rather than less than or greater than. I have corrected that in the attached workbook. The formula now matches the results in the other columns.

    Hope this helps.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    10-21-2016
    Location
    Broken Arrow, Oklahoma
    MS-Off Ver
    2010
    Posts
    79

    Re: Comparing data from 2 sheets

    Thank You so much! One more question. Is there a way to have the same conditional formatting on the difference tab to reflect on the new forecast tab?

+ 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. [SOLVED] Comparing Data Across 2 sheets
    By excelmee in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-04-2013, 05:55 PM
  2. [SOLVED] Comparing data in 2 sheets
    By JakeMann in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-14-2013, 04:13 AM
  3. comparing data between 2 sheets
    By pengwin in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-01-2010, 04:19 PM
  4. Need help in comparing data in 2 sheets
    By giantsquid in forum Excel General
    Replies: 2
    Last Post: 08-26-2008, 03:30 AM
  5. Comparing two sheets of data for changes.
    By paperclip in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-24-2006, 06:25 AM
  6. Comparing data of two sheets
    By shenlingstyle in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-29-2005, 01:57 PM
  7. Comparing Data between two sheets
    By AChesley in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-18-2005, 10:06 AM
  8. Comparing sheets data
    By Vytautas in forum Excel General
    Replies: 1
    Last Post: 02-15-2005, 06:06 AM

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