Unique values from 200+ columns

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?

Workflow

Tips and Tricks

Common Use Cases

Batch Macro

Dynamic Processing

Optimization

Data Investigation

Iterative Macro

Accepted answers

All comments

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.

0502-omkarshinde.yxmd

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-whole-table-and-shift-cells-up/td-p/805525 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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280