Consolidate data from several columns to one

Hi clever people,

My issue is as old as few years now, yet still can't find a solution for it, so gave up searching and do the task manually. And the task is:

Assume you have 4 columns of data. First column is for products A,B,C,D etc. The next three columns may or may not have subproducts in them i.e. A1,B1,C1,C2,C3 etc.

The question arises then, how do I consolidate all products and subproducts in one column, which according to an example above would give me a result like this: A,A1,B,B1,C,C1,C2,C3,D.

The solution can be either formula or a piece of a code, does not really matter!

Thanks in advance!

P.S. dummy attached...

Hi guys and thanks for a prompt response.

PFDave, your solution works perfectly on a dummy, but... I forgot to mention that there is a gap in between products and subproducts. Products are in column A and subproducts are in columns W,X,Y. And I find difficult to adapt your code to make code work...

Special-K - I'm testing yours now.

Special-K, good approach. I have inserted a few columns between products and subproducts, code did work, but dumps all the blanks on the list too. On the actual project those will be not blanks, but other data in between columns A & W,X,Y.

Sorry, if I haven't described the issue clearly. Let me try again.

The data range I'm working with is A1:Y1000.
Top row is for headers.
There will be blanks in the range except column A.
There will be text and numeric values.
The data to be extracted is from columns A,W,X,Y.

I believe I have covered all now.

Bump!!!!!!!!!!!!!!!!!!!!

New sheet please...

Hallelujah!!!

Worked on your dummy, transferred the code onto the actual spreadsheet - works like a charm! Very neat piece of code, thanks a lot!