Extract Unique Values from Single Row

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
I am trying to sort it out, but with no success so far.

If some one will want to try I am pasting table here (from the attached file)


Excel 2010
ABCDEFG
1SrField 1Field 2Field 3Field 4Field 5Final Output
21CarCarTestTestCar, Test
32TestDriveDriveDriveTest, Drive
43DriveDriveTestTestDrive, Test
Sheet2
 
Upvote 0
I made the UDF for this exercise, but for some reason it doesn't want to work for me any ideas what is wrong?

Code just stops at the highlighted line.
Rich (BB code):
Function uMERGE(Target As Range)

Dim myCollection As New Collection
Dim temp As Variant
Dim finOutup() As Variant
Dim x As Integer


On Error Resume Next
For Each temp In Target
    myCollection.Add Item:=temp, Key:=temp
Next temp
On Error GoTo 0


x = 0
For Each temp In myCollection
If Not temp = Empty Then
    If UBound(Filter(finOutup(x), temp)) = 0 Then
        ReDim Preserve finOutup(x)
        finOutup(x) = temp
        x = x + 1
    End If
End If
Next


uMERGE = finOutup


End Function
 
Last edited:
Upvote 0
I change the code to search in string instead of filtering the array. and it works now. But if some one could advice what i did wrong?

to OP:

try this UDF:
Code:
Function uMERGE(Target As Range)

Dim myCollection As New Collection
Dim temp As Variant
Dim finalOutput As String

On Error Resume Next
For Each temp In Target
    myCollection.Add Item:=temp, Key:=temp
Next temp
On Error GoTo 0

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

uMERGE = finalOutput

End Function


Excel 2010
ABCDEFGHI
1SrField 1Field 2Field 3Field 4Field 5Final Output
21CarCarTestTestCar, TestCar, Test
32TestDriveDriveDriveTest, DriveTest, Drive
43DriveDriveTestTestDrive, TestDrive, Test
Sheet2
Cell Formulas
RangeFormula
I2=uMERGE(B2:F2)
 
Upvote 0
Hi skorpionkz,

I am glad that you come up with perfect solution for me.

Thank you very much. It saved my lot of time and resources.

Edwin Mark
 
Last edited:
Upvote 0
Hello,

After saving as both macro enabled format and normal excel format, when I re-open that file UDF didn't work. I can see that code in VB module.. but function is not working.

Can you please help me?

Edwin
 
Upvote 0
If you put this formula in G2 and drag down, it will extract what you want. However it will only work up to 2 originals, as you show in the demo.
You must input with Ctrl+shift+enter:
=INDEX($B2:$F2,SMALL(IF(FREQUENCY(IF($B2:$F2<>"",MATCH($B2:$F2,$B2:$F2,0)),COLUMN($B2:$F2)-COLUMN($B2)+1)>0,TRANSPOSE(COLUMN($B2:$F2)-COLUMN($B2)+1)),1))&", "&INDEX($B2:$F2,SMALL(IF(FREQUENCY(IF($B2:$F2<>"",MATCH($B2:$F2,$B2:$F2,0)),COLUMN($B2:$F2)-COLUMN($B2)+1)>0,TRANSPOSE(COLUMN($B2:$F2)-COLUMN(B$2)+1)),2)).

BUT if you put the below formula and drag to your right and down it will extract all originals (more than 2) BUT in seperate cells

=IFERROR(INDEX($B2:$F2,SMALL(IF(FREQUENCY(IF($B2:$F2<>"",MATCH($B2:$F2,$B2:$F2,0)),COLUMN($B2:$F2)-COLUMN($B2)+1)>0,TRANSPOSE(COLUMN($B2:$F2)-COLUMN($B2)+1)),COLUMNS($G2:G2))),"")
 
Upvote 0
Hello,

After saving as both macro enabled format and normal excel format, when I re-open that file UDF didn't work. I can see that code in VB module.. but function is not working.

Can you please help me?

Edwin

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?
 
Last edited:
Upvote 0
If you put this formula in G2 and drag down, it will extract what you want. However it will only work up to 2 originals, as you show in the demo.
You must input with Ctrl+shift+enter:
=INDEX($B2:$F2,SMALL(IF(FREQUENCY(IF($B2:$F2<>"",MATCH($B2:$F2,$B2:$F2,0)),COLUMN($B2:$F2)-COLUMN($B2)+1)>0,TRANSPOSE(COLUMN($B2:$F2)-COLUMN($B2)+1)),1))&", "&INDEX($B2:$F2,SMALL(IF(FREQUENCY(IF($B2:$F2<>"",MATCH($B2:$F2,$B2:$F2,0)),COLUMN($B2:$F2)-COLUMN($B2)+1)>0,TRANSPOSE(COLUMN($B2:$F2)-COLUMN(B$2)+1)),2)).

BUT if you put the below formula and drag to your right and down it will extract all originals (more than 2) BUT in seperate cells

=IFERROR(INDEX($B2:$F2,SMALL(IF(FREQUENCY(IF($B2:$F2<>"",MATCH($B2:$F2,$B2:$F2,0)),COLUMN($B2:$F2)-COLUMN($B2)+1)>0,TRANSPOSE(COLUMN($B2:$F2)-COLUMN($B2)+1)),COLUMNS($G2:G2))),"")

Nice formula. Is there is a way to modify it so it would work for X number of unique values?
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top