Need formula to combine values in two columns, remove empty cells, & alphabetize.

I need a formula that will combine two columns, remove the empty cells, and alphabetize the combined results. I've got this formula that got me partially there, but I'm struggling with the removing of empty cells & the alphabetizing part. Eventually, I will need to remove the duplicate entries in the list, but I imagine that will be a second step/helper column.

I try my best, but I'm a bit of a hack, so I may be complicating things.

Also, don't want to use the filter thing because the list is constantly changing & want list to dynamically update without user input.

Any help would be greatly appreciated.

Formula:

Please Login or Register  to view this content.

Vendors.jpg

Try this ...

=IFERROR(INDEX($I:$I, SMALL(IF($I$5:$I$46<>"", ROW($I$5:$I$46)), ROWS($K$5:K5))),
IFERROR(INDEX($J:$J, SMALL(IF($J$5:$J$46<>"", ROW($J$5:$J$46)),ROWS($K$5:K5)-COUNTA($I$5:$I$46))),""))

Enter with Ctrl+Shift+Enter.

Glenn Kennedy, I could kiss you! That worked beautifully.

If I was in Ireland, I'd buy you a pint, but I'm in Maine, so that will have to wait....(the pint buying part, not the kissing)

Thanks so much for your help. Adding to reputation as soon as I'm done with this post.