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.
Last edited by ahuang3433; 01-31-2017 at 03:58 PM.
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.
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.
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" ?
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.
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.
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.
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.
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).
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?
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.
Last edited by ahuang3433; 02-01-2017 at 11:08 AM.
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.
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.
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?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks