Unique values from 200+ columns

[omkarshinde]

I have a database containing 200+ columns and I need to find unique values for each column and compile into an excel file columnwise. However adding summarise tool to every column and then unique tool is too manual because i have many such databases too.
Any way I can make a batch or iterative macro to automate this process? 

[Qiu]

@omkarshinde 
Combination of Transpose and Cross Tab is alway good for the processing data of Multiple Columns.

Here is a quick sample and hope it will work for your case.

[omkarshinde]

@Qiu Thanks for this approach however problem with this is manually again renaming all the columns any way we can avoid that?

[Qiu]

@omkarshinde 
Can you give more details about the "Renaming"?

Maybe a sample data would do. 😁

[apathetichell]

transpose the columns you want -> summarize with group by name/value. group by will automatically give you the distinct/unique values.

[omkarshinde]

@Qiu @apathetichell  no problem with renaming actually it worked thanks
however now i have several empty cells in final output and I used https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/How-to-delete-empty-cells-from... to remove them however i am losing out on columns which are entirely empty too which i wish to retain any idea how to do that?

[apathetichell]

Hey - can you share some samples? I'd recommend using summarize in max mode or using a multi-field formula to force convert values. 

[omkarshinde]

@apathetichell @Qiu 

[apathetichell]

please provide an excel before and after