Extract Unique Values From a Row

[MrSamExcel]

Excel 2013 64 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH][/TH]
[TH]

A​

[/TH]
[TH]

B​

[/TH]
[TH]

C​

[/TH]
[TH]

D​

[/TH]
[TH]

E​

[/TH]
[TH]

F​

[/TH]
[TH]

G​

[/TH]
[TH]

H​

[/TH]
[TH]

I​

[/TH]
[TH]

J​

[/TH]
[TH]

K​

[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

1​

[/TD]
[TD]ID[/TD]
[TD]

FCM​

[/TD]
[TD]

FP​

[/TD]
[TD]

FCLMS​

[/TD]
[TD]

SD​

[/TD]
[TD]

AED​

[/TD]
[TD]

LAD​

[/TD]
[TD]

WAEM​

[/TD]
[TD]

ABP​

[/TD]
[TD="bgcolor: #5B9BD5"]Note Formula[/TD]
[TD="bgcolor: #70AD47"]Script Formula[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

2​

[/TD]
[TD]ABC[/TD]
[TD][/TD]
[TD]

FT014​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #5B9BD5"]FT014[/TD]
[TD="bgcolor: #70AD47"]1[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

3​

[/TD]
[TD]DEF[/TD]
[TD][/TD]
[TD]

FT014​

[/TD]
[TD]

FT015​

[/TD]
[TD][/TD]
[TD]

FT016​

[/TD]
[TD]

FT016​

[/TD]
[TD]

FT016​

[/TD]
[TD]

FT016​

[/TD]
[TD="bgcolor: #5B9BD5"]FT014, FT015, FT016[/TD]
[TD="bgcolor: #70AD47"]1, 2, 3[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

4​

[/TD]
[TD]ZYX[/TD]
[TD]

FT028​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

FT016​

[/TD]
[TD]

FT016​

[/TD]
[TD]

FT016​

[/TD]
[TD]

FT016​

[/TD]
[TD="bgcolor: #5B9BD5"]FT014, FT016[/TD]
[TD="bgcolor: #70AD47"]1, 2[/TD]
[/TR]
</tbody>[/TABLE]
​
In the example above, is there a formula I could place in J2:J4 and K2:K4 to produce the results I've typed manually?
1. Note Formula returns all distinct non-blank values in the range, each separated by a comma and a space

2. Script Formula produces sequence from 1 to n, where n is the number of distinct non-blank values in the range, each separated by a comma and a space
I can get the n using =SUMPRODUCT((B2:I2<>"")/COUNTIF(B2:I2,B2:I2&""))
I could then create nested IF statements -- IF(n=1, "1", IF(n=2, "1, 2", IF(etc.))) -- but is there a more elegant approach I could learn?

 

[JoeMo]

Since you have no replies offering a worksheet formula solution, I'll give you a UDF (user defined function) you can use just like a worksheet formula (see example below).
To install the UDF:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the UDF from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Use the UDF as you would any native Excel function.
7. Make sure you have enabled macros whenever you open the file or the code will not run.

Function ListUniques(R As Range) As Variant
Dim c As Range
With CreateObject("Scripting.dictionary")
    For Each c In R
        If c.Value <> "" Then .Item(c.Value) = .Item(c.Value) + 1
    Next c
    If .Count > 0 Then
        ListUniques = Join(.keys, ", ")
    Else
        ListUniques = CVErr(xlErrNA)
    End If
End With
End Function

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	ID	FCM	FP	FCLMS	SD	AED	LAD	WAEM	ABP	Note Formula
2	ABC		FT014							FT014
3	DEF		FT014	FT015		FT016	FT016	FT016	FT016	FT014, FT015, FT016
4	ZYX	FT028				FT016	FT016	FT016	FT016	FT028, FT016
5										#N/A
Sheet1

 

[István Hirsch]

Try this in cell K2:

=LEFT("1, 2, 3, 4, 5, 6, 7, 8",FIND(M2,"1, 2, 3, 4, 5, 6, 7, 8"))

 

[István Hirsch]

Try this in cell K2:

=LEFT("1, 2, 3, 4, 5, 6, 7, 8",FIND(M2,"1, 2, 3, 4, 5, 6, 7, 8"))

With "M2"

=LEFT("1, 2, 3, 4, 5, 6, 7, 8",FIND(SUMPRODUCT((B2:I2<>"")/COUNTIF(B2:I2,B2:I2&"")),"1, 2, 3, 4, 5, 6, 7, 8"))

 

[MrSamExcel]

=LEFT("1, 2, 3, 4, 5, 6, 7, 8",FIND(SUMPRODUCT((B2:I2<>"")/COUNTIF(B2:I2,B2:I2&"")),"1, 2, 3, 4, 5, 6, 7, 8"))

Very nice, much cleaner than nested IFs...thanks! Any ideas for the Notes Formula?

Since you have no replies offering a worksheet formula solution, I'll give you a UDF (user defined function)

Unfortunately I'm limited to worksheet formulas for this project. It looks like TEXTJOIN function would be perfect, but I don't have Excel 2016 yet so hopefully a similar approach can be applied in Excel 2013 somehow.

 

[István Hirsch]

Try this:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	ID	FCM	FP	FCLMS	SD	AED	LAD	WAEM	ABP	Note Formula	Script Formula
2	ABC		FT014							FT014	1
3	DEF		FT014	FT015		FT016	FT016	FT016	FT016	FT014, FT015, FT016	1, 2, 3
4	ZYX	FT028				FT016	FT016	FT016	FT016	FT028, FT016	1, 2
Sheet1

 

[István Hirsch]

Watch the spaces! At this part of the formula there are TWO spaces between the quotation marks (that the editor reduced to one):

" "&B2&" "&C2&" "&D2&" "&E2&" "&F2&" "&G2&" "&H2&" "&I2&" "

 

[MrSamExcel]

Try this:

Spreadsheet Formulas
Cell Formula
J2 =SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&B2&" "&C2&" "&D2&" "&E2&" "&F2&" "&G2&" "&H2&" "&I2&" ",B2,"*"&B2,1)," "&B2&" "," "),C2,"*"&C2,1)," "&C2&" "," "),D2,"*"&D2,1)," "&D2&" "," "),E2,"*"&E2,1)," "&E2&" "," "),F2,"*"&F2,1)," "&F2&" "," "),G2,"*"&G2,1)," "&G2&" "," "),H2,"*"&H2,1)," "&H2&" "," "),"*"," "))," ",", ")

Watch the spaces! At this part of the formula there are TWO spaces between the quotation marks (that the editor reduced to one):

" "&B2&" "&C2&" "&D2&" "&E2&" "&F2&" "&G2&" "&H2&" "&I2&" "

Worked perfectly, THANKS again. I copied directly from your original post and didn't see any spacing issues. I need to study the formula, can you explain what it's doing to generate the desired result?

 

[István Hirsch]

You are welcome.

The most important idea: substitute the first occurrence of each similar item with, for example, "*item", then substitute the further similar items " item " (space/item/space) with "", that is every second, third etc similar occurrence of the item (if exist) will be deleted, only the "*item" from each different kind will be left (when searching for " item ", "*item" will not be found and substituted). Double spaces between the items are needed, because if a former " item " is deleted, the next one may be left without a leading space and may not be found and deleted if only one space separates the items.

 

[MrSamExcel]

Your explanation was helpful. I don't understand the entire concept enough to recreate/reapply this formula in a different scenario just yet but your notes will help me get there, thanks. And I went back and changed single spaces to double in the section before ,B2,"*"&B2,1)