# Replace Zero in cell and show blank in amount columns

#### RAJESH1960

##### Well-known Member
Hello guys

When I run the macro, cell M3 & O3 automatically are replaced by data. After I run the macro, and if there are say 10 rows of entries, the 11th and 12th row will display 0 is all the amount columns. I have to manually delete it every time and run the next macro. I need your expert advice & help to remove the zero with the help of editing the formula.

remove 0 with formula within the formula.xlsx
ABCDEFGHIJKLMNOPQRS
1
2ABCDEFGHABCDEAmtFAmtG
30  0 000 0
4      00 0
5
6
7
8
9
Query
Cell Formulas
RangeFormula
K3:K8K3=IFERROR(INDEX(\$A\$3:\$I\$2000,MATCH(\$M3,\$C\$3:\$C\$2000,0),1),"")
L3:L8L3=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)),"")
M4,M6:M8M4=IFERROR(INDEX(\$C\$3:\$C\$2000,MATCH(0,COUNTIF(\$M\$2:\$M3,(\$C\$3:\$C\$2000)),0)),"")
N3:N8N3=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:O8O3=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:P8P3=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:Q8Q3=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:R8R3=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:S8S3=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),""))
I3:I9I3=IF(A2="","",SUM(F3:G3))
Press CTRL+SHIFT+ENTER to enter array formulas.

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### RAJESH1960

##### Well-known Member
I tried adding &"" in the end of the formula. It helped in some columns but not in all the columns. Sharing the file with data to explain you better.
remove 0 with formula within the formula.xlsx
ABCDEFGHIJKLMNOPQRST
1
2ABCDEFGHABCDEAmtFAmtGAmt
301-07-2021Receipt1448ABC-5000.00-500001-07-2021Receipt1448 ABC-5000Round Off1.45Manoj4998.55
401-07-2021Receipt1448Round Off1.451.4502-07-2021Payment1449 ABC8250Rent-15000TDS6750
501-07-2021Receipt1448Manoj4998.554998.5502-07-2021Payment1450 ABC25993Bank Loan-12060Interest Paid-13933
602-07-2021Payment1449ABC8250.008250  0  0
702-07-2021Payment1449Rent-15000.00-15000     0
802-07-2021Payment1449TDS6750.006750
902-07-2021Payment1450ABC25993.0025993
1002-07-2021Payment1450Bank Loan-12060.00-12060
1102-07-2021Payment1450Interest Paid-13933.00-13933
120
Query
Cell Formulas
RangeFormula
K3:K8K3=IFERROR(INDEX(\$A\$3:\$I\$2000,MATCH(\$M3,\$C\$3:\$C\$2000,0),1),"")
L3:L8L3=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)),"")
N3:N8N3=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:O8O3=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:P8P3=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:Q8Q3=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:R8R3=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:S8S3=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:T8T3=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),""))&""
M4:M8M4=IFERROR(INDEX(\$C\$3:\$C\$2000,MATCH(0,COUNTIF(\$M\$2:\$M3,(\$C\$3:\$C\$2000)),0)),"")
I3:I12I3=IF(A2="","",SUM(F3:G3))&""
Press CTRL+SHIFT+ENTER to enter array formulas.

#### RAJESH1960

##### Well-known Member
Problem solved. Adding &"" and dragging the formula to the last row, worked.

Replies
5
Views
109
Replies
25
Views
412
Replies
3
Views
46
Replies
0
Views
107
Replies
3
Views
117

### Forum statistics

1,144,388
Messages
5,724,068
Members
422,532
Latest member
KiwiGrue ### 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.

### Which adblocker are you using?    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

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