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]

Hi mohdamir,

I'm struggling to understand what you are trying to do. I think you are going to need to post a screenshot of each of your sheets.

Cheers
JB

 

[mohdamir1989]

Hi mohdamir,

I'm struggling to understand what you are trying to do. I think you are going to need to post a screenshot of each of your sheets.

Cheers
JB

Hey Bellman,

Thank you so much for considering and trying to understand the issue.

https://ibb.co/nvTwA6
https://ibb.co/h1PQ3R

Please check screenshots, I couldn't insert screenshots here. So linked as above

Best Regards

 

[bellman101]

Ok, I think I kinda get it.

You can say:

=If(EDATE(TODAY(),IF(T2="Visit",-1,-3)<R2,"Redo Service....","")

Designed to be filled down from row 2. You may have to tweak it for your file. This also assumes you already have the other stuff in there, like Visit/No Visit, and the dates.

Cheers
JB

 

[mohdamir1989]

Ok, I think I kinda get it.

You can say:

=If(EDATE(TODAY(),IF(T2="Visit",-1,-3)<r2,"redo service....","")

Designed to be filled down from row 2. You may have to tweak it for your file. This also assumes you already have the other stuff in there, like Visit/No Visit, and the dates.

Cheers
JB

Dear Bellman,

Actually I have to check Bar Code/Asset code in previous sheet to see if that is matching with in 1 month/3 months based on criteria. In your formula I couldn't see reference to Bar Code/Asset Code.

Basically work done of single bar code have warranty of 1 month in case of visit and warranty of 3 months in case of no visit. If if falls in warranty period it is considered redo with no incentive.

Hope I am able to clear my point

Really appreciate your efforts to understand and reply </r2,"redo>

 

[bellman101]

Ok, sorry, I had actually built that part first, just forgot to include it at the end! Notice the If statement is incomplete. Had a few beers last night! :D

Also, I notice those are future dates. Does that mean they should be on or before 25th Dec/25th Feb, as opposed to after 25th Oct/25th Aug?

If so you and each combo only appears once in the data, then you can use a SUMIFS to get the date and work that into the previous formula... Try this:

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

 

[mohdamir1989]

Ok, sorry, I had actually built that part first, just forgot to include it at the end! Notice the If statement is incomplete. Had a few beers last night! :D

Also, I notice those are future dates. Does that mean they should be on or before 25th Dec/25th Feb, as opposed to after 25th Oct/25th Aug?

If so you and each combo only appears once in the data, then you can use a SUMIFS to get the date and work that into the previous formula... Try this:

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

Dear Bellman,

Hopefully its working but I'm not clear with references you defined. Can you please share how you arranged data as per references in formula

Best Regards

 

[bellman101]

Ok, so this all assumed your fist formula would be on row 2. If it is further down than that, just change all 3 instances of '2' to whatever row you are working on.

I also assumed this formula was going in cell S2, as you said the two codes were in P & Q, so date is R, Redo is S, and Visit is T. If this is wrong, change the 'T2' to the correct row and column for 'Visit'. Same for P2 & Q2, I think these are the right columns though as they are the ones you specified in your post, so maybe just adjust the row number if necessary.

You mentioned the data sheet is called 'Previous', and you didn't say, but I assumed you would have the data in columns A:E. So 'A:A' should be looking at Bar Code, 'B:B' at Asset Code, and 'C:C' is the date field.

Also, remember this will only work if there are no duplicate bar/asset code combos in your list.

Cheers
JB

 

[mohdamir1989]

Ok, so this all assumed your fist formula would be on row 2. If it is further down than that, just change all 3 instances of '2' to whatever row you are working on.

I also assumed this formula was going in cell S2, as you said the two codes were in P & Q, so date is R, Redo is S, and Visit is T. If this is wrong, change the 'T2' to the correct row and column for 'Visit'. Same for P2 & Q2, I think these are the right columns though as they are the ones you specified in your post, so maybe just adjust the row number if necessary.

You mentioned the data sheet is called 'Previous', and you didn't say, but I assumed you would have the data in columns A:E. So 'A:A' should be looking at Bar Code, 'B:B' at Asset Code, and 'C:C' is the date field.

Also, remember this will only work if there are no duplicate bar/asset code combos in your list.

Cheers
JB

Thank you so very much Bellman, Really Appreciate your prompt and continuous feedback. I'm stuck with the duplicates as the bar code repeats more than once may twice or more times. Still your efforts are really appreciated.

I was thinking for another way to do so as currently I have arranged data (Date wise) in previous sheet and using lookup which offcourse is returning first instance (last instance date wise). Which further need to evaluate manually.

There's another way to do so with =VLOOKUP(MAX(A2:A9),A2:C9,3,0)
Which I found in today's search. But I am unable to understand the syntax as this is not looking for Barcode/Asset Code instead is using date only

Best Regards

 

[bellman101]

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?