Hi,
I have a list of variable subjects, and each subject has a list of inputs. Also, I have a summary which consists of some texts and the variable subjects. I need to Join the summary with the first variable subject and replace that variable from the summary with the list of variable inputs. The output of that, needs to be matched with the next variable subject and repeat the same steps. Repeat the process for each variable subject. Attached an example.
The problem is that in the real data I won't have the same number of variable subjects and inputs. In the attached example, there are only three variables, so we had to do the process three times only. In reality however, the number would be different each time, and there is no limit for number of variables. Sometimes it is 27 variable in one run, so I would have to do the process 27 time! Therefore, I need a more dynamic way of doing the process to end up with each input from each variable subject matched with each input from all the other variable subjects, and then replace the variables from the summary with the inputs.
I tried to do it using cross tap and text to column, but it still was not dynamic. I also tried building a macro, but the way I designed the macro requires you to have the macro output (which has the summary text matched and replaced with the variables) as an input of the macro itself to match and replace the next variable subject. Is there a way of doing such a macro, where the output of one iteration is the input of the next iteration? Also, If you have a simpler way of doing the same process whether it requires a macro or not, please let me know. Any help would be highly appreciated.
