0

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

enter image description here

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.

Improve this question

2 Answers 2

Reset to default
2

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

enter image description here

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))))
Improve this answer
5
  • Very good answer +1.
    – Reddy Lutonadio
    Commented Sep 6, 2021 at 16:28
  • @ReddyLutonadio Please undelete your original answer. It still solves the problem except for instances of multiple values in a single cell. I feel it would definitely be useful for others and would like to give it an upvote.
    – Monomeeth
    Commented Sep 6, 2021 at 21:10
  • Thanks Ron, this does the trick. :) If at all possible, could you shed light on what a Mac solution would be? We also use Macs (although for this context we're using Windows PCs), but to be thorough it would make your answer even better and apply to more users. Thanks again!
    – Monomeeth
    Commented Sep 6, 2021 at 21:15
  • @Monomeeth MAC sol'n posted, but it does require Office 365
    – Ron Rosenfeld
    Commented Sep 7, 2021 at 0:38
  • Thanks Ron, that's awesome. Appreciate you taking the time to come back and add the edit. :)
    – Monomeeth
    Commented Sep 7, 2021 at 0:39
1

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. enter image description here

Improve this answer
6
  • Thanks for taking the time to reply. I did have a play with using TEXTJOIN earlier but couldn't work out how to have the end result only showing each unique value once? Likewise with your example. In both cases I can't work out why, but, if you can show me how to correct that, then that'd be exactly what I need. Just to clarify my meaning, referring back to the screenshot in my question, the end result in I9 should be I, R, C, not I, I, R, C, I.
    – Monomeeth
    Commented Sep 6, 2021 at 8:01
  • @Monomeeth The values I,I,R,C,I as from the range K9 to O9 not the final result. The final result is on I9: I,R,C. Look closely at the screenshot.
    – Reddy Lutonadio
    Commented Sep 6, 2021 at 8:09
  • I think I just worked out what the problem is. In some cases users enter multiple values in the one cell. So, looking at my screenshot again, and specifically at row 11, the formula returns l,C,R,I,C, but now I see that that's because cell K11 has I,C entered in it, so the formula is working, it's just unfortunate in this scenario that it's seeing I,C in Cell K11 as a different value unique from the I in cells M11 and O11 and from the C in N11. So, all this time I thought I was going crazy, but it wasn't until your last comment that I realised what was going on! Thanks! :)
    – Monomeeth
    Commented Sep 6, 2021 at 8:22
  • I don't suppose you can think of any way to fix that situation?
    – Monomeeth
    Commented Sep 6, 2021 at 8:23
  • @Monomeeth Do the users enter at most 2 values in one cell like in K11 with I,C?
    – Reddy Lutonadio
    Commented Sep 6, 2021 at 9:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .