RAJESH1960
Banned for repeated rules violations
- Joined
- Mar 26, 2020
- Messages
- 2,313
- Office Version
- 2019
- Platform
- 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.
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 | |||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | |||
1 | |||||||||||||||||||||
2 | A | B | C | D | E | F | G | H | A | B | C | D | E | Amt | F | Amt | G | ||||
3 | 0 | 0 | 0 | 0 | 0 | 0 | |||||||||||||||
4 | 0 | 0 | 0 | ||||||||||||||||||
5 | |||||||||||||||||||||
6 | |||||||||||||||||||||
7 | |||||||||||||||||||||
8 | |||||||||||||||||||||
9 | |||||||||||||||||||||
Query |
Cell Formulas | ||
---|---|---|
Range | Formula | |
K3:K8 | K3 | =IFERROR(INDEX($A$3:$I$2000,MATCH($M3,$C$3:$C$2000,0),1),"") |
L3:L8 | 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)),"") |
M4,M6:M8 | M4 | =IFERROR(INDEX($C$3:$C$2000,MATCH(0,COUNTIF($M$2:$M3,($C$3:$C$2000)),0)),"") |
N3:N8 | 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:O8 | 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:P8 | 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:Q8 | 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:R8 | 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:S8 | 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),"")) |
I3:I9 | I3 | =IF(A2="","",SUM(F3:G3)) |
Press CTRL+SHIFT+ENTER to enter array formulas. |