date match in a range

Sprinter99

New Member
Joined
Mar 11, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hi,
I have a formula that i am struggling with, i have attached a picture of the spreadsheet i am working on the Blue shaded cells in row 4, are the dates when an application will complete testing (1 seperate app per column).

The Blue cells in column "K" is the desired date when a user wants to be migrated, M6:AD25 is a range that will be "blank" or contain the word "Required", each row can contain a number of blanks if the user does not require the applicaiton indicated in the column, or "Required" if they do.

Clearly i cannot migrate a user if any of their required apps have not completed testing, so in column L i would like a simple "Good" if all required apps have a ready date that is earlier than the planned migration date (Conditional formatting is used to show the app as Green if its good, e.g. Cell N7 has an app requirement, and the app ready date is earlier than the user cutover, all is good), if an app will not be ready in time (Cell M7 shows that the user in row 7 requires a migration date that is one day before the app is ready), this turns the cell red.

Now the fun part, how do I create a formula or array formula that searchs all apps for each user (per row), and compares each one with the applicaiton ready data on a per app basis where an app is required, and if "any" return a date that is greater than the migration date, a suitable word "Bad will do" is inserted in the relevent line in column L to show that the user is flagged as having an app problem, if app ready dates are smaller than the migration date then a simple good would be useful in column L.

Hope i have explained it correctly, thanks in advance, its driving me mad.

User_Migration_Question.jpg
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,969
Office Version
  1. 365
Platform
  1. Windows
Can you post a small sample to the thread.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 

Sprinter99

New Member
Joined
Mar 11, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hi,
I have a formula that i am struggling with, i have attached a picture of the spreadsheet i am working on the Blue shaded cells in row 4, are the dates when an application will complete testing (1 seperate app per column).

The Blue cells in column "K" is the desired date when a user wants to be migrated, M6:AD25 is a range that will be "blank" or contain the word "Required", each row can contain a number of blanks if the user does not require the applicaiton indicated in the column, or "Required" if they do.

Clearly i cannot migrate a user if any of their required apps have not completed testing, so in column L i would like a simple "Good" if all required apps have a ready date that is earlier than the planned migration date (Conditional formatting is used to show the app as Green if its good, e.g. Cell N7 has an app requirement, and the app ready date is earlier than the user cutover, all is good), if an app will not be ready in time (Cell M7 shows that the user in row 7 requires a migration date that is one day before the app is ready), this turns the cell red.

Now the fun part, how do I create a formula or array formula that searchs all apps for each user (per row), and compares each one with the applicaiton ready data on a per app basis where an app is required, and if "any" return a date that is greater than the migration date, a suitable word "Bad will do" is inserted in the relevent line in column L to show that the user is flagged as having an app problem, if app ready dates are smaller than the migration date then a simple good would be useful in column L.

Hope i have explained it correctly, thanks in advance, its driving me mad.

View attachment 34143

Clenaed_Migration_Planning.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1Application RequirementPilotPilotPilotPilotPilotPilotPilotPilotPilotPilotPilotPilotPilotMainMain
2Application Common NameApp_1App_2App_3App_4App_5App_6App_7App_8App_9App_10App_11App_12App_13App_14App_15
3Wave RequirementPilotPilotPilotPilotPilotPilotPilotPilotPilotPilotPilotPilotPilotPilotPilot
4ApplicationAvailability Date09/03/202107/03/202116/05/202117/05/202118/05/202119/05/202120/05/202121/05/202122/05/202123/05/202124/05/202125/05/202126/05/202127/05/202128/05/2021
5SerialsAMAccountNameCompanyLocationBussiness GroupIn ScopeWaveCutoverWave_StartWave_EndMigration_DateApplication AvailabilityApplication_1Application_2Application_3Application_4Application_5Application_6Application_7Application_8Application_9Application_10Application_11Application_12Application_13Application_14Application_15
611Company_1Town_1BG_1In Scope    
722Company_2Town_2BG_2In ScopeWave_1Monday11/05/202014/05/202108/03/2021BadRequiredRequiredRequired
833Company_3Town_3BG_3In ScopeWave_1Thursday11/05/202014/05/202111/03/2021GoodRequired
944Company_4Town_4BG_4In ScopeWave_1Friday11/05/202014/05/202112/03/2021 
1055Company_5Town_5BG_5In ScopeWave_1Monday11/05/202014/05/202108/03/2021RequiredRequired
1166Company_6Town_6BG_6In ScopePilotSaturday06/04/202125/04/202110/04/2021
1277Company_7Town_7BG_7In Scope    
1388Company_8Town_8BG_8In Scope    
1499Company_9Town_9BG_9In Scope    
151010Company_10Town_10BG_10In Scope    
161111Company_11Town_11BG_11In Scope    
171212Company_12Town_12BG_12In Scope    
181313Company_13Town_13BG_13In Scope    
191414Company_14Town_14BG_14In Scope    
201515Company_15Town_15BG_15In Scope    
211616Company_16Town_16BG_16In Scope    
221717Company_17Town_17BG_17In Scope    
231818Company_18Town_18BG_18In Scope    
241919Company_19Town_19BG_19In Scope    
252020Company_20Town_20BG_20In Scope    
Migration Plan
Cell Formulas
RangeFormula
M2:AA2N2=INDEX(Lookups!$B:$B,MATCH('Migration Plan'!N$5,Lookups!$A:$A,0))
M3:AA3N3=INDEX(Lookups!$C:$C,MATCH('Migration Plan'!N$2,Lookups!$B:$B,0))
M4M4=IF(INDEX(Lookups!$E:$E,MATCH('Migration Plan'!M$2,Lookups!$B:$B,0))="","Not Approved",INDEX(Lookups!$E:$E,MATCH('Migration Plan'!M$2,Lookups!$B:$B,0)))
F6:F25F6=IFERROR(IF(FIND("DELETED",INDEX(#REF!,MATCH('Migration Plan'!$B6,#REF!,0)),1)=TRUE,"","Deleted"),"In Scope")
G6:G25G6=IF(K6="","",INDEX(Lookups!$I$2:$I$9,MIN(IF((DATEVALUE(RIGHT(Lookups!$L$2:$L$9,LEN(Lookups!$L$2:$L$9)-FIND(":",Lookups!$L$2:$L$9)))>=$K6)*(DATEVALUE(LEFT(Lookups!$L$2:$L$9,FIND(":",Lookups!$L$2:$L$9)-1))<=$K6),MATCH(ROW(Lookups!$L$2:$L$9),ROW(Lookups!$I$2:$I$9)),""))))
H6:H25H6=IF(K6="","",TEXT(K6,"DDDD"))
I6:I25I6=IFERROR(INDEX(Lookups!J:J,MATCH('Migration Plan'!G6,Lookups!I:I,0)),"")
J6:J25J6=IFERROR(INDEX(Lookups!K:K,MATCH('Migration Plan'!G6,Lookups!I:I,0)),"")
L7:L9L7=IF(M7="Required",IF($M$4>K7,"Bad","Good"),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
Lookups!_FilterDatabase=Lookups!$A$1:$G$44M2:AA2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AA6:AA25Expression=AND($K6>$AA$4,AA6="Required")textNO
AA6:AA25Expression=AND($K6<$AA$4,AA6="Required")textNO
Z6:Z25Expression=AND($K6>$Z$4,Z6="Required")textNO
Z6:Z25Expression=AND($K6<$Z$4,Z6="Required")textNO
Y6:Y25Expression=AND($K6>$Y$4,Y6="Required")textNO
Y6:Y25Expression=AND($K6<$Y$4,Y6="Required")textNO
X6:X25Expression=AND($K6>$X$4,X6="Required")textNO
X6:X25Expression=AND($K6<$X$4,X6="Required")textNO
W6:W25Expression=AND($K6>$W$4,W6="Required")textNO
W6:W25Expression=AND($K6<$W$4,W6="Required")textNO
V6:V25Expression=AND($K6>$V$4,V6="Required")textNO
V6:V25Expression=AND($K6<$V$4,V6="Required")textNO
U6:U25Expression=AND($K6<$U$4,U6="Required")textNO
U6:U25Expression=AND($K6>$U$4,U6="Required")textNO
T6:T25Expression=AND($K6>$T$4,T6="Required")textNO
T6Expression=AND($K6<$T$4,T6="Required")textNO
S6Expression=AND($K6>$S$4,S6="Required")textNO
S6:S25Expression=AND($K6<$S$4,S6="Required")textNO
R6:R25Expression=AND($K6>$R$4,R6="Required")textNO
R6:R25Expression=AND($K6<$R$4,R6="Required")textNO
Q6:Q25Expression=AND($K6>$Q$4,Q6="Required")textNO
Q6:Q25Expression=AND($K6<$Q$4,Q6="Required")textNO
P6:P25Expression=AND($K6>P$4,P6="Required")textNO
P6:P25Expression=AND($K6<P$4,P6="Required")textNO
O6:O25Expression=AND($K6>$O$4,$O6="Required")textNO
O6:O25Expression=AND($K6<$O$4,$O6="Required")textNO
N6:N25Expression=AND($K6>$N$4,$N6="Required")textNO
N6:N25Expression=AND($K6<$N$4,$N6="Required")textNO
M6:M25Expression=AND($K6<$M$4,$M6="Required")textNO
M6:M25Expression=AND($K6>$M$4,$M6="Required")textNO
Cells with Data Validation
CellAllowCriteria
M1:AA1ListSelect, Pilot, Main
M6:AA25ListRequired
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,969
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, how about
Excel Formula:
=IF(COUNTIFS(M7:AA7,"Required",$M$4:$AA$4,">"&K7)>0,"Bad",IF(COUNTIFS(M7:AA7,"Required")>0, "Good",""))
 
Solution

Sprinter99

New Member
Joined
Mar 11, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Thanks for that, how about
Excel Formula:
=IF(COUNTIFS(M7:AA7,"Required",$M$4:$AA$4,">"&K7)>0,"Bad",IF(COUNTIFS(M7:AA7,"Required")>0, "Good",""))
That works like a charm, thank you, now i am going to spend some time decyphering it to ensure that i understand exactly what its doing as i think it will be used again in the future, thanks

Very much appreciated
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,969
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,295
Messages
5,641,401
Members
417,208
Latest member
wendy823

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