Ignore #VALUE! error in SORT function

Question

I have the following columns of data and would like to combine them while deleting any duplicates. I am currently using this formula:

=sort(unique(transpose(split(join(";",A:A)&join(";",B:B),";"))),1,TRUE)

The desired outcome would be:

    A
 1. a
 2. c
 3. d
 4. f
 5. h
 6. i
 7. n
 8. r
 9. s

However, due to the #VALUE! errors, the formula simply returns "#VALUE!".

I would like to ignore the #VALUE! errors, and create the desired list (above), without changing the actual data.

Answer 1

Your existing google-sheets formula can make use of the iferror function.

=sort(unique(transpose(split(join(";",iferror(A:A, ""))&join(";",iferror(B:B, "")),";"))),1,TRUE)