Count the number of times a name appears

[steven811]

Hi All

we are wanting to calculate the lifetime value of a customer. I have history table of 3 columns (10k of entries) with the customer name appearing every time they have ordered over the past two years. Is there a way I can count the number of times a name appears in a list. Ideally using the names in the list as the index.

Example of the date here. You can see that N2OLIMIT appears four times.

I'd like to add another column that shows the number of times a customer has ordered. Then I can sort and use this information.

[TABLE="width: 227"]
<tbody>[TR]
[TD]03/01/2017[/TD]
[TD="colspan: 2"]AMIENJOM[/TD]
[TD="colspan: 2"]22.04[/TD]
[/TR]
[TR]
[TD]03/01/2017[/TD]
[TD="colspan: 2"]N20LIMIT[/TD]
[TD="colspan: 2"]605.50[/TD]
[/TR]
[TR]
[TD]03/01/2017[/TD]
[TD="colspan: 2"]JKPLUMBI[/TD]
[TD="colspan: 2"]146.19[/TD]
[/TR]
[TR]
[TD]03/01/2017[/TD]
[TD="colspan: 2"]N20LIMIT[/TD]
[TD="colspan: 2"]217.00[/TD]
[/TR]
[TR]
[TD]03/01/2017[/TD]
[TD="colspan: 2"]N20LIMIT[/TD]
[TD="colspan: 2"]237.43[/TD]
[/TR]
[TR]
[TD]03/01/2017[/TD]
[TD="colspan: 2"]GBSCONSU[/TD]
[TD="colspan: 2"]276.47[/TD]
[/TR]
[TR]
[TD]03/01/2017[/TD]
[TD="colspan: 2"]DESILVAT[/TD]
[TD="colspan: 2"]46.98[/TD]
[/TR]
[TR]
[TD]03/01/2017[/TD]
[TD="colspan: 2"]CAMBRIAN[/TD]
[TD="colspan: 2"]71.05[/TD]
[/TR]
[TR]
[TD]03/01/2017[/TD]
[TD="colspan: 2"]ACACIARA[/TD]
[TD="colspan: 2"]38.64[/TD]
[/TR]
[TR]
[TD]03/01/2017[/TD]
[TD="colspan: 2"]KRULIVEL[/TD]
[TD="colspan: 2"]166.50[/TD]
[/TR]
[TR]
[TD]03/01/2017[/TD]
[TD="colspan: 2"]N20LIMIT[/TD]
[TD="colspan: 2"]1498.50[/TD]
[/TR]
[TR]
[TD]03/01/2017[/TD]
[TD="colspan: 2"]UNIVERS2[/TD]
[TD="colspan: 2"]1209.35[/TD]
[/TR]
[TR]
[TD]03/01/2017[/TD]
[TD="colspan: 2"]PROCUREM[/TD]
[TD="colspan: 2"]235.50[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance

Steven811

 

[DanteAmor]

You can use a pivot table. You can watch any video to create pivot table, for example:

https://www.youtube.com/watch?v=sJqmqKBKwGs

Excel Workbook
	A	B	C	D	E	F
1	DATE	NAME	VALUE
2	03/01/2017	AMIENJOM	22.04		Rtulos de fila	Cuenta de VALUE
3	03/01/2017	N20LIMIT	605.5		ACACIARA	1
4	03/01/2017	JKPLUMBI	146.19		AMIENJOM	1
5	03/01/2017	N20LIMIT	217		CAMBRIAN	1
6	03/01/2017	N20LIMIT	237.43		DESILVAT	1
7	03/01/2017	GBSCONSU	276.47		GBSCONSU	1
8	03/01/2017	DESILVAT	46.98		JKPLUMBI	1
9	03/01/2017	CAMBRIAN	71.05		KRULIVEL	1
10	03/01/2017	ACACIARA	38.64		N20LIMIT	4
11	03/01/2017	KRULIVEL	166.5		PROCUREM	1
12	03/01/2017	N20LIMIT	1498.5		UNIVERS2	1
13	03/01/2017	UNIVERS2	1209.35		Total general	13
14	03/01/2017	PROCUREM	235.5
Hoja2

 

[mumps]

Assuming your data is in "Sheet1", columns A:C, starting in row 2, try this macro:

Sub steven()
    Application.ScreenUpdating = False
    Dim ws1 As Worksheet, i As Long, v1, item As Variant, rngList As Object
    Set rngList = CreateObject("Scripting.Dictionary")
    Set ws1 = Sheets("Sheet1")
    v1 = ws1.Range("B2", ws1.Range("B" & Rows.Count).End(xlUp)).Value
    For i = LBound(v1) To UBound(v1, 1)
        If Not rngList.Exists(v1(i, 1)) Then
            rngList.Add v1(i, 1), Nothing
        End If
    Next i
    For Each item In rngList
        Cells(Rows.Count, "E").End(xlUp).Offset(1, 0) = item
        Cells(Rows.Count, "F").End(xlUp).Offset(1, 0) = WorksheetFunction.CountIf(Range("B:B"), item)
    Next item
    Application.ScreenUpdating = True
End Sub

 

[jtakw]

Hi,

You didn't explain How you want the results, and you didn't show any sample of results, so maybe this:

Book1
	A	B	C	D
1	3/1/2017	AMIENJOM	22.04	1
2	3/1/2017	N20LIMIT	605.5
3	3/1/2017	JKPLUMBI	146.19	1
4	3/1/2017	N20LIMIT	217
5	3/1/2017	N20LIMIT	237.43
6	3/1/2017	GBSCONSU	276.47	1
7	3/1/2017	DESILVAT	46.98	1
8	3/1/2017	CAMBRIAN	71.05	1
9	3/1/2017	ACACIARA	38.64	1
10	3/1/2017	KRULIVEL	166.5	1
11	3/1/2017	N20LIMIT	1498.5	4
12	3/1/2017	UNIVERS2	1209.35	1
13	3/1/2017	PROCUREM	235.5	1
Sheet637

Cell Formulas
Range	Formula
D1	=IF(COUNTIF(B$1:B1,B1)=COUNTIF(B$1:B$13,B1),COUNTIF(B$1:B1,B1),"")

formula copied down.