Check Redo Based on Date difference

mohdamir1989

New Member
Joined
Oct 17, 2017
Messages
42
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
 

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.
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
 
Upvote 0
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.
nvTwA6


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

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

Best Regards
 
Upvote 0
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
 
Upvote 0
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>
 
Upvote 0
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...")
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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