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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
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

Well-known Member
Joined
Apr 11, 2010
Messages
11,005

ADVERTISEMENT

Order 3Item A20/02/2018


<tbody>
</tbody>

Order 3Item A19/02/2018changed

they both changed, surely

<tbody>
</tbody>
 

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
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

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
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

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
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
 

Forum statistics

Threads
1,147,995
Messages
5,744,230
Members
423,853
Latest member
cathevs09

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top