Condition Statement for repetitive columns

Question

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 AnistonMatt DamonBrad PittJennifer AnistonMatt DamonBrad PittJennifer AnistonMatt Damon5555555555555555555555555555555555555444

Questions -

Q#Question TextQ1The instructor was effective in presenting content and facilitating class discussionsQ2The instructor answered my questions completely and provided clear explanations.Q3The instructor was knowledgeable about the content.

Sample data 2 - Course evaluations (1).xlsx

emma_Wilson · Answer

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.

AncientPandaman · Answer

@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.

StellaBon · Answer

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

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

StellaBon