Need to replace formulas with code

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
876
Office Version
  1. 2019
Platform
  1. Windows
Hello guys,

With the help of a code, I have this result data in sheet B from column A to G in a vertical order. Columns K:BD contain formulas to sort the data horizontally as shown in the image. As the formulas are too lengthy and in thousands of cells, the code takes a lot of time in calculating threads. To reduce the time taken for the macro to get the result, I was hoping somebody willing to help me to write a code to get the result from column A to G to Columns K:BD.
Shared Test.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBD
1COPY THE RESULT AND PASTE TO NEW SHEET WITH PASTE SPECIAL - VALUES
2DateVch TypeVch No.NarrationParticularsDebit NegativeCredit PositiveTotal AmtDateVch TypeVch No.NarrationLedger 1AmtLedger 2AmtLedger 3AmtLedger 4AmtLedger 5AmtLedger 6AmtLedger 7AmtLedger 8AmtLedger 9AmtLedger 10AmtLedger 11AmtLedger 12AmtLedger 13AmtLedger 14AmtLedger 15AmtLedger 16AmtLedger 17AmtLedger 18AmtLedger 19AmtLedger 20AmtLedger 21Amt
302-08-2021Receipt1026ICICI-16380.00-1638002-08-2021Receipt1026ICICI-16380January4823February11720March-163
402-08-2021Receipt1026January4823.00482303-08-2021Receipt1027ICICI-2000January1000January1000
502-08-2021Receipt1026February11720.001172003-08-2021Receipt1028ICICI-2770January2800February-30
602-08-2021Receipt1026March-163.00-16304-08-2021Payment1029ICICI1062Sunday-944Monday-118
703-08-2021Receipt1027ICICI-2000.00-200004-08-2021Receipt1030ICICI-1704Monday984Tuesday720
803-08-2021Receipt1027January1000.00100004-08-2021Payment1031ICICI94572Monday-94612Tuesday40
903-08-2021Receipt1027January1000.00100000
1003-08-2021Receipt1028ICICI-2770.00-27700
1103-08-2021Receipt1028January2800.002800
1203-08-2021Receipt1028February-30.00-30
1304-08-2021Payment1029ICICI1062.001062
1404-08-2021Payment1029Sunday-944.00-944
1504-08-2021Payment1029Monday-118.00-118
1604-08-2021Receipt1030ICICI-1704.00-1704
1704-08-2021Receipt1030Monday984.00984
1804-08-2021Receipt1030Tuesday720.00720
1904-08-2021Payment1031ICICI94572.0094572
2004-08-2021Payment1031Monday-94612.00-94612
2104-08-2021Payment1031Tuesday40.0040
B
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
876
Office Version
  1. 2019
Platform
  1. Windows
Before the code is run, Sheet B is blank like in the image.
Shared Test.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBD
1COPY THE RESULT AND PASTE TO NEW SHEET WITH PASTE SPECIAL - VALUES
2DateVch TypeVch No.NarrationParticularsDebit NegativeCredit PositiveTotal AmtDateVch TypeVch No.NarrationLedger 1AmtLedger 2AmtLedger 3AmtLedger 4AmtLedger 5AmtLedger 6AmtLedger 7AmtLedger 8AmtLedger 9AmtLedger 10AmtLedger 11AmtLedger 12AmtLedger 13AmtLedger 14AmtLedger 15AmtLedger 16AmtLedger 17AmtLedger 18AmtLedger 19AmtLedger 20AmtLedger 21Amt
30  0  0                                        
B
Cell Formulas
RangeFormula
I3I3=IF(A2="","",SUM(F3:G3))
K3K3=IFERROR(INDEX($A$3:$I$2000,MATCH($M3,$C$3:$C$2000,0),1),"")
L3L3=IFERROR(INDEX($A$3:$I$2000,MATCH($M3,$C$3:$C$2000,0),2),"")
M3M3=IFERROR(INDEX($C$3:$C$2000,MATCH(0,COUNTIF($M2:$M$2,($C$3:$C$2000)),0)),"")
N3N3=IF(IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS(N4:$O4)+1)/2)),4),"")="","")
O3O3=IF(M3="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS(O4:$O4)+1)/2)),5),""))&""
P3P3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:P3)+1)/2)),9),""))
Q3Q3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:Q4)+1)/2)),5),""))&""
R3R3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:R3)+1)/2)),9),""))
S3S3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:S4)+1)/2)),5),""))&""
T3T3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:T3)+1)/2)),9),""))
U3U3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:U4)+1)/2)),5),""))&""
V3V3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:V3)+1)/2)),9),""))
W3W3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:W4)+1)/2)),5),""))&""
X3X3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:X3)+1)/2)),9),""))
Y3Y3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:Y4)+1)/2)),5),""))&""
Z3Z3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:Z3)+1)/2)),9),""))
AA3AA3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:AA4)+1)/2)),5),""))&""
AB3AB3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:AB3)+1)/2)),9),""))
AC3AC3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:AC4)+1)/2)),5),""))&""
AD3AD3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:AD3)+1)/2)),9),""))
AE3AE3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:AE4)+1)/2)),5),""))&""
AF3AF3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:AF3)+1)/2)),9),""))
AG3AG3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:AG4)+1)/2)),5),""))&""
AH3AH3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:AH3)+1)/2)),9),""))
AI3AI3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:AI4)+1)/2)),5),""))&""
AJ3AJ3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:AJ3)+1)/2)),9),""))
AK3AK3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:AK4)+1)/2)),5),""))&""
AL3AL3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:AL3)+1)/2)),9),""))
AM3AM3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:AM4)+1)/2)),5),""))&""
AN3AN3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:AN3)+1)/2)),9),""))
AO3AO3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:AO4)+1)/2)),5),""))&""
AP3AP3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:AP3)+1)/2)),9),""))
AQ3AQ3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:AQ4)+1)/2)),5),""))&""
AR3AR3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:AR3)+1)/2)),9),""))
AS3AS3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:AS4)+1)/2)),5),""))&""
AT3AT3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:AT3)+1)/2)),9),""))
AU3AU3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:AU4)+1)/2)),5),""))&""
AV3AV3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:AV3)+1)/2)),9),""))
AW3AW3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:AW4)+1)/2)),5),""))&""
AX3AX3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:AX3)+1)/2)),9),""))
AY3AY3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:AY4)+1)/2)),5),""))&""
AZ3AZ3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:AZ3)+1)/2)),9),""))
BA3BA3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:BA4)+1)/2)),5),""))&""
BB3BB3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:BB3)+1)/2)),9),""))
BC3BC3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O4:BC4)+1)/2)),5),""))&""
BD3BD3=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:BD3)+1)/2)),9),""))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
2,143
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Not really sure what you are asking for. VBA formulas?

VBA Code:
Sub A1_FormulaAlterationsV3()
'
    Dim RowNumber  As Long
    Dim ColumnOffset    As Long, RowOffset  As Long
'
    ColumnOffset = 0
    RowNumber = 3
    RowOffset = 0
'
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset).Formula = "=IFERROR(INDEX($A$3:$I$2000,MATCH($M" & RowNumber & ",$C$3:$C$2000,0),1),"""")"
'
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 1).Formula = "=IFERROR(INDEX($A$3:$I$2000,MATCH($M" & RowNumber & ",$C$3:$C$2000,0),2),"""")"
'
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 2).FormulaArray = "=IFERROR(INDEX($C$3:$C$2000,MATCH(0,COUNTIF($M$2:$M" & RowNumber - 1 & ",($C$3:$C$2000)),0)),"""")"  ' This may need to be fixed ;)
'
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 3).Formula = "=IF(IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS(N" & RowNumber + 1 & ":$O" & RowNumber + 1 & ")+1)/2)),4),"""")="""","""")"
'
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 4).Formula = "=IF(M" & RowNumber & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS(O" & RowNumber + 1 & ":$O" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
'
'
     Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 5).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":P" & RowNumber & ")+1)/2)),9),""""))"
     Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 7).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":R" & RowNumber & ")+1)/2)),9),""""))"
     Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 9).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":T" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 11).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":T" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 13).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":V" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 15).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":X" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 17).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":Z" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 19).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AB" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 21).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AB" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 23).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AD" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 25).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AF" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 27).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AH" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 29).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AJ" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 31).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AL" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 33).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AN" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 35).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AP" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 37).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AR" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 39).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AT" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 41).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AV" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 43).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AX" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 45).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AZ" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 47).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":BB" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 49).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":BD" & RowNumber & ")+1)/2)),9),""""))"
'
'
     Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 6).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":Q" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
     Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 8).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":S" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 10).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":U" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 12).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":W" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 14).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":Y" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 16).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AA" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 18).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AC" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 20).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AE" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 22).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AG" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 24).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AI" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 26).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AK" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 28).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AM" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 30).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AO" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 32).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AQ" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 34).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AS" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 36).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AU" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 38).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AW" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 40).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AY" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 42).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":BA" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 44).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":BC" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
End Sub

You could loop through that code to put formulas across rows by incrementing RowNumber & RowOffset.
 
Last edited:

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
876
Office Version
  1. 2019
Platform
  1. Windows
Not really sure what you are asking for. VBA formulas?

VBA Code:
Sub A1_FormulaAlterationsV3()
'
    Dim RowNumber  As Long
    Dim ColumnOffset    As Long, RowOffset  As Long
'
    ColumnOffset = 0
    RowNumber = 3
    RowOffset = 0
'
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset).Formula = "=IFERROR(INDEX($A$3:$I$2000,MATCH($M" & RowNumber & ",$C$3:$C$2000,0),1),"""")"
'
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 1).Formula = "=IFERROR(INDEX($A$3:$I$2000,MATCH($M" & RowNumber & ",$C$3:$C$2000,0),2),"""")"
'
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 2).FormulaArray = "=IFERROR(INDEX($C$3:$C$2000,MATCH(0,COUNTIF($M$2:$M" & RowNumber - 1 & ",($C$3:$C$2000)),0)),"""")"  ' This may need to be fixed ;)
'
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 3).Formula = "=IF(IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS(N" & RowNumber + 1 & ":$O" & RowNumber + 1 & ")+1)/2)),4),"""")="""","""")"
'
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 4).Formula = "=IF(M" & RowNumber & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS(O" & RowNumber + 1 & ":$O" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
'
'
     Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 5).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":P" & RowNumber & ")+1)/2)),9),""""))"
     Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 7).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":R" & RowNumber & ")+1)/2)),9),""""))"
     Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 9).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":T" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 11).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":T" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 13).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":V" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 15).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":X" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 17).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":Z" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 19).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AB" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 21).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AB" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 23).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AD" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 25).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AF" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 27).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AH" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 29).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AJ" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 31).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AL" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 33).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AN" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 35).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AP" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 37).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AR" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 39).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AT" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 41).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AV" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 43).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AX" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 45).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AZ" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 47).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":BB" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 49).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":BD" & RowNumber & ")+1)/2)),9),""""))"
'
'
     Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 6).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":Q" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
     Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 8).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":S" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 10).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":U" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 12).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":W" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 14).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":Y" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 16).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AA" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 18).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AC" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 20).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AE" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 22).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AG" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 24).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AI" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 26).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AK" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 28).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AM" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 30).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AO" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 32).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AQ" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 34).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AS" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 36).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AU" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 38).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AW" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 40).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AY" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 42).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":BA" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 44).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":BC" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
End Sub

You could loop through that code to put formulas across rows by incrementing RowNumber & RowOffset.
I want to remove the formulas from K:BD and get the result in them with the help of a code.
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
876
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

The data to be sorted is pasted in columns from A3:J3. The cells marked yellow is the expected result after running the code. Column I is the help column with a formula to calculate and get the amount positive or negative. Whenever I enter data in cell A3:J3, it should sort the data horizontally in columns K3:BD3 in different rows depending on the voucher number. One voucher number one row as shown in the image.
Please note: This is a sample data. The range of rows may varry up to 5000 rows max in different scenarios.
Convert formula to code.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBD
1Help Column
2DateVch TypeVch No.NarrationParticularsDebit NegativeCredit PositiveTotal AmtDateVch TypeVch No.NarrationLedger 1AmtLedger 2AmtLedger 3AmtLedger 4AmtLedger 5AmtLedger 6AmtLedger 7AmtLedger 8AmtLedger 9AmtLedger 10AmtLedger 11AmtLedger 12AmtLedger 13AmtLedger 14AmtLedger 15AmtLedger 16AmtLedger 17AmtLedger 18AmtLedger 19AmtLedger 20AmtLedger 21Amt
302-08-2021Receipt1026ICICI-16380.00-1638002-08-2021Receipt1026ICICI-16380January4823February11720March-163
402-08-2021Receipt1026January4823.00482303-08-2021Receipt1027ICICI-2000January1000January1000
502-08-2021Receipt1026February11720.001172003-08-2021Receipt1028ICICI-2770January2800February-30
602-08-2021Receipt1026March-163.00-16304-08-2021Payment1029ICICI1062Sunday-944Monday-118
703-08-2021Receipt1027ICICI-2000.00-200004-08-2021Receipt1030ICICI-1704Monday984Tuesday720
803-08-2021Receipt1027January1000.00100004-08-2021Payment1031ICICI94572Monday-94612Tuesday40
903-08-2021Receipt1027January1000.001000
1003-08-2021Receipt1028ICICI-2770.00-2770
1103-08-2021Receipt1028January2800.002800
1203-08-2021Receipt1028February-30.00-30
1304-08-2021Payment1029ICICI1062.001062
1404-08-2021Payment1029Sunday-944.00-944
1504-08-2021Payment1029Monday-118.00-118
1604-08-2021Receipt1030ICICI-1704.00-1704
1704-08-2021Receipt1030Monday984.00984
1804-08-2021Receipt1030Tuesday720.00720
1904-08-2021Payment1031ICICI94572.0094572
2004-08-2021Payment1031Monday-94612.00-94612
2104-08-2021Payment1031Tuesday40.0040
B
Cell Formulas
RangeFormula
I3:I21I3=IF(A2="","",SUM(F3:G3))
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
876
Office Version
  1. 2019
Platform
  1. Windows
Not really sure what you are asking for. VBA formulas?

VBA Code:
Sub A1_FormulaAlterationsV3()
'
    Dim RowNumber  As Long
    Dim ColumnOffset    As Long, RowOffset  As Long
'
    ColumnOffset = 0
    RowNumber = 3
    RowOffset = 0
'
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset).Formula = "=IFERROR(INDEX($A$3:$I$2000,MATCH($M" & RowNumber & ",$C$3:$C$2000,0),1),"""")"
'
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 1).Formula = "=IFERROR(INDEX($A$3:$I$2000,MATCH($M" & RowNumber & ",$C$3:$C$2000,0),2),"""")"
'
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 2).FormulaArray = "=IFERROR(INDEX($C$3:$C$2000,MATCH(0,COUNTIF($M$2:$M" & RowNumber - 1 & ",($C$3:$C$2000)),0)),"""")"  ' This may need to be fixed ;)
'
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 3).Formula = "=IF(IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS(N" & RowNumber + 1 & ":$O" & RowNumber + 1 & ")+1)/2)),4),"""")="""","""")"
'
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 4).Formula = "=IF(M" & RowNumber & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS(O" & RowNumber + 1 & ":$O" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
'
'
     Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 5).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":P" & RowNumber & ")+1)/2)),9),""""))"
     Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 7).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":R" & RowNumber & ")+1)/2)),9),""""))"
     Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 9).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":T" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 11).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":T" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 13).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":V" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 15).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":X" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 17).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":Z" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 19).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AB" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 21).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AB" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 23).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AD" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 25).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AF" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 27).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AH" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 29).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AJ" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 31).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AL" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 33).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AN" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 35).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AP" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 37).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AR" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 39).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AT" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 41).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AV" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 43).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AX" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 45).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":AZ" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 47).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":BB" & RowNumber & ")+1)/2)),9),""""))"
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 49).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":BD" & RowNumber & ")+1)/2)),9),""""))"
'
'
     Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 6).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":Q" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
     Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 8).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":S" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 10).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":U" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 12).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":W" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 14).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":Y" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 16).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AA" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 18).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AC" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 20).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AE" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 22).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AG" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 24).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AI" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 26).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AK" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 28).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AM" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 30).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AO" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 32).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AQ" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 34).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AS" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 36).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AU" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 38).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AW" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 40).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":AY" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 42).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":BA" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
    Range("K" & RowNumber).Offset(RowOffset, ColumnOffset + 44).Formula = "=IF(M" & RowNumber - 1 & "="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":BC" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
End Sub

You could loop through that code to put formulas across rows by incrementing RowNumber & RowOffset.
JohnnyL. Only the first 3 columns are displaying correct. K L and M.
1. Only one row is filled which is not correct. All the ledger and all the amount rcolumns are displaying FALSE.
2. the code should fill down all vouchers. (1026-1031 in this case) that means 6 rows.
Only one voucher number should be displayed horizontally in one row. The next voucher no should display in the next row and so on. Please check the result in #5.
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
2,143
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

@RAJESH1960 It is not a finished product. Right now we just want to make sure the code for the formulas is correct. That being said, I did notice that I missed some quotation marks in the coding. I fixed that issue and then shortened up the code. Test it and see if any of the formulas are not displaying correctly. Once the formula code is verified, we can proceed. This is the formula code for K3:BD3.

VBA Code:
    Dim ColumnOffset    As Long, RowNumber  As Long
    Dim FormulaLooper   As Long
'
    ColumnOffset = 0
    RowNumber = 3
'
        Range("K" & RowNumber).Offset(, ColumnOffset).Formula = "=IFERROR(INDEX($A$3:$I$2000,MATCH($M" & RowNumber & ",$C$3:$C$2000,0),1),"""")"
'
        Range("K" & RowNumber).Offset(, ColumnOffset + 1).Formula = "=IFERROR(INDEX($A$3:$I$2000,MATCH($M" & RowNumber & ",$C$3:$C$2000,0),2),"""")"
'
        Range("K" & RowNumber).Offset(, ColumnOffset + 2).FormulaArray = "=IFERROR(INDEX($C$3:$C$2000,MATCH(0,COUNTIF($M$2:$M" & RowNumber - 1 & ",($C$3:$C$2000)),0)),"""")"
'
        Range("K" & RowNumber).Offset(, ColumnOffset + 3).Formula = "=IF(IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS(N" & RowNumber + 1 & ":$O" & RowNumber + 1 & ")+1)/2)),4),"""")="""","""")"
'
        Range("K" & RowNumber).Offset(, ColumnOffset + 4).Formula = "=IF(M" & RowNumber & "="""","""",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS(O" & RowNumber + 1 & ":$O" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
'
        For FormulaLooper = 5 To 45 Step 2
            Range("K" & RowNumber).Offset(, ColumnOffset + FormulaLooper).Formula = "=IF(M" & RowNumber - 1 & "="""","""",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":" & Range("K" & RowNumber).Offset(, ColumnOffset + FormulaLooper).Address(0, 0) & ")+1)/2)),9),""""))"
        Next
'
        For FormulaLooper = 6 To 44 Step 2
            Range("K" & RowNumber).Offset(, ColumnOffset + FormulaLooper).Formula = "=IF(M" & RowNumber - 1 & "="""","""",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":" & Range("K" & RowNumber).Offset(1, ColumnOffset + FormulaLooper).Address(0, 0) & ")+1)/2)),5),""""))&"""""
        Next
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
876
Office Version
  1. 2019
Platform
  1. Windows
Yes... You got the first row right. Next, I think it is better to fill down the rows from K:BD depending on the used range in columns A:G. and not the whole 5000 rows.
Count the number of voucher numbers in column C and fill down as many rows in K:BD .....
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
2,143
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Your example shows 6 rows so how about:

VBA Code:
    Dim ColumnOffset    As Long, RowNumber  As Long
    Dim FormulaLooper   As Long
'
    ColumnOffset = 0
    RowNumber = 3
'
    For RowNumber = 3 To RowNumber + 6
        Range("K" & RowNumber).Offset(, ColumnOffset).Formula = "=IFERROR(INDEX($A$3:$I$2000,MATCH($M" & RowNumber & ",$C$3:$C$2000,0),1),"""")"
        Range("K" & RowNumber).Offset(, ColumnOffset + 1).Formula = "=IFERROR(INDEX($A$3:$I$2000,MATCH($M" & RowNumber & ",$C$3:$C$2000,0),2),"""")"
        Range("K" & RowNumber).Offset(, ColumnOffset + 2).FormulaArray = "=IFERROR(INDEX($C$3:$C$2000,MATCH(0,COUNTIF($M$2:$M" & RowNumber - 1 & ",($C$3:$C$2000)),0)),"""")"
        Range("K" & RowNumber).Offset(, ColumnOffset + 3).Formula = "=IF(IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS(N" & RowNumber + 1 & ":$O" & RowNumber + 1 & ")+1)/2)),4),"""")="""","""")"
        Range("K" & RowNumber).Offset(, ColumnOffset + 4).Formula = "=IF(M" & RowNumber & "="""","""",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS(O" & RowNumber + 1 & ":$O" & RowNumber + 1 & ")+1)/2)),5),""""))&"""""
'
'
        For FormulaLooper = 5 To 45 Step 2
            Range("K" & RowNumber).Offset(, ColumnOffset + FormulaLooper).Formula = "=IF(M" & RowNumber - 1 & "="""","""",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber & ":" & Range("K" & RowNumber).Offset(, ColumnOffset + FormulaLooper).Address(0, 0) & ")+1)/2)),9),""""))"
        Next
'
        For FormulaLooper = 6 To 44 Step 2
            Range("K" & RowNumber).Offset(, ColumnOffset + FormulaLooper).Formula = "=IF(M" & RowNumber - 1 & "="""","""",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M" & RowNumber & "),INT((COLUMNS($O" & RowNumber + 1 & ":" & Range("K" & RowNumber).Offset(1, ColumnOffset + FormulaLooper).Address(0, 0) & ")+1)/2)),5),""""))&"""""
        Next
    Next

Let me know if there is any issue with that.
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
876
Office Version
  1. 2019
Platform
  1. Windows
But in different scenarios the rows may varry from 10 or 1000 also...
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,817
Messages
5,772,456
Members
425,760
Latest member
paphon

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