Remove duplicates within formula and result in a list within one cell....

[mimih23]

Hello,
Hopefully you can help.
I have the following data on individual tabs within a workbook:
Sheet2[TABLE="width: 500"]
<TBODY>[TR]
[TD]Data
[/TD]
[/TR]
[TR]
[TD]4532
[/TD]
[/TR]
[TR]
[TD]6562
[/TD]
[/TR]
[TR]
[TD]4532
[/TD]
[/TR]
[TR]
[TD]9981
[/TD]
[/TR]
[TR]
[TD]1244
[/TD]
[/TR]
</TBODY>[/TABLE]

I desire a formula to pull all of the data within Column 1 and yield a list of the items without duplicates within one cell:
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Sheet Name
[/TD]
[TD]Data
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet2
[/TD]
[TD]4532,6562,9981,1244
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet3
[/TD]
[TD]........
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

Is this possible?

Thanks!!

 

[Jon von der Heyden]

Yes it's possible, with VBA. Add this UDF to a module in your workbook project:

Public Function StringUnique(ByVal rngVals As Range) As String
    Dim objDict As Object
    Dim lngRow As Long, lngCol As Long

    Set objDict = CreateObject("Scripting.Dictionary")

    With objDict
        For lngRow = 1 To rngVals.Rows.Count
            For lngCol = 1 To rngVals.Columns.Count
                If Not .Exists(rngVals(lngRow, lngCol).Value2) Then
                    Call .Add(Key:=rngVals(lngRow, lngCol).Value2, Item:=rngVals(lngRow, lngCol).Value2)
                End If
            Next lngCol
        Next lngRow
        StringUnique = Join$(.keys, ",")
    End With
End Function

And then use as a formula as follows:

=StringUnique(Sheet2!A1:A6)

 

[mimih23]

Thanks!
I must be missing something. When I enter the code in VBA and go to hit RUN, nothing shows. Should I see something listed in the Macro window? Also, will the formula automatically pull the sheet names...Sheet2, Sheet3, etc. Remember, the listing of Sheet names will be found on Sheet 1. The data that is to be populated on Sheet 1 will come from the individual sheets within one specific column. Thanks so much for your help.

 

[Jon von der Heyden]

You need to call it as a formula; e.g:
=StringUnique(Sheet2!A2:A6)

And you might as well hard type the sheet name adjacent to the formula result since you have to point the formula to a specific sheet anyway.