Union Tool - How to stack data by column, instead of row

I need to combine multiple Excel files. All of them have the same format. The rows contain survey questions. Same survey questions in all files. The column contains entity name. When I use the Union tool, it stacks the data in rows, ie. data rows for File A is followed by data rows of File B. So the survey questions are repeated many times But I want the data to be stacked in column by entity name. Please see the attached sample file. Please help to see if you could kindly give me a solution.

Sample.xlsx

Workflow

Accepted answers

caltang

Use the Join tool like so:

For HSCUBS.yxzp

caltang

I suggest also to add some RowIDs to your questions so it's easier to join them as a primary key than the full question itself. Sometimes the questions can be phrased slightly differently or a spelling error or some inconsistent whitespace exists.

Hope this helps @HSCUBS - if it does, kindly leave a like and mark my reply as the accepted solution so that others may benefit as well + to close the thread accordingly.

All comments

caltang

Use the Join tool like so:

For HSCUBS.yxzp

caltang

Hope this helps @HSCUBS - if it does, kindly leave a like and mark my reply as the accepted solution so that others may benefit as well + to close the thread accordingly.

HSCUBS

Thanks for your prompt reply. As I have more than 2 files, I have copied your workflow and used Multiple Join. Thanks again.

Quick Links

This months top contributors

FláviaB 262

JORGE4900 235

caltang 177

DanM 167

IraWatt 157