Need to replace formulas with code

RAJESH1960 · Jan 5, 2022

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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

AT

AU

AV

AW

AX

AY

AZ

BA

BB

BC

BD

1

COPY THE RESULT AND PASTE TO NEW SHEET WITH PASTE SPECIAL - VALUES

2

Date

Vch Type

Vch No.

Narration

Particulars

Debit Negative

Credit Positive

Total Amt

Date

Vch Type

Vch No.

Narration

Ledger 1

Amt

Ledger 2

Amt

Ledger 3

Amt

Ledger 4

Amt

Ledger 5

Amt

Ledger 6

Amt

Ledger 7

Amt

Ledger 8

Amt

Ledger 9

Amt

Ledger 10

Amt

Ledger 11

Amt

Ledger 12

Amt

Ledger 13

Amt

Ledger 14

Amt

Ledger 15

Amt

Ledger 16

Amt

Ledger 17

Amt

Ledger 18

Amt

Ledger 19

Amt

Ledger 20

Amt

Ledger 21

Amt

3

02-08-2021

Receipt

1026

ICICI

-16380.00

-16380

02-08-2021

Receipt

1026

ICICI

-16380

January

4823

February

11720

March

-163

4

02-08-2021

Receipt

1026

January

4823.00

4823

03-08-2021

Receipt

1027

ICICI

-2000

January

1000

January

1000

5

02-08-2021

Receipt

1026

February

11720.00

11720

03-08-2021

Receipt

1028

ICICI

-2770

January

2800

February

-30

6

02-08-2021

Receipt

1026

March

-163.00

-163

04-08-2021

Payment

1029

ICICI

1062

Sunday

-944

Monday

-118

7

03-08-2021

Receipt

1027

ICICI

-2000.00

-2000

04-08-2021

Receipt

1030

ICICI

-1704

Monday

984

Tuesday

720

8

03-08-2021

Receipt

1027

January

1000.00

1000

04-08-2021

Payment

1031

ICICI

94572

Monday

-94612

Tuesday

40

9

03-08-2021

Receipt

1027

January

1000.00

1000

0

10

03-08-2021

Receipt

1028

ICICI

-2770.00

-2770

0

11

03-08-2021

Receipt

1028

January

2800.00

2800

12

03-08-2021

Receipt

1028

February

-30.00

-30

13

04-08-2021

Payment

1029

ICICI

1062.00

1062

14

04-08-2021

Payment

1029

Sunday

-944.00

-944

15

04-08-2021

Payment

1029

Monday

-118.00

-118

16

04-08-2021

Receipt

1030

ICICI

-1704.00

-1704

17

04-08-2021

Receipt

1030

Monday

984.00

984

18

04-08-2021

Receipt

1030

Tuesday

720.00

720

19

04-08-2021

Payment

1031

ICICI

94572.00

94572

20

04-08-2021

Payment

1031

Monday

-94612.00

-94612

21

04-08-2021

Payment

1031

Tuesday

40.00

40

B

RAJESH1960 · Jan 5, 2022

Before the code is run, Sheet B is blank like in the image.

Shared Test.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

AT

AU

AV

AW

AX

AY

AZ

BA

BB

BC

BD

1

COPY THE RESULT AND PASTE TO NEW SHEET WITH PASTE SPECIAL - VALUES

2

Date

Vch Type

Vch No.

Narration

Particulars

Debit Negative

Credit Positive

Total Amt

Date

Vch Type

Vch No.

Narration

Ledger 1

Amt

Ledger 2

Amt

Ledger 3

Amt

Ledger 4

Amt

Ledger 5

Amt

Ledger 6

Amt

Ledger 7

Amt

Ledger 8

Amt

Ledger 9

Amt

Ledger 10

Amt

Ledger 11

Amt

Ledger 12

Amt

Ledger 13

Amt

Ledger 14

Amt

Ledger 15

Amt

Ledger 16

Amt

Ledger 17

Amt

Ledger 18

Amt

Ledger 19

Amt

Ledger 20

Amt

Ledger 21

Amt

3

0

B

Cell Formulas
Range	Formula
I3	I3	=IF(A2="","",SUM(F3:G3))
K3	K3	=IFERROR(INDEX($A$3:$I$2000,MATCH($M3,$C$3:$C$2000,0),1),"")
L3	L3	=IFERROR(INDEX($A$3:$I$2000,MATCH($M3,$C$3:$C$2000,0),2),"")
M3	M3	=IFERROR(INDEX($C$3:$C$2000,MATCH(0,COUNTIF($M2:$M$2,($C$3:$C$2000)),0)),"")
N3	N3	=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),"")="","")
O3	O3	=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),""))&""
P3	P3	=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),""))
Q3	Q3	=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),""))&""
R3	R3	=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),""))
S3	S3	=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),""))&""
T3	T3	=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),""))
U3	U3	=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),""))&""
V3	V3	=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),""))
W3	W3	=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),""))&""
X3	X3	=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),""))
Y3	Y3	=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),""))&""
Z3	Z3	=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),""))
AA3	AA3	=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),""))&""
AB3	AB3	=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),""))
AC3	AC3	=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),""))&""
AD3	AD3	=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),""))
AE3	AE3	=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),""))&""
AF3	AF3	=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),""))
AG3	AG3	=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),""))&""
AH3	AH3	=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),""))
AI3	AI3	=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),""))&""
AJ3	AJ3	=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),""))
AK3	AK3	=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),""))&""
AL3	AL3	=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),""))
AM3	AM3	=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),""))&""
AN3	AN3	=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),""))
AO3	AO3	=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),""))&""
AP3	AP3	=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),""))
AQ3	AQ3	=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),""))&""
AR3	AR3	=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),""))
AS3	AS3	=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),""))&""
AT3	AT3	=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),""))
AU3	AU3	=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),""))&""
AV3	AV3	=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),""))
AW3	AW3	=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),""))&""
AX3	AX3	=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),""))
AY3	AY3	=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),""))&""
AZ3	AZ3	=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),""))
BA3	BA3	=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),""))&""
BB3	BB3	=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),""))
BC3	BC3	=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),""))&""
BD3	BD3	=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 · Jan 5, 2022

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.

RAJESH1960 · Jan 6, 2022

johnnyL said:

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 · Jan 6, 2022

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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

AT

AU

AV

AW

AX

AY

AZ

BA

BB

BC

BD

1

Help Column

2

Date

Vch Type

Vch No.

Narration

Particulars

Debit Negative

Credit Positive

Total Amt

Date

Vch Type

Vch No.

Narration

Ledger 1

Amt

Ledger 2

Amt

Ledger 3

Amt

Ledger 4

Amt

Ledger 5

Amt

Ledger 6

Amt

Ledger 7

Amt

Ledger 8

Amt

Ledger 9

Amt

Ledger 10

Amt

Ledger 11

Amt

Ledger 12

Amt

Ledger 13

Amt

Ledger 14

Amt

Ledger 15

Amt

Ledger 16

Amt

Ledger 17

Amt

Ledger 18

Amt

Ledger 19

Amt

Ledger 20

Amt

Ledger 21

Amt

3

02-08-2021

Receipt

1026

ICICI

-16380.00

-16380

02-08-2021

Receipt

1026

ICICI

-16380

January

4823

February

11720

March

-163

4

02-08-2021

Receipt

1026

January

4823.00

4823

03-08-2021

Receipt

1027

ICICI

-2000

January

1000

January

1000

5

02-08-2021

Receipt

1026

February

11720.00

11720

03-08-2021

Receipt

1028

ICICI

-2770

January

2800

February

-30

6

02-08-2021

Receipt

1026

March

-163.00

-163

04-08-2021

Payment

1029

ICICI

1062

Sunday

-944

Monday

-118

7

03-08-2021

Receipt

1027

ICICI

-2000.00

-2000

04-08-2021

Receipt

1030

ICICI

-1704

Monday

984

Tuesday

720

8

03-08-2021

Receipt

1027

January

1000.00

1000

04-08-2021

Payment

1031

ICICI

94572

Monday

-94612

Tuesday

40

9

03-08-2021

Receipt

1027

January

1000.00

1000

10

03-08-2021

Receipt

1028

ICICI

-2770.00

-2770

11

03-08-2021

Receipt

1028

January

2800.00

2800

12

03-08-2021

Receipt

1028

February

-30.00

-30

13

04-08-2021

Payment

1029

ICICI

1062.00

1062

14

04-08-2021

Payment

1029

Sunday

-944.00

-944

15

04-08-2021

Payment

1029

Monday

-118.00

-118

16

04-08-2021

Receipt

1030

ICICI

-1704.00

-1704

17

04-08-2021

Receipt

1030

Monday

984.00

984

18

04-08-2021

Receipt

1030

Tuesday

720.00

720

19

04-08-2021

Payment

1031

ICICI

94572.00

94572

20

04-08-2021

Payment

1031

Monday

-94612.00

-94612

21

04-08-2021

Payment

1031

Tuesday

40.00

40

B

Cell Formulas
Range	Formula
I3:I21	I3	=IF(A2="","",SUM(F3:G3))

RAJESH1960 · Jan 6, 2022

johnnyL said:

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 · Jan 6, 2022

@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 · Jan 7, 2022

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 · Jan 7, 2022

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 · Jan 7, 2022

But in different scenarios the rows may varry from 10 or 1000 also...

Need to replace formulas with code

RAJESH1960

Banned for repeated rules violations

Excel Facts

RAJESH1960

Banned for repeated rules violations

johnnyL

Well-known Member

RAJESH1960

Banned for repeated rules violations

RAJESH1960

Banned for repeated rules violations

RAJESH1960

Banned for repeated rules violations

johnnyL

Well-known Member

RAJESH1960

Banned for repeated rules violations

johnnyL

Well-known Member

RAJESH1960

Banned for repeated rules violations

Similar threads

Forum statistics

Share this page

Need to replace formulas with code

Banned for repeated rules violations

Excel Facts

Banned for repeated rules violations

Well-known Member

Banned for repeated rules violations

Banned for repeated rules violations

Banned for repeated rules violations

Well-known Member

Banned for repeated rules violations

Well-known Member

Banned for repeated rules violations

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock