• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need to locate purchase orders that have had their OTTR date changed

S

sinkster

New Member
Hi All,
This is a fascinating site and appreciate all the shared knowledge. I am looking for help with an issue. The example lists two lines each from two separate spreadsheets. What I am trying to do is have a formula that will let me know if a date has changed in the Requested Date (OTTR) column (J) for a corresponding PO Number in column (C). This is a very small example where I know it has changed, however each spreadsheet contains around 10K lines, and I have to download weekly (see Run Date column A) and run this comparison, very time consuming. Any help is GREATLY appreciated!

sinkster
 

Attachments

Here's one technique for achieving your goal. I created two tables, essentially equivalent in structure, excpet one has a "Date Chnged?" column. Compared the same PO and OTTR in each, using SUMPRODUCT() - If they are not equivalent, we get a 1 asa result, otherwise it returns a 0 when both records match on the values tested.

There are many other methods for achieving the same goal - I think Conditional Formatting would provide you with a visual flag, if you'd prefer that ...
 

Attachments

David Evans said:
Here's one technique for achieving your goal. I created two tables, essentially equivalent in structure, excpet one has a "Date Chnged?" column. Compared the same PO and OTTR in each, using SUMPRODUCT() - If they are not equivalent, we get a 1 asa result, otherwise it returns a 0 when both records match on the values tested.

There are many other methods for achieving the same goal - I think Conditional Formatting would provide you with a visual flag, if you'd prefer that ...
Click to expand...

@David Evans
Thank you for your quick help. Your example helps, however I am not astute enough to know how the tables were created. You mention conditional formatting, which I poked around a bit, which visually may be better and could possibly require less steps?
Thanks again for your help.
sinkster
 
The tables are created by Insert - Table. They just make it easier to write formulas as they expand and contract as you add/delete data. They are a good way to go whenever you have data to analyze. The formula in the Date Changed column extends for whatever the table size ...
I've done a Conditional Format example for you - I must admit to finding CF mind-bending at times - it's as if I don't always get its logic, but there are lots of examples out there to "emulate" ;)
 

Attachments

Hi ,

What is meant by spreadsheets ? Do you mean workbooks or worksheets within one workbook ? Is it possible to have your data in 2 worksheets within one workbook ?

Narayan
 
David,
Thanks again for a new example using conditional formatting. Would it be too much to ask to explain how I could set it up so I can try and see if my results are the same?

Narayan, I download this info from a portal into an excel spreadsheet weekly, usually on Mondays. I can easily create a book with separate spreadsheets, as I have done in the new attachment. I have named the sheets within it to correspond the week downloaded. This would actually be easier for me then copying and pasting the data into one sheet for comparison.

Thanks again for your patience. :)
 

Attachments

sinkster said:
David,
Thanks again for a new example using conditional formatting. Would it be too much to ask to explain how I could set it up so I can try and see if my results are the same?


Thanks again for your patience. :)
Click to expand...

On your most recent list, can the OTTR be changed on a PO line item or does it apply to the whole PO?
I could probably work it our for myself with a little time, but I'm rushing out to the airport this morning ...
 
David Evans said:
On your most recent list, can the OTTR be changed on a PO line item or does it apply to the whole PO?
I could probably work it our for myself with a little time, but I'm rushing out to the airport this morning ...
Click to expand...

Hi David,
Yes, in fact the line item does change occasionally, as there may be several line items per PO. I totally understand your time constraints and again, appreciate the help!
Safe travels.

sinkster
 
Hi ,

Can you clarify a few points ?

1. Each of the tabs 0319 and 0327 contains data of multiple dates.

I assume that when you say you want to know if the information in column J has changed , it is with reference to an earlier piece of information.

Thus , if for a PO , on 19-03-2016 , column J had a date X , and if for the same PO , on 26-03-2016 , column J had a date Y , it means that this change is to be highlighted.

If , on the contrary , for all dates if a particular PO has the same information , or if a PO has exactly one entry in the database , it means there has been no change of information for that PO.

2. After seeing your data , I think it would be far better if all of the data is in one worksheet tab , so that identifying if there is exactly one entry for a PO , or in the case of multiple entries , identifying whether there is a change of information for that PO , will be easier.

Having the information in multiple tabs is as bad as having it in multiple workbooks. If the information can all be together in one worksheet tab , that would be nice.

3. I assume that the identification of a change needs to be done only once i.e. suppose there are 3 entries for a PO ; if we need to verify that there is no change in the information , we need to look at all 3 entries , and see that all of them are identical. However , to verify that there has been a change , if we detect a change once , we need not check all the entries.

For example , if we have 3 entries , the possibilities are as follows :

1. A = B = C i.e. all 3 entries have the same information , which means there has been no change

2. A = B , C is different ; this means there has been a change , but this will be detected only if we look at all 3 entries

3. A <> B , C = B ; B has seen a change , which means we need not look at C

4. A <> B , C <> B ; again , since B has seen a change , we need not look at C

Thus , in cases 2 , 3 and 4 , do we highlight all 3 entries ?

Narayan
 
Hi Narayan,
Thanks for you help! Let me try and answer each point.
1. Yes, I would like to be able to see any changes in column A for a particular PO, from one week to the next. Highlighting that change (from X to Y) would be great. If there are no changes, then you are correct, no info would need to added, unless we wanted to have the formula state "no change" for that PO.
2. Regarding all the data on one sheet, yes, I can do that. It would just require me to copy and paste the new data to the previous weeks sheet. I am certainly open to whatever is the easiest way for us to capture good data.
3. For each PO, there may be a separate line item (column D) so you are correct, I would need to see any changes to each PO item (column D) for each corresponding PO (column C).
Please let me know if I clarified for you. Here is the new sheet with both week's data.
 

Attachments

Sinkster - you're nearly at a point that you can solve this yourself. With all of the data in a single table identify which elements combine to make records duplicates - Concatenate them and look for duplicates.
In your case, I think you'll need ones that are NOT duplicates as that will suggest your OTTR date has changed.

You can also use Pivot Tables to identify duplicates,

That is why Narayan suggested you keep them all in one table, I suspect. After all he is :awesome::DD:DD
 
Hi ,

Instead of using concatenation , I have used the following formula :

=SUMPRODUCT(($C$2:$C$656=C2)*($J$2:$J$656<>J2))

which translates to :

For the same PO is there a date which is different from this date ?

Possibly this mathematical operation may be faster than concatenation , which is a string operation.

See the attached file.

Narayan
 

Attachments

Hello,
Thanks for your example and formula. Actually, I spent most of yesterday trying to come up with a vlookup formula that would enable me to look at all the date on two sheets within a book. With some help from my wife ( didn't know she knew excel this much), I think this is almost finished. I ran into an issue where the PO repeats several times due to different line items, but that is now resolved. My last issue is there are also different PO Sched lines (column G) for each PO/Line item (column D). I just need to determine what to add to my formula (column M) to include column G info to ensure the formula captures ALL data. Also, a result of "#N/A means the PO in sheet 0327 is new and was not found on sheet 0319.
Columns M and N reflect two formulas.
"M" =VLOOKUP(D2,'0319'!D$2:$L$346,9,FALSE)
"N" =IF(M2=L2,"NO","YES")

sinkster
 

Attachments

You must log in or register to reply here.
Back
Top