Count Duplicate Texts..

[MrRajKumar]

Hi, I have the following data.

<TABLE style="WIDTH: 220pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=293 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 172pt; mso-width-source: userset; mso-width-alt: 8374" width=229><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>A</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 172pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=229>B</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>SL.No</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Customer</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>1</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABC-Traders</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>2</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Amana-Traders</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>3</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABC-Sales, KP-Traders, JP-Traders</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>4</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Amana-Fashion, JP-Traders</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>5</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Modern-Traders</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>6</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">JP-Traders, ABC-Traders</TD></TR></TBODY></TABLE>


I want to count how many duplicate entries are in Col B. In some cells in Col B contains more than one customers. All customers are seperated by comma.

My expecting answer is = 5 [ ABC-Traders, JP-Traders ]

Any Formula?

Thank you
Raj

 

[Banker1]

If cells with multiple customers are separated by a comma why not use the text to columns function to deliminate the cells and then a countif?

 

[MrRajKumar]

If cells with multiple customers are separated by a comma why not use the text to columns function to deliminate the cells and then a countif?

Thank you for your reply. I have more than 10,000 rows. Is any formula for this?

Thank you
Raj

 

[Banker1]

I can't think of one, someone else might have something more elaborate which will work. VBA would be an option as well...

 

[xenou]

Try this, see if it helps. It does require that there be no other commas in the customer names except the commas that separate one customer from another. It will create a data grid in a two-column format that you can use for a pivot table or other formulas to count duplicates etc.

Sub CreateSLNoGrid2()
Dim wsSource As Worksheet, wsDest As Worksheet
Dim i As Long, j As Long
Dim intCount As Long
Dim SLno As String
Dim a() As Variant
Dim b

Set wsSource = ActiveSheet
Set wsDest = Worksheets.Add(After:=wsSource)

With wsSource
    
    For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
        SLno = .Cells(i, 1).Value
        
        'Capture customer values and pair with SL.No
        'SL.No and Customer are stored in a "two-column" array
        'There must be commas separating the values, and no other commas in the customer names
        b = Split(.Cells(i, 2).Value, ",")
        For j = 0 To UBound(b)
            intCount = intCount + 1
            ReDim Preserve a(1 To 2, 1 To intCount)
            a(1, intCount) = SLno: a(2, intCount) = b(j)
        Next j
    
    Next i

End With

'Write paired SL.No and Customers to a worksheet as a two-column table
wsDest.Cells(1, 1).Value = "SL.No": wsDest.Cells(1, 2).Value = "Customer"
wsDest.Cells(2, 1).Resize(UBound(a, 2), UBound(a, 1)).Value = WorksheetFunction.Transpose(a)
wsDest.Columns(1).AutoFit
wsDest.Columns(2).AutoFit

End Sub

Result:

20080621_RajKumarSort.xls
	A	B	C	D
1	SL.No	Customer
2	1	ABC-Traders
3	2	Amana-Traders
4	3	ABC-Sales
5	3	KP-Traders
6	3	JP-Traders
7	4	Amana-Fashion
8	4	JP-Traders
9	5	Modern-Traders
10	6	JP-Traders
11	6	ABC-Traders
Sheet8

May I ask how you posted your sample data so neatly with the table borders?

Regards,
AB

 

[facethegod]

Try

Option Explicit
Sub tst()
Dim r As Range, aa, ac As New Collection, b() As String, c() As String
Dim x As Long, i As Long, ii As Long, a, s As String
With Application
Set r = Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row): a = r
ReDim b(1 To Rows.Count)
For i = 1 To UBound(a, 1)
    aa = Split(a(i, 1), ",")
    For ii = LBound(aa) To UBound(aa)
    x = x + 1: b(x) = Trim(aa(ii))
    On Error Resume Next: ac.Add Item:=b(x), Key:=CStr(b(x))
    Next
Next
On Error GoTo 0
ReDim Preserve b(1 To x): ReDim c(1 To ac.Count): ReDim a(1 To ac.Count) As Long
For i = 1 To UBound(c): c(i) = ac.Item(i): Next
For i = 1 To UBound(b)
x = .Match(b(i), c, 0)
a(x) = a(x) + 1
Next
x = 0
For i = 1 To UBound(a)
If a(i) > 1 Then s = IIf(s = "", c(i), s & "," & c(i)): x = x + a(i)
Next
Cells(1, 3) = x & " [" & s & " ]"
End With
End Sub

HTH

 

[jindon]

UDF
Use in cell
=Kumar(B2:B7)

Function Kumar(rng As Range) As Long
Dim e
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For Each e In  Split(Join(WorksheetFunction.Transpose(rng.Value),","),",")
        If e <> "" Then
            If Not .exists(e) Then
                .add e, 0
            Else
                .item(e) = .item(e) + IIf(.item(e) = 0, 2, 1)
            End If
        End If
    Next
    Kumar = Application.Sum(.items)
End With
End Function