Creating a single list with no blanks from multiple columns

I typically use the following formula to create a list with no blanks, when I have a single column of data. Is there a way to do the same thing when you have multiple columns of data (side by side to make it simple) and you want to create a master list that gets rid of the blanks, and keeps them in the order they appear (by column)?

Please Login or Register  to view this content.

For example, if I had data (with some blank cells randomly placed) in columns AM and AN, and I wanted a singe master list in one column of all non-blank cells in column AL, followed by column AM, etc.? Is there a formula to do this, or must I resort to a macro? Thanks!

Originally Posted by oeldere

You can solve this by sorting the data.

data => sort

Thanks, but I don't think that will work for my situation. I have dynamic lists that change, and so I need it to sort it on the fly, automatically, whenever the lists change. It will become a validation list, which is the ultimate goal. Sorry for lack of details in OP.

I decided to just make one VERY long column (nearly 3000 rows). I don't know why I didn't think of that before. Thanks again.