Macro or Solution for Dynamic Filtering and Formatting

rgalik

Hi All, 

I have data that looks like table1 below. The request numbers increase monthly, and the questions more or less repeat themselves, but may change depending on what the end user selects. There are roughly 35 questions per request number. I need to reformat the data to resemble table2 below, and then append each request number to it's mate from a different sheet. All the questions must stack.

I have already solved the other details portion with a formula and then removed the column using the select tool. I am having trouble automating this without manually adding a new 'stack' (See photo below) every time a new request number set is added.

Any ideas on how to accomplish this?

Request Number	Question	Response	Other Details
1234-56789	What is your favorite Color?	Blue
1234-56789	What is the airspeed velocity of a swallow?	European or African?
1234-56789	What is your quest?	To get the grail.
1235-67890	What is your favorite Color?	Green
1235-67890	What is the airspeed velocity of a swallow?	N/A	Laden or Unladen?
1235-67890	What is your quest?	To get the grail.	N/A

I need the data to look like this:

Request Number	What is your favorite Color?	What is the airspeed velocity of a swallow?	What is your quest?
1234-56789	Blue	European or African?	To get the grail.
1235-67890	Green	Laden or Unladed?	To get the grail.

FreeRangeDingo

If Other Details were populated for request number 1234-56789, what would you expect to get?  Would those other details be concatenated? And do you expect N/A to be filtered out?  Are there Other Details that should be filtered out?  Is N/A an acceptable response for a question but not Other Details?