How many possible values exist for each column, and how many columns? You will have to check each row to see if a value exists across all columns:
[Col1] = "P" OR [Col2] = "P" OR [Col3] = "P" OR [Col4] = "P" OR [Col5] = "P"
[Col1] = "M" OR [Col2] = "M" OR [Col3] = "M" OR [Col4] = "M" OR [Col5] = "M"
do this for each possible value across all possible columns. could be tedious if there are many possible values and many possible columns. If it is the five columns above with the values above it is not too burdensome.
Then define new columns 1-5 and check what exists in each row.
IF [Has P] THEN "P"
ELSEIF [Has M] THEN "M"
do one ELSEIF for each possible value.
In the Col2 Sorted through Col5 sorted you need to check if a value has already been used in a previous column.
IF [Col1 Sorted] = "P" THEN
IF [Has M] THEN "M"
ELSEIF [Col1 Sorted] = "M" THEN
IF [Has A] THEN "A"
The key is knowing what order the values should be sorted and checking for that value.
Then use those sorted columns in your output.
Thanks Aaron for replying. I am having multiple different values approx 50. This is not limited to above mentioned number of columns. I am having like more than 10 columns with 50 different values repeated.
You probably don't want to manually check each one then. Maybe you could create a "rank" reference sheet that has the letter, and where it should be ranked, then use Tableau Prep to pivot the columns into rows, join to the rank reference sheet, then sort the data.