0

I have this formula which works well to combine column A and column B in a specific order:

=SUBSTITUTE(FILTERXML("<t><s>"&TEXTJOIN("</s><s>'",,CHOOSE({1,2},IF(E2:E67="-","",E2:E67),G2:G67))&"</s></t>","//s"),"'","")

This is a variation on: =FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(",",TRUE,A2:B22),",","</b><b>")&"</b></a>","//b").

The problem with the old formula is that is requires the columns where it pulls the values from to be adjacent. The new formula solves this but now unlike the original doesn't filter out blank cells and instead inserts 0.

Additionally I would like to be able to define a character e.g. "-" for the new formula to ignore.

The attached image shows the differences of both formulas. enter image description here

Nick
  • 789
  • 5
  • 22
  • change your if to blank out the `-`: `IF((A3:A61="")+(A3:A61="-"),"",A3:A61),IF((B3:B61="")+(B3:B61="-"),"",B3:B61)` – Scott Craner Feb 10 '21 at 18:24
  • Hi so ive entered: `=SUBSTITUTE(FILTERXML(""&TEXTJOIN("'",,CHOOSE({1,2},IF((E3:E61="")+(E3:E61="-"),"",E3:E61),IF((F3:F61="")+(F3:F61="-"),"",F3:F61)&"","//s"),"'","")` but it just returns and error – Nick Feb 10 '21 at 18:36
  • 2
    `=SUBSTITUTE(FILTERXML(""&TEXTJOIN("'",,CHOOSE({1,2},IF((E3:E61="")+(E3:E61="-"),"",E3:E61),IF((F3:F61="")+(F3:F61="-"),"",F3:F61)))&"","//s"),"'","")` You need to take the time to understand the parts so yo can make the changes you need without coming here day after day asking us to do your work for you. Next time try to make the changes not just report what someone gave you and the next step you need. – Scott Craner Feb 10 '21 at 18:41
  • I know and I do apologise. I just have this project to sort asap and not being familiar with excel its been quite challenging. However I promise this is the last question of this sort and have been learning a lot each time. I really do appreciate your help. – Nick Feb 10 '21 at 18:46
  • 1
    Subtile change but somewhere along the way you lost the first apostrophe in the XML string since I gave you that answer. – JvdV Feb 10 '21 at 19:43
  • Ah okay, thank you both. – Nick Feb 10 '21 at 20:22

0 Answers0