Condition Statement for repetitive columns

[hiteshnpwc]

Hello All,

Need your help in solving the below issue, I am trying to write a condition formula where the If column header will be repetitive in the data. If the name appears for 1st time it should pick Q1 and if the same name appears 2nd time it should pick Q2 and same name appears for 3rd time it should pick Q3, Below is the sample data 

Jennifer Aniston	Matt Damon	Brad Pitt	Jennifer Aniston	Matt Damon	Brad Pitt	Jennifer Aniston	Matt Damon
5	5	5	5	5	5	5	5
5	5	5	5	5	5	5	5
5	5	5	5	5	5	5	5
5	5	5	5	5	5	5	5
5	5	5	5	5	4	4	4

Questions - 

Q#	Question Text
Q1	The instructor was effective in presenting content and facilitating class discussions
Q2	The instructor answered my questions completely and provided clear explanations.
Q3	The instructor was knowledgeable about the content.

[StellaBon]

@hiteshnpwc The issue here is that you cannot have the exact same column name repeating in one process. My suggestions:

1) pivot the data (Transform tool) and provide a column for record numbers (Record ID Tool), and each name and their score on a row:  1 | Jennifer Aniston,  2 |Matt Damon,  3 | Brad Pitt,  4 | Jennifer Aniston,  5 | Matt Damon, etc

2) You could slightly change the column names, Jennifer Aniston,  _Jennifer Aniston, JenniferAniston. Kind of lazy but would be fast.

StellaBon

[Pang_Hee_Choy]

@hiteshnpwc when it load the table it will automatically rename with number, when duplicate header found.
so we can use this as advance to pair with question number. 

by using transform tool, use formula tool to split name and last digit. then you can explore the rest.

[emma_Wilson]

If you want to map the names to Q1, Q2, and Q3 depending on their occurrence, you can use a dictionary to track the count for each name, and simply iterate through the names. Here's a concise code snippet:

headers = ["Jennifer Aniston", "Matt Damon", "Brad Pitt", "Jennifer Aniston", "Matt Damon", "Brad Pitt", "Jennifer Aniston", "Matt Damon"]
name_occurrences = {}

for header in headers:
    count = name_occurrences.get(header, 0) + 1
    name_occurrences[header] = count
    question_number = f"Q{count}"
    print(f"For {header}, pick {question_number}")

This code will produce the same output as before, mapping each occurrence of a name to Q1, Q2, or Q3 as needed.