Index Aggregate Help

hajiali

Active Member
Joined
Sep 8, 2018
Messages
392
Office Version
  1. 2016
Platform
  1. Windows
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.

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAP
203/07/2021-04/10/2021SHIFT ADJUSTED03/07/2021-04/10/2021SHIFT ADJUSTED03/07/2021-04/10/2021SHIFT ADJUSTED03/07/2021-04/10/2021SHIFT ADJUSTED03/07/2021-04/10/2021SHIFT ADJUSTED03/07/2021-04/10/2021SHIFT ADJUSTED03/07/2021-04/10/2021SHIFT ADJUSTED03/07/2021-04/10/2021SHIFT ADJUSTED03/07/2021-04/10/2021SHIFT ADJUSTED03/07/2021-04/10/2021SHIFT ADJUSTED
3Sun, Mar 07Sun, Mar 07Sun, Mar 07Sun, Mar 07Sun, Mar 07Sun, Mar 07Sun, Mar 07Sun, Mar 07Sun, Mar 07Sun, Mar 073/83/103/83/133/13      3/8
4Mon, Mar 08Mon, Mar 0804A-12CMon, Mar 08Mon, Mar 08Mon, Mar 08Mon, Mar 08Mon, Mar 08Mon, Mar 08Mon, Mar 08Mon, Mar 083/93/143/93/183/15      3/9
5Tue, Mar 09Tue, Mar 0906B-15Tue, Mar 09Tue, Mar 09Tue, Mar 09Tue, Mar 09Tue, Mar 09Tue, Mar 09Tue, Mar 09Tue, Mar 093/93/253/14 3/19      3/10
6Wed, Mar 1007-15BWed, Mar 10Wed, Mar 10Wed, Mar 10Wed, Mar 10Wed, Mar 10Wed, Mar 10Wed, Mar 10Wed, Mar 10Wed, Mar 103/10 3/26        3/13
7Thu, Mar 11Thu, Mar 11Thu, Mar 11Thu, Mar 11Thu, Mar 11Thu, Mar 11Thu, Mar 11Thu, Mar 11Thu, Mar 11Thu, Mar 113/10          3/14
8Fri, Mar 12Fri, Mar 12Fri, Mar 12Fri, Mar 12Fri, Mar 12Fri, Mar 12Fri, Mar 12Fri, Mar 12Fri, Mar 12Fri, Mar 123/15          3/15
9Sat, Mar 13Sat, Mar 13Sat, Mar 1311A-19CSat, Mar 1313-21BSat, Mar 13Sat, Mar 13Sat, Mar 13Sat, Mar 13Sat, Mar 13Sat, Mar 133/18          3/18
10Sun, Mar 1408-15BSun, Mar 1404A-12CSun, Mar 14Sun, Mar 14Sun, Mar 14Sun, Mar 14Sun, Mar 14Sun, Mar 14Sun, Mar 14Sun, Mar 143/19          3/19
11Mon, Mar 15Mon, Mar 15Mon, Mar 15Mon, Mar 1516b-01Mon, Mar 15Mon, Mar 15Mon, Mar 15Mon, Mar 15Mon, Mar 15Mon, Mar 153/25          3/25
12Tue, Mar 16Tue, Mar 16Tue, Mar 16Tue, Mar 16Tue, Mar 16Tue, Mar 16Tue, Mar 16Tue, Mar 16Tue, Mar 16Tue, Mar 163/26          3/26
RELIEF AGENTS (TESTING)
Cell Formulas
RangeFormula
AE3:AE12AE3=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:AF12AF3=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:AG12AG3=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:AH12AH3=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:AI12AI3=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:AJ12AJ3=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:AK12AK3=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:AL12AL3=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:AM12AM3=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:AN12AN3=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:AO12AO3=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,D2A2=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,D3A3='[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:A12A4=IF($A$3+ROWS($A$4:A4)>'[CSA Schedule Tool.xlsm]CSA BUILD'!$I$1,"",$A$3+ROWS($A$4:A4))
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,132
Office Version
  1. 2019
Platform
  1. Windows
Try this in your AE3 formula, and down:
Excel Formula:
=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<>""))>=1),ROWS($AF$3:AF3))),"")

Since you're adding the conditions, there is some potential for multiple logic checks to be true, meaning one or more elements in the denominator array will have a value greater than 1...and when that "larger than 1" value is divided into the column indexing array (the numerator), the row numbers you want get messed up. By wrapping your entire denominator with one extra logic check (is each element in the array greater than or equal to 1), we can form the desired array.
 
Solution

hajiali

Active Member
Joined
Sep 8, 2018
Messages
392
Office Version
  1. 2016
Platform
  1. Windows
Thanks for that KRice. Much appreciate the tip as well. Thanks
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,132
Office Version
  1. 2019
Platform
  1. Windows
I'm happy to help. I just noticed, I incorrectly called your numerator a "column indexing array"...in this case, it is a "row indexing array."
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,435
Office Version
  1. 2016
Platform
  1. Windows
Hi hajiali

Try in AE3
Excel Formula:
=IFERROR(INDEX($A$3:$A$60,AGGREGATE(15,6,(ROW($A$3:$A$60)-ROW($A$2))/((($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<>"")>0)),ROWS($AF$3:AF3))),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,808
Members
416,884
Latest member
leeshjay

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