Extract Unique Values from Single Row

[EdwinMark]

Hi,

I need unique values from given dynamic range (B2:F2).

Concatenated values in final cell (G2) should be comma separated.

I would really appreciate if someone could solve it through formula preferably.

However I couldn't able to upload file here, so please use below given dropbox link to download excel file.

Excel File: https://www.dropbox.com/s/y6deupms44okct3/Concatenate Unique Values.xlsx

Thanks
Edwin Mark

 

[EdwinMark]

Hello,

Please check this file : https://www.dropbox.com/s/430epv46p6rm538/Test.xlsm

It is not working for me, even after PC restart.

Regards.

I checked the file saved on my drive and it works fine after re-open. Try to restart the computer maybe and then check if it works?

 

[skorpionkz]

It works for me. I have onl United State as a result as the other 2 columns contains an error

 

[EdwinMark]

I checked it from other PC with Excel 2007, and above file works well for all 4 occurrence of function.

Just one issue... if cell contains only numerical value then function didn't include numerical value. but alpha numerical, numerical with special character works properly.

Can you please include only numerical value into count? And is there any reason why function didn't work properly while accessing from my office pc with Excel 2010?

 

[Aladin Akyurek]

I checked it from other PC with Excel 2007, and above file works well for all 4 occurrence of function.

Just one issue... if cell contains only numerical value then function didn't include numerical value. but alpha numerical, numerical with special character works properly.

Can you please include only numerical value into count? And is there any reason why function didn't work properly while accessing from my office pc with Excel 2010?

The following admits also numeric values, using an often-quoted ACONCAT function...

G2, control+shift+enter, not just enter, and copy down:

=REPLACE(aconcat(IF(TRANSPOSE(FREQUENCY(IF($B2:$F2<>"", 
  MATCH("~"&$B2:$F2,$B2:$F2&"",0)),COLUMN($B2:$F2)-COLUMN($B2)+1)),", "&$B2:$F2,"")),1,2,"")

For this formula to work, you need to install the ACONCAT code, using Alt+F11...


ACONCAT is a function coded in <ACRONYM title="visual basic for applications">VBA</ACRONYM>, which is given below. You need to add this to your workbook using Alt+F11 in order to run the foregoing formula.

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

 

[EdwinMark]

Hello Aladin,

I have implemented your given formula and it worked like a charm.

Thank you very much.

Regards,
Edwin

 

[Aladin Akyurek]

Hello Aladin,

I have implemented your given formula and it worked like a charm.

Thank you very much.

Regards,
Edwin

You are welcome.

 

[skorpionkz]

I see Aladin already gave you formula which works nice, here is my corrected solution.

I changed a bit code, you need to provide there Range from where you want to take Text and then there is logical variable (1/0 or True/False) if you select True then it will give you Unique values only, for formula will return all NON blank values.

Function rngCONC(Target As Range, q As Boolean) As String

Dim temp As Variant
Dim finalOutput As String

finalOutput = Empty

For Each temp In Target
If Not temp = Empty Then
    If q = False Then
        If finalOutput = Empty Then
            finalOutput = temp
        Else
            finalOutput = finalOutput & ", " & temp
        End If
    Else
        If InStr(finalOutput, temp) = 0 Then
            If finalOutput = Empty Then
                finalOutput = temp
            Else
                finalOutput = finalOutput & ", " & temp
            End If
        End If
    End If
End If
Next temp

rngCONC = finalOutput

End Function

you can run (need to be run only once) below code to add Help Description for the formula

Sub DescribeFunction_rngCONC()
   Dim FuncName As String
   Dim FuncDesc As String
   Dim Category As String
   Dim ArgDesc(1 To 2) As String

   FuncName = "rngCONC"
   FuncDesc = "Concatenate text from selected range"
   Category = 7 'Text category
   ArgDesc(1) = "Range that needs to be concatenated"
   ArgDesc(2) = "Is a logical value (False or True): ""False"" to include each text from selected range, ""True"" for UNIQUE values only"

   Application.MacroOptions _
      Macro:=FuncName, _
      Description:=FuncDesc, _
      Category:=Category, _
      ArgumentDescriptions:=ArgDesc
End Sub

 

[Jborg]

Can't think of anything at the moment for X number of unique values. But the best way is to use the bottom one and extract in different cells.

Maybe some expert will suggest further and continue from here on. Would be interesting to follow.

 

[Jborg]

I have great respect for all experts here, from which I have learnt alot, but Aladin amazes me with his formulas. Thanks Aladin

 

[Aladin Akyurek]

I have great respect for all experts here, from which I have learnt alot, but Aladin amazes me with his formulas. Thanks Aladin

Thanks for the kind feedback.