Problem: I am attempting to deal with two lists. In columns A and B, there is one labelled "HYPHEN" and another labelled "CAS Number."
If a hyphen is present in column A, it is placed before the adjacent CAS number, which is then entered below, and so on until all hyphens and CAS numbers are present. Column C utilizes a formula that combines column A and column B and ranks them. To help illustrate this, I've included an image, and the formula to duplicate it is provided below.
To replicate the issue: Columns A and B can have any data entered. To replicate the output of column C the formula is given below:
Formula for Column C: =FILTERXML(""&SUBSTITUTE(TEXTJOIN(",",TRUE,A2:B26),",","")&"","//b")
(Formula provided by @Gary's Student on Stack Overflow)
Can someone please help me with this?