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?

[rgalik]

Hi There, 

I already solved the other details portion using a formula and select tool. I have edited the output above to reflect more accurately what I am receiving. 

Thanks!

[FreeRangeDingo]

Can you upload the workflow?

[rgalik]

Hi, 

Yes, I have attached the workflow here. I created dummy data, as I cannot share the real data with you. There are 35+ actual columns on the 'Additional Information' tab and ultimately there will be 60+ clients who will be on the 'Additional Information' Tab. I would like to be able to run this and have it format the data properly without amending the filter or stacks each time I run it.