How to maintain true Blank Cells when copying a column

user_13562186 0
Viewed 164

Problem: I have data in Column A as headings of some sort and in column B I have subheadings.Because of this column A has blank cells between the headings. If I set Cell A1 in a second sheet to be equal to A1 on the first sheet and copy this throughout the blank cells return as '0'. This messes with formulas that rely on true blank cells.

I've attached and image to visually explain the problem. enter image description here

To replicate the desired output and the problem:

  • Enter random data into columns A and B leaving blanks in column B.

  • In column C enter: =SUBSTITUTE(FILTERXML("<t><s>"&TEXTJOIN("</s><s>'",,A1:B50)&"</s></t>","//s"),"'","")'

  • This Will return a correct result combining the heading and subheadings in a logical order.

.

  • However if you set say column E and F to equal Columns A and B respectively and enter the following in column G: =SUBSTITUTE(FILTERXML("<t><s>"&TEXTJOIN("</s><s>'",,D1:E50)&"</s></t>","//s"),"'","")

  • The data will return correctly sorted but now with a bunch of 0s between each heading and subheading because the formula now includes cells that were originally blank as 0.

What I've Tried: Aesthetically you can set '0' Cells to not display 0 in advanced setting but as the name descries its still a 0 and just hidden but not from the formula. I have tried setting the cell to Text only to try and eliminate 0 but this has no affect as numbers are still recognised as text. I have also tried to eliminate 0 with the code =IF(E1="0","",E1) and =IF(E1="0",NA(), E1). This removes the 0 or adds N/A# but ultimately these cells are still recognised as such by the above formula and a 0 is entered or the formula fails.

Does anyone know how to make one column with blank cells equal to another and still maintain True Blank Cells and not 0s? Alternatively if anyone knows how I can modify my formula to ignore 0s that would be great too. I don't have any experience in VBA but I'm willing to learn/copy and paste a code that will remove this problem.

Any help is appreciated, or if you think its not possible let me know.

office365 excel-formula vba excel arrays
2 Answers

Please try this formula in your cell E2 and copy down.

=LOOKUP(2,1/($A$1:$A2<>""),$A:$A)

The opposite effect is achieved with this formula.

=IF(A2="","",A2)

The first formula will copy the header to each row even where the source is blank whereas the second produces a blank cell for a blank cell. Another ay is to allow the zeros to be written but suppress their display with a cell format or a universal setting (for the whole sheet) that suppresses display of zero values.

user_76754370

Did you mean you would like to use a condition in TEXTJOIN(), a nested IF():

enter image description here

Formula in D1:

=SUBSTITUTE(FILTERXML("<t><s>'"&TEXTJOIN("</s><s>'",,IF(A2:B13<>0,A2:B13,""))&"</s></t>","//s"),"'","")
user_97581940
Related