Find unique values in each field

[akumarbvp]

I am new to Alteryx, and would need to find  unique values in each column within a spreadsheet. I can do a individual unique over each column but my spreadsheet has 70 odd columns and was wondering if there is way to iterate through columns and do unique over each column. 

Please also suggest if there is any other suitable tool to achieve this.

[MarqueeCrew]

If you transpose the data first, you will get name value pairs. Only select the fields that your interested in when you select the transpose tool. Then you’ll use the unique tool an select both the name and value fields. Your output will be a list of your unique values.

Cheers,

Mark

[akumarbvp]

@MarqueeCrew 

Thanks for prompt response, maybe since I am new to this tool so could not follow your suggestion.

I have the following data

Field1	Field2	Field3	Field4	Field5	Field6	Field7	Field8	Field9
a	a	a	a	a	a	a	a	a
b	b	b	b	b	b	b	b	b
g	g	g	g	g	g	g	g	g
j	j	j	j	j	j	j	j	j
a	b	g	j	a	a	a	a	a
a	b	g	j	a	a	a	a	a
a	b	g	j	a	a	a	a	a

and what I intend to achieve is  following . Spreadsheet I am using has 200k rows.

Field1 Field2 Field3 Field4 Field5 Field6 Field7 Field8 Field9
a a a a a a a a a
b b b b b b b b b
g g g g g g g g g
j j j j j j j j j

I tried your suggestion with following workflow 

and I am getting following result , maybe I did not follow you.

Field1 a
Field1 b
Field1 g
Field1 j
Field2 a
Field2 b
Field2 g
Field2 j
Field3 a
Field3 b
Field3 g
Field3 j
Field4 a
Field4 b
Field4 g
Field4 j
Field5 a
Field5 b
Field5 g
Field5 j
Field6 a
Field6 b
Field6 g
Field6 j
Field7 a
Field7 b
Field7 g
Field7 j
Field8 a
Field8 b
Field8 g
Field8 j
Field9 a
Field9 b
Field9 g
Field9 j

[MarqueeCrew · Accepted solution]

To convert back to rows:

Cross Tab with name as header and value as value.

I think that it will produce what you’re asking for? In what was produced originally it is a list of unique values by field. If you use a key, then you can produce unique values by that key.

[MatanGee]

Hi

Thanks for the help, but becuase i have diffrent number of items in each coulmn i get a lot of empty cells after using cross tab.

is there anyway around it.

to pick up from the example above:

col1 a

col2 1

col3 dad

col1 b

col2 2

col 1 c

thanks

into

col1  col2  col3  

a       1      dad

b       2      

c

[nishanttayal]

I was also encountering many blanks and eventually identified the better solution to get this result :

Step1: After transpose and removing the duplicates add formula tool to add dummy integer column with value 1.

Step2: Calculate the running total of dummy column grouping by Name column of transpose result.

Step3: Now use Crosstab and group data by Running total output. 

Here you go.