Returning Values Based on Referent Cells (Avoiding Redundant Values)

[merlinCLT]

Hi Mr. Excel Gurus,

I am attempting to create a column (K) which reflects the top three motives/values from scores on a personality inventory. Essentially, the logic is to look across the values of each record (i.e., participant) in each motive category (columns A:J) and return the top three motives (e.g., Altruistic, Power, Science) based on the corresponding numerical values recorded for each record. I have gotten far enough along to where I am pulling values using a combination of TEXTJOIN, INDEX, MATCH, and LARGE:
=TEXTJOIN(", ", TRUE,
INDEX($A$1:$J$1, MATCH(LARGE(A2:J2, 1), A2:J2, 0)),
INDEX($A$1:$J$1, MATCH(LARGE(A2:J2, 2), A2:J2, 0)),
INDEX($A$1:$J$1, MATCH(LARGE(A2:J2, 3), A2:J2, 0))
)

This formula returns the top motives where the one with the largest numerical value is listed first.

THE ISSUE: I am running into an issue (see row 4 in the attached Excel document) where Excel is returning a redundant motive because two motives have the same value (see cell K4 in the attached Excel document). I have consulted with ChatGPT to try to reconcile this issue using different formulas and conditions and VBA. I have not been able to successfully mitigate the issue and would value the help from the Mr. Excel community.

Desired OUTCOME: I want the formula to refer to a redundant value only once so that the appropriate motive can be listed in column K. That is, in the attached document, I want cell K4 to return "Altruistic, Tradition, Science" rather than its present value of "Altruistic, Altruistic, Science."

Thanks, in advance, for your support! Also, these data have been randomly generated, so there is no sensitive data here.

-MerlinCLT

 

[Fluff]

What should happen on row 10 where you have 3 equal values for the 3rd place?

 

[merlinCLT]

What should happen on row 10 where you have 3 equal values for the 3rd place?

Good question! It would be fine to have any of the three represented, so maybe the first occurrence (i.e., Hedonism).

 

[Fluff]

Ok, how about

=TEXTJOIN(", ",,TAKE(SORTBY($A$1:$J$1,A2:J2,-1),,3))

 

[merlinCLT]

Ok, how about

=TEXTJOIN(", ",,TAKE(SORTBY($A$1:$J$1,A2:J2,-1),,3))

Wonderful! Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.