Complex tracking of changing dates

[Keti89]

Hello Everyone,

I have a challenging one, I will try to be precise, but let me know if you need to know anything else.

Basically, I have 2 sets of data (as below).

I want to have those dates highlighted in DATASET 2 where the date has changed.
This is not so easy, because a given item can appear on more than 1 orders and also, certain items will disappear from the orders (as they are available & ship).

I am thinking I would need to apply an array formula, as a vlookup will not do. I am sure some of you will have more knowledge on those than me.

DATASET 1

[TABLE="width: 226"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Order[/TD]
[TD]Product[/TD]
[TD]Availability[/TD]
[/TR]
[TR]
[TD]Order 1[/TD]
[TD]Item A[/TD]
[TD]07/03/2018[/TD]
[/TR]
[TR]
[TD]Order 1[/TD]
[TD]Item B[/TD]
[TD]07/03/2018[/TD]
[/TR]
[TR]
[TD]Order 1[/TD]
[TD]Item C[/TD]
[TD]23/02/2018[/TD]
[/TR]
[TR]
[TD]Order 2[/TD]
[TD]Item D[/TD]
[TD]07/03/2018[/TD]
[/TR]
[TR]
[TD]Order 2[/TD]
[TD]Item E[/TD]
[TD]14/02/2018[/TD]
[/TR]
[TR]
[TD]Order 2[/TD]
[TD]Item A[/TD]
[TD]13/03/2018[/TD]
[/TR]
[TR]
[TD]Order 2[/TD]
[TD]Item C[/TD]
[TD]01/03/2018[/TD]
[/TR]
[TR]
[TD]Order 3[/TD]
[TD]Item F[/TD]
[TD]07/03/2018[/TD]
[/TR]
[TR]
[TD]Order 3[/TD]
[TD]Item G[/TD]
[TD]13/02/2018[/TD]
[/TR]
[TR]
[TD]Order 3[/TD]
[TD]Item A[/TD]
[TD]19/02/2018[/TD]
[/TR]
[TR]
[TD]Order 3[/TD]
[TD]Item D[/TD]
[TD]16/02/2018[/TD]
[/TR]
[TR]
[TD]Order 3[/TD]
[TD]Item A[/TD]
[TD]20/02/2018[/TD]
[/TR]
[TR]
[TD]Order 3[/TD]
[TD]Item H[/TD]
[TD]19/02/2018[/TD]
[/TR]
</tbody>[/TABLE]

DATASET 2

[TABLE="width: 226"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Order[/TD]
[TD]Product[/TD]
[TD]Availability[/TD]
[/TR]
[TR]
[TD]Order 1[/TD]
[TD]Item A[/TD]
[TD="align: right"]10/03/2018[/TD]
[/TR]
[TR]
[TD]Order 2[/TD]
[TD]Item A[/TD]
[TD="align: right"]20/03/2018[/TD]
[/TR]
[TR]
[TD]Order 2[/TD]
[TD]Item C[/TD]
[TD="align: right"]25/03/2018[/TD]
[/TR]
[TR]
[TD]Order 3[/TD]
[TD]Item F[/TD]
[TD="align: right"]07/03/2018[/TD]
[/TR]
[TR]
[TD]Order 3[/TD]
[TD]Item G[/TD]
[TD="align: right"]13/02/2018[/TD]
[/TR]
[TR]
[TD]Order 3[/TD]
[TD]Item A[/TD]
[TD="align: right"]19/02/2018[/TD]
[/TR]
[TR]
[TD]Order 3[/TD]
[TD]Item D[/TD]
[TD="align: right"]10/03/2018[/TD]
[/TR]
</tbody>[/TABLE]


Thanks for any input and helping intention!

Tibi

 

[oldbrewer]

[TABLE="width: 797"]
<colgroup><col span="2"><col><col><col span="7"></colgroup><tbody>[TR]
[TD="colspan: 2"]DATASET 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order
[/TD]
[TD]Product[/TD]
[TD]Availability[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order 1[/TD]
[TD]Item A[/TD]
[TD="align: right"]07/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order 1[/TD]
[TD]Item B[/TD]
[TD="align: right"]07/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order 1[/TD]
[TD]Item C[/TD]
[TD="align: right"]23/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order 2[/TD]
[TD]Item D[/TD]
[TD="align: right"]07/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order 2[/TD]
[TD]Item E[/TD]
[TD="align: right"]14/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order 2[/TD]
[TD]Item A[/TD]
[TD="align: right"]13/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order 2[/TD]
[TD]Item C[/TD]
[TD="align: right"]01/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order 3[/TD]
[TD]Item F[/TD]
[TD="align: right"]07/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order 3[/TD]
[TD]Item G[/TD]
[TD="align: right"]13/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order 3[/TD]
[TD]Item A[/TD]
[TD="align: right"]19/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order 3[/TD]
[TD]Item D[/TD]
[TD="align: right"]16/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order 3[/TD]
[TD]Item A[/TD]
[TD="align: right"]20/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order 3[/TD]
[TD]Item H[/TD]
[TD="align: right"]19/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]DATASET 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order
[/TD]
[TD]Product[/TD]
[TD]Availability[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order 1[/TD]
[TD]Item A[/TD]
[TD]10/03/2018[/TD]
[TD]changed[/TD]
[TD]####[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order 2[/TD]
[TD]Item A[/TD]
[TD]20/03/2018[/TD]
[TD]changed[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order 2[/TD]
[TD]Item C[/TD]
[TD]25/03/2018[/TD]
[TD]changed[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order 3[/TD]
[TD]Item F[/TD]
[TD]07/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order 3[/TD]
[TD]Item G[/TD]
[TD]13/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order 3[/TD]
[TD]Item A[/TD]
[TD]19/02/2018[/TD]
[TD]changed[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order 3[/TD]
[TD]Item D[/TD]
[TD]10/03/2018[/TD]
[TD]changed[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]####[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 8"]=IF(SUMPRODUCT(($A$4:$A$16=A22)*($B$4:$B$16=B22)*($C$4:$C$16))<>C22,"changed","")[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]instead of putting the word changed or a blank in column D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]just use it as a conditional formatting equation[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]so C22 - the first availabilty date in the lower table would be[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 6"]=SUMPRODUCT(($A$4:$A$16=A22)*($B$4:$B$16=B22)*($C$4:$C$16))<>C22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Keti89]

Woah, thanks. Can you explain the logic behind?

Also, I realized it did not work for the last 2 rows, as those did not change.
I am not able to tell why, but I just did a quick check and those 2 remained the same, but the formula returns "changed".

 

[Keti89]

And also, on a side note, I would more than likely have the 2 sets of data on different sheets or next to each other, instead of one being below the other.

 

[oldbrewer]

[TABLE="class: cms_table, width: 797"]
<tbody>[TR]
[TD]Order 3[/TD]
[TD]Item A[/TD]
[TD="align: right"]20/02/2018

[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: cms_table, width: 797"]
<tbody>[TR]
[TD]Order 3[/TD]
[TD]Item A[/TD]
[TD]19/02/2018[/TD]
[TD]changed

they both changed, surely[/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

it does not matter where you locate the second table

=IF(SUMPRODUCT(($A$4:$A$16=A22)*($B$4:$B$16=B22)*($C$4:$C$16))<>C22,"changed","")
...................find order 1 in top table...........find item A..........find the date.......is it different

 

[Keti89]

Thanks, you are right, I actually had order 3 item A listed twice, so my bad.

I would love to understand the underlying logic, because sumproduct would make this a multiplication with 0 which should get 0 as a result, no?

Meanwhile, I think I also came up with a solution while walking home - creating a unique tag for each line (pairing the order and product ID) and doing a simple vlookup on that.

I really appreciate your efforts and the solution, thank you!

 

[oldbrewer]

if there is only one date that matches the criteria, that date is returned, so if you had
order 1 item a more than once it would fail

I often concatenate in this case eg order1 item a

 

[Keti89]

Yes, I thought about that actually and yes, that should be the case, order 1 item a should only appear once.
This is exactly what I had on my mind, concatenate, so get order1itema and then I can pull the date and do some colorcoding.

Does the sumproduct solution work even if order 1 item a occurs more than once? Because in the set I originally posted that was the case and it did mix it up.
Also, I do not understand the logic behind, as sumproduct on the texts returned 0 for me, so in theory it is 0 x 0 x date value, no?

 

[oldbrewer]

if no order and no item it will return 0, otherwise the date. Were there two order 1 item a's you would have to define, say, if latest or earliest date was to be considered