Complex tracking of changing dates

Keti89

New Member
Joined
Feb 8, 2018
Messages
10
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

OrderProductAvailability
Order 1Item A07/03/2018
Order 1Item B07/03/2018
Order 1Item C23/02/2018
Order 2Item D07/03/2018
Order 2Item E14/02/2018
Order 2Item A13/03/2018
Order 2Item C01/03/2018
Order 3Item F07/03/2018
Order 3Item G13/02/2018
Order 3Item A19/02/2018
Order 3Item D16/02/2018
Order 3Item A20/02/2018
Order 3Item H19/02/2018

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

DATASET 2

OrderProductAvailability
Order 1Item A10/03/2018
Order 2Item A20/03/2018
Order 2Item C25/03/2018
Order 3Item F07/03/2018
Order 3Item G13/02/2018
Order 3Item A19/02/2018
Order 3Item D10/03/2018

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


Thanks for any input and helping intention!

Tibi
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,003
DATASET 1
Order
ProductAvailability
Order 1Item A07/03/2018
Order 1Item B07/03/2018
Order 1Item C23/02/2018
Order 2Item D07/03/2018
Order 2Item E14/02/2018
Order 2Item A13/03/2018
Order 2Item C01/03/2018
Order 3Item F07/03/2018
Order 3Item G13/02/2018
Order 3Item A19/02/2018
Order 3Item D16/02/2018
Order 3Item A20/02/2018
Order 3Item H19/02/2018
DATASET 2
Order
ProductAvailability
Order 1Item A10/03/2018changed####
Order 2Item A20/03/2018changed
Order 2Item C25/03/2018changed
Order 3Item F07/03/2018
Order 3Item G13/02/2018
Order 3Item A19/02/2018changed
Order 3Item D10/03/2018changed
####
=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

New Member
Joined
Feb 8, 2018
Messages
10
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

New Member
Joined
Feb 8, 2018
Messages
10
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

Board Regular
Joined
Apr 11, 2010
Messages
11,003

ADVERTISEMENT

Order 3Item A20/02/2018


<tbody>
</tbody>

Order 3Item A19/02/2018changed

they both changed, surely

<tbody>
</tbody>
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,003
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

New Member
Joined
Feb 8, 2018
Messages
10

ADVERTISEMENT

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!
 
Last edited:

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,003
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

New Member
Joined
Feb 8, 2018
Messages
10
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

Board Regular
Joined
Apr 11, 2010
Messages
11,003
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,072
Messages
5,526,628
Members
409,713
Latest member
roman9980

This Week's Hot Topics

Top