Formula to extract only unique values from horizontal range into a single cell, separated by comma

I have a spreadsheet where users are asked to place a value in their allocated column across many rows.

There are a range of other columns used by an Admin area and one of these is supposed to list only the unique values entered by users in the corresponding row.

EXAMPLE DATA

So, using the above screenshot as an example:

• users are allocated a column to complete (e.g. column K) in which they need to enter a value such as I, R, C etc down a number of rows.
• the Admin area needs to summarise these in another column by indicating all of the unique values for that row, separated by comma (e.g. in Cell I9 they would enter I, R, C).

I've been asked to automate this in some way so that the Admin area doesn't have to manually identify the unique values for each row and enter them in. At present the best I have come up with is the following approach:

• In a later column (Let's say Cell AA9) I am using the UNIQUE function as follows: =UNIQUE(K9:O9,1). This then populates the following cells with each of the unique values in the range, only once. That is, AA9 will have I, AB9 has R, AC9 has C and so on.
• In Cell I9 I use a formula as follows: =K9&", "&L9&", "&M9&", "&N9&", "&O9

This does achieve the desired result (although it does show a zero (0) value for any blank cells, which is annoying), but to my mind there would have to be a more elegant way to achieve this.

Question

Is there a way to have a single formula in Cell I9 that does the job of both formulas I'm using (i.e. it does the job of both what's in AA9 and I9 at present), returning all of the unique values in I9 separated by comma and without the need to use helper columns at AA, AB etc.

To handle the problem of multiple, comma-separated entries in a single cell, try:

=TEXTJOIN(",",TRUE,FILTERXML("<t><s>" &SUBSTITUTE(TEXTJOIN(",",TRUE,K27:N27),",","</s><s>")&"</s></t>","//s[not(preceding-sibling::* = .)]"))

Algorithm

• Join the cells with a comma separator
• Create an XML for each comma-delimited substring
• Use an xPath which only returns the unique nodes
• Join the results of the FILTERXML function with a comma delimiter

There are other solutions if you have a Mac and do not have the FILTERXML function

If necessary, you could SORT the results

Edit for MAC Office 365 solution

=TEXTJOIN(",",TRUE,UNIQUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(",",TRUE,K27:N27),",",REPT(" ",99)),IF(SEQUENCE(99,,0)=0,1,SEQUENCE(99,,0)*99),99))))

Use TEXTJOIN function with your =UNIQUE(K9:O9;1)

=TEXTJOIN(",";TRUE;UNIQUE(K9:O9;1))

Change the ; to , as you use a different regional setting to mine.