Unique Column Headers

[skotian1289]

Hi All,

I know this could be a simple solution but I need help as I am quite new to Alteryx.

My input file has 100s of column headers and some column headers are repeated multiple times. I want to give a unique numbers to each column headers because I want to reconcile them. In excel I am able to perform that step by using the below formula.

A2&"/"&COUNTIF(A$2:A2,A2)          (A2 value being 1)

See sample of what I am trying to achieve in alteryx, let me know if anyone can help me

Input File Column headers:

Blue     Green    Red   Blue    Red   Blue   Yellow    Green

Expected Resuts:

Blue/1     Green/1    Red/1    Blue/2    Red/2    Blue/3    Yellow/1    Green/2

Thanks

[vizAlter]

@skotian1289 — That's should not be the case! Can you attach your file which you tried on?

[skotian1289]

@vizAlter  - See attached sample Input file.

For Example if my Column Header name is "User 1" or   "User 3 Red" the Regex is changing It to "User/1" and "User 3 Red"      Instead of "User 1/1"  or "User 3 Red/1" .

I am sure there is some tweak in the formula which could solve it but not very familiar with Regex hence I need assistance. Rest when there is no numbers involved in column headers the formula is successfully recognizing the duplicate values and assigning numbers next to it. Like if column name "User Name" is repeated thrice then formula will change them to "User Name/1"   "User Name/2"  and "User Name/3" .

[skotian1289]

@atcodedog05  - Your solution is adding "/1" to column names successfully but can we recognize the duplicate names and add numbers accordingly without regex. I am able to do that in excel using the formula     A3&"/"&COUNTIF(A$2:A3,A3) where column A is list of column header names of my input and A$2 is 1.

Sample input for reference

Thanks

[atcodedog05]

Hi @skotian1289 

My workflow is working on grouping so if you give

10 columns with name Blue

It will start with Blue/1 successively adding it till 10 on every encountered duplicate column name.

[atcodedog05]

Hi @skotian1289 ,

I think the above scenario might cause issue in mine too since i am cleaning all numbers let me see how fix it.

[atcodedog05]

Hi @skotian1289 

I need some clarification in the below columns from the file

Which of them are duplicate

User Blue 1
User 2 Red
User Amber 3
Green
Purple 5
User 2 Red2
Purple 6
User Blue 1_2 (according to the input tool read only this is duplicate)

Can you confirm on this so that i can build the logic

[atcodedog05 · Accepted solution]

Hi @skotian1289 

Just got an idea and here my solution.

While reading the data i set this option

Input taken

After this it was a piece of cake. By using group by. Here you can clearly see you duplicate column names getting renamed according to the sequence.

This should always work.

Hope this helps : )

If this helps please mark post as solution.

[atcodedog05]

Hi @skotian1289 ,

Thank you for this task man. Really learnt a lot from this.

[skotian1289]

Alteryx automatically changes the name for duplicate columns hencec the confusion.

User Blue 1      (This is appearing twice, last column is the dupe)
User 2 Red       (This is appearing twice)
User Amber 3   (Appearing Only Once)
Green               (Appearing only once)
Purple 5            (Appearing twice)
User 2 Red2      (This is User 2 Red  but alteryx changed the name)
Purple 6            (This is Purple 5  but alteryx auto changed the name)
User Blue 1_2 (according to the input tool read only this is duplicate)

[atcodedog05]

Hi @skotian1289 

Refer to my latest solution. Most of the thing is clear now.