1 Workbook w/7worksheets
E
S
M
K
A
C
R
Worksheet E needs 4 “buttons” to start macros (1 each for worksheets S, M, K, and R) plus will use specified data for each to specify variables for worksheet columns
Worksheet K (Run this for all rows (typically 180,000 – 200,00))
Column K has 2 variables that will be text characters defined in Worksheet E, C13 and D13
Column L has variables that will be defined in Worksheet E, C14, D14, E14, F14, G14
Column M has variables that will be defined in Worksheet E, C15, D15, E15, F15, G15
Currently, this is a single cell argument that must be run in the sequence provided
Column P IF([@K]=E!C13,IFNA(IF([@L]=E!C14,E!C16,IF([@L]=E!D14,E!C16,IF([@L]=E!E14,E!C16,IF([@L]=E!F14,E!C16,IF([@L]=E!G14,E!C16,IF([@L]=[@M],E!D16,E!E16)))))),E!F16),E!D13)
Worksheet M (Run this for all rows (typically 120,000 – 150,00))
Column B contains text
Column N contains text or might be blank
Column O contains text or might be blank
Column R contains text or might be blank
Note that text searches are case sensitive
Column X OR(ISNUMBER(FIND(E!C21,[@N],1)),ISNUMBER(FIND(E!C21,[@R],1)),ISNUMBER(FIND(E!C21,[@O],1)))
Column Y OR(ISNUMBER(FIND(E!C22,[@N],1)),ISNUMBER(FIND(E!C22,[@R],1)),ISNUMBER(FIND(E!C22,[@O],1)))
Column Z OR(ISNUMBER(FIND(E!C23,[@N],1)),ISNUMBER(FIND(E!C23,[@R],1)),ISNUMBER(FIND(E!C23,[@O],1)))
Column AA OR(ISNUMBER(FIND(E!C24,[@N],1)),ISNUMBER(FIND(E!C24,[@R],1)),ISNUMBER(FIND(E!C24,[@O],1)))
Column AB IF([@B]=E!C20,OR(ISNUMBER(FIND(E!D26,[@N],1)),ISNUMBER(FIND(E!G25,[@N],1)),ISNUMBER(FIND(E!E25,[@N],1)),ISNUMBER(FIND(E!F25,[@N],1)),ISNUMBER(FIND(E!D25,[@R],1)),ISNUMBER(FIND(E!G25,[@R],1)),ISNUMBER(FIND(E!E25,[@R],1)),ISNUMBER(FIND(E!F25,[@R],1)),ISNUMBER(FIND(E!D25,[@O],1)),ISNUMBER(FIND(E!G25,[@O],1)),ISNUMBER(FIND(E!E25,[@O],1)),ISNUMBER(FIND(E!F25,[@O],1)),ISNUMBER(FIND(E!H25,[@N],1)),ISNUMBER(FIND(E!H25,[@R],1)),ISNUMBER(FIND(E!H25,[@O],1))),FALSE)
Column AC OR([@X]=TRUE,[@Y]=TRUE,[@Z]=TRUE,[@AA]=TRUE,[@AB]=TRUE)
Column AD IF([@L]="",FALSE,IF(43343-[@L]<730,TRUE,FALSE))
Column AE INDEX(A!C:C,MATCH(LEFT([@E],4),A!B:B,0))
Column AF IFNA(IF(INDEX(A!G:G,MATCH(LEFT([@E],4),A!B:B,0))<YEAR([@I]),YEAR([@I]),INDEX(A!G:G,MATCH(LEFT([@E],4),A!B:B,0))),"Unknown")
Column AG INDEX(A!G:G,MATCH(LEFT([@E],4),A!B:B,0))
Column AH is blank
Column AI this is what I use currently (43343 is an older date) OR(AND(IF([@H]>(43343-730),TRUE,FALSE),IF(([@H]-[@I])<730,TRUE,FALSE)),[@AE]=E!C32,[@AE]=E!D32,[@AE]=E!E32,[@AE]=E!F32,[@AE]=E!G32,[@AE]=E!H32,[@AE]=E!I32,[@AE]=E!J32,[@AE]=E!K32,[@AE]=E!L32,[@AE]=E!M32,[@AE]=E!N32,[@AE]=E!O32,[@AE]=E!P32,[@AE]=E!Q32,[@AE]=E!R32,[@AE]=E!S32,[@AE]=E!T32)
Column AJ this is what I use currently OR([@AD],[@AK])
Column AK this is what I use currently (43312 is an older date) AND(IF([@H]>(43312-730),TRUE,FALSE),IF(([@H]-[@I])<730,TRUE,FALSE))
Column AL this is what I use currently OR(ISNUMBER(FIND(E!C35,[@N],1)),ISNUMBER(FIND(E!C35,[@R],1)),ISNUMBER(FIND(E!C35,[@O],1)))
Column AM this is what I use currently OR([@X]=TRUE,[@Y]=TRUE,[@Z]=TRUE,[@AA]=TRUE,[@AB]=TRUE,[@AL]=TRUE)
Column AN IFNA(MATCH([@E],S!L:L,0),”Not in S”)
Worksheet S in file