Replace Zero in cell and show blank in amount columns

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
685
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Mar 26, 2020
Messages
685
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Mar 26, 2020
Messages
685
Office Version
  1. 2019
Platform
  1. Windows
Problem solved. Adding &"" and dragging the formula to the last row, worked.
 

Forum statistics

Threads
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.
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