Check Redo Based on Date difference

[mohdamir1989]

Hi,

I am looking for a solution to check redo based on multiple criteria. There are basically 2 values in column P and Q it should check both in another sheet named "Previous" if value of P Q matches values in "Previous" and is with 1 month (matching value "Visit") or 3 months (Matching value "no visit") it should write redo.

For Example

[TABLE="width: 500"]
<tbody>[TR]
[TD]25-Nov-17[/TD]
[TD]300123129[/TD]
[TD]M129129002[/TD]
[TD]Visit[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29-Nov-17[/TD]
[TD]300190332[/TD]
[TD]CC72801239[/TD]
[TD][/TD]
[TD]No Visit[/TD]
[/TR]
[TR]
[TD]30-Nov-17[/TD]
[TD]300920912[/TD]
[TD]F030000124[/TD]
[TD]Visit[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This data is in sheet 1

Similar format is in sheet "Previous" Now if the value matches in other sheet within 1 Month. It should write redo.

I've gone through the web regarding this but couldn't understand right output.

Your guidance in this regards is highly appreciated.

Best Regards

 

[bellman101]

And yes, that VLOOKUP will return max date out of all rows.

IF you have office 16/365 then you can use the new MAXIFS function:

=IF(EDATE(TODAY(),IF(T2="Visit",1,3))>MAXIFS(Previous!C:C,Previous!A:A,P2,Previous!B:B,Q2),"","Redo Visit...")

If you are on an earlier version, then you will need a helper column. In your data add a new column. We'll say column F, and in the top data row (row 2?) enter '=A2&B2'. Fill it to the bottom, and you should then have Bar Code & Asset code in one string.

You can then try this:

=IF(EDATE(TODAY(),IF(T2="Visit",1,3))>VLOOKUP(P2&Q2,CHOOSE({1,2},Previous!F:F,Previous!C:C),2,0),"","Redo Visit...")

Cheers
JB

 

[mohdamir1989]

It doesn't matter if the bar code repeats, or the asset code repeats, as long as they don't both repeat.

e.g.

1111 3333
1111 4444
1111 5555
2222 3333
2222 4444

This is fine, as no two rows are the same.

Hope that helps?

Awesome Work Bellman, Really a big round of applause. :D

Cheers Man,

Is it possible if I may refer the Visit to a non blank cell which means it can contain several other duties as well :D

 

[bellman101]

No bother, yeah, just replace it with the cell reference. use dollars in the cell address if it is always the same cell.

 

[mohdamir1989]

No bother, yeah, just replace it with the cell reference. use dollars in the cell address if it is always the same cell.

Dear Bellman,

Thank your for continuous support and guidance. The formula is working great but I found little problem as per requirement. Keeping in view the same scenario I've found below array formula which is working to provide last date against the same Bar Code (P column Value) as I observed I'll have to check last "Customer remarks" each time to verify.

=MAX(IF(P5=Previous!D:D,Previous!H:H),IF(Q5=Previous!E:E,Previous!H:H))

Now I'm wondering how to grab "Tech Remarks" which are located in L Column of previous sheet in the same result with this formula

Best Regards

 

[bellman101]

Hi,

I'm not sure that MAX(IF( formula will do exactly what you want. You may want to test it, but the first 'IF(' should return the max date where D:D = P5. The second will return the max date where E:E = Q5. These two IFs are completely independent. The MAX() will now contain two dates, and return the max of those two. I'm not sure how to get column L for the same row with a formula. It may have to be some simple VBA.

Cheers
JB