Complex tracking of changing dates

K

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

[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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
[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]
 
Upvote 0
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".
 
Upvote 0
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.
 
Upvote 0
[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]
 
Upvote 0
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

 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Ranking values (in descending order) with 1 criteria - BUT without skipping numbers
Replies
3
Views
276
Fluff
Fluff
V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
496
etaf
etaf
L
  • Question Question
INDEX MATCH
Replies
1
Views
323
Fluff
Fluff
M
  • Question Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
540
mrshuck
M
A
  • Solved
Extract the names of the Items purchased at different prices
Replies
2
Views
136
ala995
A

Forum statistics

Threads
1,223,248
Messages
6,171,011
Members
452,374
Latest member
keccles

Share this page

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
Back
Top