Using the table below I used Index Aggregate in columns AF I have it pull all dates from column A if value in B exists, Column AG I have it pull all dates from column D if value in C exists...etc all the way to AO. I need help to combining all 10 formulas into 1 formula. I attempted to do it in Column AE but not getting the correct answer. I have two 3/9 dates and two 3/10 which is not correct. I Do not need the dates duplicated even if they are values appear in the same date in multiple columns. The solution that I looking for is in Column AP. Would prefer Non-Array Formula. any help is greatly apricated.
Cell Formulas | ||
---|---|---|
Range | Formula | |
AE3:AE12 | AE3 | =IFERROR(INDEX($A$3:$A$60,AGGREGATE(15,6,(ROW($A$3:$A$60)-ROW($A$3)+1)/(($B$3:$B$60<>"")+($E$3:$E$60<>"")+($H$3:$H$60<>"")+($K$3:$K$60<>"")+($N$3:$N$60<>"")+($Q$3:$Q$60<>"")+($T$3:$T$60<>"")+($W$3:$W$60<>"")+($Z$3:$Z$60<>"")+($AC$3:$AC$60<>"")),ROWS($AF$3:AF3))),"") |
AF3:AF12 | AF3 | =IFERROR(INDEX($A$3:$A$60,AGGREGATE(15,6,(ROW($A$3:$A$60)-ROW($A$3)+1)/(($B$3:$B$60<>"")),ROWS($AF$3:AF3))),"") |
AG3:AG12 | AG3 | =IFERROR(INDEX($A$3:$A$60,AGGREGATE(15,6,(ROW($A$3:$A$60)-ROW($A$3)+1)/(($E$3:$E$60<>"")),ROWS($AG$3:AG3))),"") |
AH3:AH12 | AH3 | =IFERROR(INDEX($A$3:$A$60,AGGREGATE(15,6,(ROW($A$3:$A$60)-ROW($A$3)+1)/(($H$3:$H$60<>"")),ROWS($AG$3:AG3))),"") |
AI3:AI12 | AI3 | =IFERROR(INDEX($A$3:$A$60,AGGREGATE(15,6,(ROW($A$3:$A$60)-ROW($A$3)+1)/(($K$3:$K$60<>"")),ROWS($AG$3:AG3))),"") |
AJ3:AJ12 | AJ3 | =IFERROR(INDEX($A$3:$A$60,AGGREGATE(15,6,(ROW($A$3:$A$60)-ROW($A$3)+1)/(($N$3:$N$60<>"")),ROWS($AG$3:AG3))),"") |
AK3:AK12 | AK3 | =IFERROR(INDEX($A$3:$A$60,AGGREGATE(15,6,(ROW($A$3:$A$60)-ROW($A$3)+1)/(($Q$3:$Q$60<>"")),ROWS($AG$3:AG3))),"") |
AL3:AL12 | AL3 | =IFERROR(INDEX($A$3:$A$60,AGGREGATE(15,6,(ROW($A$3:$A$60)-ROW($A$3)+1)/(($T$3:$T$60<>"")),ROWS($AG$3:AG3))),"") |
AM3:AM12 | AM3 | =IFERROR(INDEX($A$3:$A$60,AGGREGATE(15,6,(ROW($A$3:$A$60)-ROW($A$3)+1)/(($W$3:$W$60<>"")),ROWS($AG$3:AG3))),"") |
AN3:AN12 | AN3 | =IFERROR(INDEX($A$3:$A$60,AGGREGATE(15,6,(ROW($A$3:$A$60)-ROW($A$3)+1)/(($Z$3:$Z$60<>"")),ROWS($AG$3:AG3))),"") |
AO3:AO12 | AO3 | =IFERROR(INDEX($A$3:$A$60,AGGREGATE(15,6,(ROW($A$3:$A$60)-ROW($A$3)+1)/(($AC$3:$AC$60<>"")),ROWS($AG$3:AG3))),"") |
A2,AB2,Y2,V2,S2,P2,M2,J2,G2,D2 | A2 | =CONCATENATE(TEXT('[CSA Schedule Tool.xlsm]CSA BUILD'!$B$1,"MM/DD/YYYY"),"-",TEXT('[CSA Schedule Tool.xlsm]CSA BUILD'!$I$1,"MM/DD/YYYY")) |
A3,AB3,Y3,V3,S3,P3,M3,J3,G3,D3 | A3 | ='[CSA Schedule Tool.xlsm]CSA BUILD'!$B$1 |
AB4:AB12,Y4:Y12,V4:V12,S4:S12,P4:P12,M4:M12,J4:J12,G4:G12,D4:D12,A4:A12 | A4 | =IF($A$3+ROWS($A$4:A4)>'[CSA Schedule Tool.xlsm]CSA BUILD'!$I$1,"",$A$3+ROWS($A$4:A4)) |