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

Order	Product	Availability
Order 1	Item A	07/03/2018
Order 1	Item B	07/03/2018
Order 1	Item C	23/02/2018
Order 2	Item D	07/03/2018
Order 2	Item E	14/02/2018
Order 2	Item A	13/03/2018
Order 2	Item C	01/03/2018
Order 3	Item F	07/03/2018
Order 3	Item G	13/02/2018
Order 3	Item A	19/02/2018
Order 3	Item D	16/02/2018
Order 3	Item A	20/02/2018
Order 3	Item H	19/02/2018

<colgroup><col><col><col></colgroup><tbody>
</tbody>

DATASET 2

Order	Product	Availability
Order 1	Item A	10/03/2018
Order 2	Item A	20/03/2018
Order 2	Item C	25/03/2018
Order 3	Item F	07/03/2018
Order 3	Item G	13/02/2018
Order 3	Item A	19/02/2018
Order 3	Item D	10/03/2018

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Thanks for any input and helping intention!

Tibi

 

[oldbrewer]

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

<colgroup><col span="2"><col><col><col span="7"></colgroup><tbody>
</tbody>

 

[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]

Order 3

Item A

20/02/2018

<tbody>
</tbody>

Order 3

Item A

19/02/2018

changed they both changed, surely

<tbody>
</tbody>

 

[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