Option Explicit
Sub Print2AEdited()
With Sheets("2A Extract")
'=VLOOKUP(D2,CHOOSE({1,2},'2A'!C7:C10000,'2A'!A7:A10000),2,0)
Range("A2").FormulaArray = "=VLOOKUP(D2,CHOOSE({1,2},'2A'!C7:C10000,'2A'!A7:A10000),2,0)"
'=VLOOKUP(D2,IF(ISNUMBER(D2),--B2B!$C:$E,B2B!$C:$E),3,0)
Range("B2").FormulaArray = "=VLOOKUP(D2,IF(ISNUMBER(D2),--B2B!$C:$E,B2B!$C:$E),3,0)"
'=VLOOKUP(D2,IF(ISNUMBER(D2),--B2B!$C:$E,B2B!$C:$E),3,0)
Range("C2").FormulaArray = "=VLOOKUP(D2,IF(ISNUMBER(D2),--B2B!$C:$E,B2B!$C:$E),3,0)"
' =IFERROR(IFERROR(VALUE(SUBSTITUTE(INDEX('2A'!$C$7:$C$10000,AGGREGATE(15,6,ROW($A$1:$A$9999)/(RIGHT('2A'!$C$7:$C$10000,5)="Total"),ROWS($1:1))),"-Total","")),SUBSTITUTE(INDEX('2A'!$C$7:$C$5963,AGGREGATE(15,6,ROW($A$1:$A$5991)/(RIGHT('2A'!$C$7:$C$5963,5)="Total"),ROWS($1:1))),"-Total","")),"")
Range("D2").Formula = "=IFERROR(IFERROR(VALUE(SUBSTITUTE(INDEX('2A'!$C$7:$C$10000,AGGREGATE(15,6,ROW($A$1:$A$9999)/(RIGHT('2A'!$C$7:$C$10000,5)=""Total""),ROWS($1:1))),""-Total"","""")),SUBSTITUTE(INDEX('2A'!$C$7:$C$5963,AGGREGATE(15,6,ROW($A$1:$A$5991)/(RIGHT('2A'!$C$7:$C$5963,5)=""Total""),ROWS($1:1))),""-Total"","""")),"""")"
' =IFERROR(IFERROR(VALUE(SUBSTITUTE(INDEX('2A'!$C$7:$C$10000,AGGREGATE(15,6,ROW($A$1:$A$9999)/(RIGHT('2A'!$C$7:$C$10000,5)="Total"),ROWS($1:1))),"-Total","")),SUBSTITUTE(INDEX('2A'!$C$7:$C$5963,AGGREGATE(15,6,ROW($A$1:$A$5991)/(RIGHT('2A'!$C$7:$C$5963,5)="Total"),ROWS($1:1))),"-Total","")),"")
Range("E2").Formula = "=IFERROR(IFERROR(VALUE(SUBSTITUTE(INDEX('2A'!$C$7:$C$10000,AGGREGATE(15,6,ROW($A$1:$A$9999)/(RIGHT('2A'!$C$7:$C$10000,5)=""Total""),ROWS($1:1))),""-Total"","""")),SUBSTITUTE(INDEX('2A'!$C$7:$C$5963,AGGREGATE(15,6,ROW($A$1:$A$5991)/(RIGHT('2A'!$C$7:$C$5963,5)=""Total""),ROWS($1:1))),""-Total"","""")),"""")"
'=INDEX('2A'!$B$7:$B$5947,MATCH(A2&D2,'2A'!$A$7:$A$5947&'2A'!$C$7:$C$5947,0))
Range("F2").FormulaArray = "=INDEX('2A'!$B$7:$B$5947,MATCH(A2&D2,'2A'!$A$7:$A$5947&'2A'!$C$7:$C$5947,0))"
' =INDEX('2A'!$F$7:$F$5947,MATCH(A2&D2,'2A'!$A$7:$A$5947&'2A'!$C$7:$C$5947,0)) ... Array Formula
Range("G2").FormulaArray = "=INDEX('2A'!$F$7:$F$5947,MATCH(A2&D2,'2A'!$A$7:$A$5947&'2A'!$C$7:$C$5947,0))" '
' =SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$K$7:$K$9947=0)*('2A'!$I$7:$I$9947=H$1)*('2A'!$J$7:$J$9947))
Range("H2").Formula = "=SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$K$7:$K$9947=0)*('2A'!$I$7:$I$9947=H$1)*('2A'!$J$7:$J$9947))"
'=SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$K$7:$K$9947=0)*('2A'!$I$7:$I$9947=I$1)*('2A'!$J$7:$J$9947))
Range("I2").Formula = "=SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$K$7:$K$9947=0)*('2A'!$I$7:$I$9947=I$1)*('2A'!$J$7:$J$9947))"
'=SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$K$7:$K$9947=0)*('2A'!$I$7:$I$9947=J$1)*('2A'!$J$7:$J$9947))
Range("J2").Formula = "=SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$K$7:$K$9947=0)*('2A'!$I$7:$I$9947=J$1)*('2A'!$J$7:$J$9947))"
'=SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$K$7:$K$9947=0)*('2A'!$I$7:$I$9947=K$1)*('2A'!$J$7:$J$9947))
Range("K2").Formula = "=SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$K$7:$K$9947=0)*('2A'!$I$7:$I$9947=K$1)*('2A'!$J$7:$J$9947))"
'=SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$K$7:$K$9947=0)*('2A'!$I$7:$I$9947=L$1)*('2A'!$J$7:$J$9947))
Range("L2").Formula = "=SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$K$7:$K$9947=0)*('2A'!$I$7:$I$9947=L$1)*('2A'!$J$7:$J$9947))"
'=SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$K$7:$K$9947=0)*('2A'!$I$7:$I$9947=M$1)*('2A'!$J$7:$J$9947))
Range("M2").Formula = "=SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$K$7:$K$9947=0)*('2A'!$I$7:$I$9947=M$1)*('2A'!$J$7:$J$9947))"
'=SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$L$7:$L$9947=0)*('2A'!$I$7:$I$9947=N$1)*('2A'!$J$7:$J$9947))
Range("N2").Formula = "=SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$L$7:$L$9947=0)*('2A'!$I$7:$I$9947=N$1)*('2A'!$J$7:$J$9947))"
'=SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$L$7:$L$9947=0)*('2A'!$I$7:$I$9947=O$1)*('2A'!$J$7:$J$9947))
Range("O2").Formula = "=SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$L$7:$L$9947=0)*('2A'!$I$7:$I$9947=O$1)*('2A'!$J$7:$J$9947))"
'=SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$L$7:$L$9947=0)*('2A'!$I$7:$I$9947=P$1)*('2A'!$J$7:$J$9947))
Range("P2").Formula = "=SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$L$7:$L$9947=0)*('2A'!$I$7:$I$9947=P$1)*('2A'!$J$7:$J$9947))"
'=SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$L$7:$L$9947=0)*('2A'!$I$7:$I$9947=Q$1)*('2A'!$J$7:$J$9947))
Range("Q2").Formula = "=SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$L$7:$L$9947=0)*('2A'!$I$7:$I$9947=Q$1)*('2A'!$J$7:$J$9947))"
'=SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$L$7:$L$9947=0)*('2A'!$I$7:$I$9947=R$1)*('2A'!$J$7:$J$9947))
Range("R2").Formula = "=SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$L$7:$L$9947=0)*('2A'!$I$7:$I$9947=R$1)*('2A'!$J$7:$J$9947))"
' =IF(OFFSET($N$1,ROW()-1,MATCH((S$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=S$1*2)*('2A'!$L$7:$L$9947)),0)
Range("S2").Formula = "=IF(OFFSET($N$1,ROW()-1,MATCH((S$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=S$1*2)*('2A'!$L$7:$L$9947)),0)"
'=IF(OFFSET($N$1,ROW()-1,MATCH((T$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=T$1*2)*('2A'!$L$7:$L$9947)),0)
Range("T2").Formula = "=IF(OFFSET($N$1,ROW()-1,MATCH((T$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=T$1*2)*('2A'!$L$7:$L$9947)),0)"
'=IF(OFFSET($N$1,ROW()-1,MATCH((U$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=U$1*2)*('2A'!$L$7:$L$9947)),0)
Range("U2").Formula = "=IF(OFFSET($N$1,ROW()-1,MATCH((U$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=U$1*2)*('2A'!$L$7:$L$9947)),0)"
'=IF(OFFSET($N$1,ROW()-1,MATCH((V$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=V$1*2)*('2A'!$L$7:$L$9947)),0)
Range("V2").Formula = "=IF(OFFSET($N$1,ROW()-1,MATCH((V$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=V$1*2)*('2A'!$L$7:$L$9947)),0)"
'=IF(OFFSET($N$1,ROW()-1,MATCH((W$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=W$1*2)*('2A'!$L$7:$L$9947)),0)
Range("W2").Formula = "=IF(OFFSET($N$1,ROW()-1,MATCH((W$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=W$1*2)*('2A'!$L$7:$L$9947)),0)"
'=IF(OFFSET($N$1,ROW()-1,MATCH((X$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=X$1*2)*('2A'!$L$7:$L$9947)),0)
Range("X2").Formula = "=IF(OFFSET($N$1,ROW()-1,MATCH((X$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=X$1*2)*('2A'!$L$7:$L$9947)),0)"
'=IF(OFFSET($N$1,ROW()-1,MATCH((Y$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=Y$1*2)*('2A'!$L$7:$L$9947)),0)
Range("Y2").Formula = "=IF(OFFSET($N$1,ROW()-1,MATCH((Y$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=Y$1*2)*('2A'!$L$7:$L$9947)),0)"
'=IF(OFFSET($N$1,ROW()-1,MATCH((Z$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=Z$1*2)*('2A'!$L$7:$L$9947)),0)
Range("Z2").Formula = "=IF(OFFSET($N$1,ROW()-1,MATCH((Z$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=Z$1*2)*('2A'!$L$7:$L$9947)),0)"
'=IF(OFFSET($N$1,ROW()-1,MATCH((AA$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=AA$1*2)*('2A'!$L$7:$L$9947)),0)
Range("AA2").Formula = "=IF(OFFSET($N$1,ROW()-1,MATCH((AA$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=AA$1*2)*('2A'!$L$7:$L$9947)),0)"
'=IF(OFFSET($N$1,ROW()-1,MATCH((AB$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=AB$1*2)*('2A'!$L$7:$L$9947)),0)
Range("AB2").Formula = "=IF(OFFSET($N$1,ROW()-1,MATCH((AB$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=AB$1*2)*('2A'!$L$7:$L$9947)),0)"
'=IF(OFFSET($H$1,ROW()-1,MATCH(AC$1,$H$1:$M$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=AC$1)*('2A'!$K$7:$K$9947)),0)
Range("AC2").Formula = "=IF(OFFSET($H$1,ROW()-1,MATCH(AC$1,$H$1:$M$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=AC$1)*('2A'!$K$7:$K$9947)),0)"
'=IF(OFFSET($H$1,ROW()-1,MATCH(AD$1,$H$1:$M$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=AD$1)*('2A'!$K$7:$K$9947)),0)
Range("AD2").Formula = "=IF(OFFSET($H$1,ROW()-1,MATCH(AD$1,$H$1:$M$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=AD$1)*('2A'!$K$7:$K$9947)),0)"
'=IF(OFFSET($H$1,ROW()-1,MATCH(AE$1,$H$1:$M$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=AE$1)*('2A'!$K$7:$K$9947)),0)
Range("AE2").Formula = "=IF(OFFSET($H$1,ROW()-1,MATCH(AE$1,$H$1:$M$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=AE$1)*('2A'!$K$7:$K$9947)),0)"
'=IF(OFFSET($H$1,ROW()-1,MATCH(AF$1,$H$1:$M$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=AF$1)*('2A'!$K$7:$K$9947)),0)
Range("AF2").Formula = "=IF(OFFSET($H$1,ROW()-1,MATCH(AF$1,$H$1:$M$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=AF$1)*('2A'!$K$7:$K$9947)),0)"
'=IF(OFFSET($H$1,ROW()-1,MATCH(AG$1,$H$1:$M$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=AG$1)*('2A'!$K$7:$K$9947)),0)
Range("AG2").Formula = "=IF(OFFSET($H$1,ROW()-1,MATCH(AG$1,$H$1:$M$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=AG$1)*('2A'!$K$7:$K$9947)),0)"
'=G2-SUM(H2:AG2)
Range("AH2").Formula = "=G2-SUM(H2:AG2)"
'=INDEX('2A'!$P$7:$P$5947,MATCH(A2&D2,'2A'!$A$7:$A$5947&'2A'!$C$7:$C$5947,0))
Range("AI2").FormulaArray = "=INDEX('2A'!$P$7:$P$5947,MATCH(A2&D2,'2A'!$A$7:$A$5947&'2A'!$C$7:$C$5947,0))"
End With
End Sub