MichaelFerguson
New Member
- Joined
- Jul 30, 2019
- Messages
- 3
Hello,
I have a spreadsheet that is concatenating multiple ranges of data to formula a note that can be copied and pasted elsewhere. Unfortunately I can't figure out how to exclude blank cells and instead I'm getting 0's in my final note, like this:
[TABLE="width: 658"]
<tbody>[TR]
[TD="width: 877, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , colspan: 2"]
[TABLE="width: 658"]
<tbody>[TR]
[TD="width: 877, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , colspan: 2"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]$1,664.00 check from ABC Company applied to account 123456 for $256.00, to account 123456 for $190.00, to account 123456 for $351.00, to account 123456 for $166.48, to account 123456 for $166.48, to account 123456 for $249.52, to account 123456 for $166.20, to account 123456 for $118.32, to account for $0.00, to account for $0.00, to account for $0.00, to account for $0.00, to account for $0.00, to account for $0.00, to account for $0.00, to account for $0.00, to account for $0.00, to account for $0.00, to account for $0.00, to account for $0.00 as a prior year contribution[/COLOR]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
All of the segments with blank account numbers and $0.00 amounts are being pulled in from blank cells (sometimes these cells will contain data that I want included). How can I adjust my formula to make this work?
Here is my formula:
=IF(COUNTA(E2)=1,CONCATENATE(Z6,TEXT(A2,"#,##0.00"),AA6,B2,AB6,D2," ",AC6," ",AD6 & TEXT(E2,"#,##0.00"),AE6," ",AF6," ",D3," ",AC6," ",AD6 & TEXT(E3,"#,##0.00"),"",AE6,AF6," ",D4," ",AC6," ",AD6 & TEXT(E4,"#,##0.00"),AE6,AF6," ",D5," ",AC6," ",AD6 & TEXT(E5,"#,##0.00"),AE6," ", AF6," ",D6," ", AC6," ",AD6 & TEXT(E6,"#,##0.00"),AE6," ",AF6," ",D7," ", AC6," ",AD6 & TEXT(E7,"#,##0.00"),AE6," ",AF6," ",D8," ", AC6," ",AD6 & TEXT(E8,"#,##0.00"),AE6," ", AF6," ",D9," ", AC6," ",AD6 & TEXT(E9,"#,##0.00"),AE6," ", AF6," ",D10," ", AC6," ",AD6 & TEXT(E10,"#,##0.00"),AE6," ", AF6," ",D11," ", AC6," ",AD6 & TEXT(E11,"#,##0.00"),AE6," ", AF6," ",D12," ", AC6," ",AD6 & TEXT(E12,"#,##0.00"),AE6," ", AF6," ",D13," ", AC6," ",AD6 & TEXT(E13,"#,##0.00"),AE6," ", AF6," ",D14," ", AC6," ",AD6 & TEXT(E14,"#,##0.00"),AE6," ",AF6," ",D15," ", AC6," ",AD6 & TEXT(E15,"#,##0.00"),AE6," ",AF6," ",D16," ", AC6," ",AD6 & TEXT(E16,"#,##0.00"),AE6," ",AF6," ",D17," ", AC6," ",AD6 & TEXT(E17,"#,##0.00"),AE6," ",AF6," ",D18," ", AC6," ",AD6 & TEXT(E18,"#,##0.00"),AE6," ",AF6," ",D19," ", AC6," ",AD6 & TEXT(E19,"#,##0.00"),AE6," ",AF6," ",D20," ", AC6," ",AD6 & TEXT(E20,"#,##0.00"),AE6," ",AF6," ",D21," ", AC6," ",AD6 & TEXT(E21,"#,##0.00")," "))
Account numbers and amount are entered in D2:21 and E2:21, respectively. Other data is pulled from elsewhere to build the final note, but I have that part figured out. Would appreciate any help. I'm a big novice.
[TABLE="width: 146"]
<tbody>[TR]
[TD="width: 90, bgcolor: #A9D08E"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]Account#[/COLOR]
[/TD]
[TD="width: 106, bgcolor: #A9D08E"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]Amount[/COLOR]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR]
[/TD]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]256.00[/COLOR]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR]
[/TD]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]190.00[/COLOR]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR]
[/TD]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]351.00[/COLOR]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR]
[/TD]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]166.48[/COLOR]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR]
[/TD]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]166.48[/COLOR]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR]
[/TD]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]249.52[/COLOR]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR]
[/TD]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]166.20[/COLOR]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR]
[/TD]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]118.32[/COLOR]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][/TD]
[TD="bgcolor: #00B050"][/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][/TD]
[TD="bgcolor: #00B050"][/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][/TD]
[TD="bgcolor: #00B050"][/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][/TD]
[TD="bgcolor: #00B050"][/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][/TD]
[TD="bgcolor: #00B050"][/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][/TD]
[TD="bgcolor: #00B050"][/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][/TD]
[TD="bgcolor: #00B050"][/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][/TD]
[TD="bgcolor: #00B050"][/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][/TD]
[TD="bgcolor: #00B050"][/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][/TD]
[TD="bgcolor: #00B050"][/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][/TD]
[TD="bgcolor: #00B050"][/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][/TD]
[TD="bgcolor: #00B050"][/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]Total[/COLOR]
[/TD]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]$1,664.00[/COLOR]
[/TD]
[/TR]
</tbody>[/TABLE]
I have a spreadsheet that is concatenating multiple ranges of data to formula a note that can be copied and pasted elsewhere. Unfortunately I can't figure out how to exclude blank cells and instead I'm getting 0's in my final note, like this:
[TABLE="width: 658"]
<tbody>[TR]
[TD="width: 877, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , colspan: 2"]
[TABLE="width: 658"]
<tbody>[TR]
[TD="width: 877, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , colspan: 2"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]$1,664.00 check from ABC Company applied to account 123456 for $256.00, to account 123456 for $190.00, to account 123456 for $351.00, to account 123456 for $166.48, to account 123456 for $166.48, to account 123456 for $249.52, to account 123456 for $166.20, to account 123456 for $118.32, to account for $0.00, to account for $0.00, to account for $0.00, to account for $0.00, to account for $0.00, to account for $0.00, to account for $0.00, to account for $0.00, to account for $0.00, to account for $0.00, to account for $0.00, to account for $0.00 as a prior year contribution[/COLOR]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
All of the segments with blank account numbers and $0.00 amounts are being pulled in from blank cells (sometimes these cells will contain data that I want included). How can I adjust my formula to make this work?
Here is my formula:
=IF(COUNTA(E2)=1,CONCATENATE(Z6,TEXT(A2,"#,##0.00"),AA6,B2,AB6,D2," ",AC6," ",AD6 & TEXT(E2,"#,##0.00"),AE6," ",AF6," ",D3," ",AC6," ",AD6 & TEXT(E3,"#,##0.00"),"",AE6,AF6," ",D4," ",AC6," ",AD6 & TEXT(E4,"#,##0.00"),AE6,AF6," ",D5," ",AC6," ",AD6 & TEXT(E5,"#,##0.00"),AE6," ", AF6," ",D6," ", AC6," ",AD6 & TEXT(E6,"#,##0.00"),AE6," ",AF6," ",D7," ", AC6," ",AD6 & TEXT(E7,"#,##0.00"),AE6," ",AF6," ",D8," ", AC6," ",AD6 & TEXT(E8,"#,##0.00"),AE6," ", AF6," ",D9," ", AC6," ",AD6 & TEXT(E9,"#,##0.00"),AE6," ", AF6," ",D10," ", AC6," ",AD6 & TEXT(E10,"#,##0.00"),AE6," ", AF6," ",D11," ", AC6," ",AD6 & TEXT(E11,"#,##0.00"),AE6," ", AF6," ",D12," ", AC6," ",AD6 & TEXT(E12,"#,##0.00"),AE6," ", AF6," ",D13," ", AC6," ",AD6 & TEXT(E13,"#,##0.00"),AE6," ", AF6," ",D14," ", AC6," ",AD6 & TEXT(E14,"#,##0.00"),AE6," ",AF6," ",D15," ", AC6," ",AD6 & TEXT(E15,"#,##0.00"),AE6," ",AF6," ",D16," ", AC6," ",AD6 & TEXT(E16,"#,##0.00"),AE6," ",AF6," ",D17," ", AC6," ",AD6 & TEXT(E17,"#,##0.00"),AE6," ",AF6," ",D18," ", AC6," ",AD6 & TEXT(E18,"#,##0.00"),AE6," ",AF6," ",D19," ", AC6," ",AD6 & TEXT(E19,"#,##0.00"),AE6," ",AF6," ",D20," ", AC6," ",AD6 & TEXT(E20,"#,##0.00"),AE6," ",AF6," ",D21," ", AC6," ",AD6 & TEXT(E21,"#,##0.00")," "))
Account numbers and amount are entered in D2:21 and E2:21, respectively. Other data is pulled from elsewhere to build the final note, but I have that part figured out. Would appreciate any help. I'm a big novice.
[TABLE="width: 146"]
<tbody>[TR]
[TD="width: 90, bgcolor: #A9D08E"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]Account#[/COLOR]
[/TD]
[TD="width: 106, bgcolor: #A9D08E"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]Amount[/COLOR]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR]
[/TD]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]256.00[/COLOR]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR]
[/TD]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]190.00[/COLOR]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR]
[/TD]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]351.00[/COLOR]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR]
[/TD]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]166.48[/COLOR]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR]
[/TD]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]166.48[/COLOR]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR]
[/TD]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]249.52[/COLOR]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR]
[/TD]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]166.20[/COLOR]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]123456[/COLOR]
[/TD]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]118.32[/COLOR]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][/TD]
[TD="bgcolor: #00B050"][/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][/TD]
[TD="bgcolor: #00B050"][/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][/TD]
[TD="bgcolor: #00B050"][/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][/TD]
[TD="bgcolor: #00B050"][/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][/TD]
[TD="bgcolor: #00B050"][/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][/TD]
[TD="bgcolor: #00B050"][/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][/TD]
[TD="bgcolor: #00B050"][/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][/TD]
[TD="bgcolor: #00B050"][/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][/TD]
[TD="bgcolor: #00B050"][/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][/TD]
[TD="bgcolor: #00B050"][/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][/TD]
[TD="bgcolor: #00B050"][/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][/TD]
[TD="bgcolor: #00B050"][/TD]
[/TR]
[TR]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]Total[/COLOR]
[/TD]
[TD="bgcolor: #00B050"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 000]$1,664.00[/COLOR]
[/TD]
[/TR]
</tbody>[/TABLE]