Count number of times a value appears

T

tommill52

New Member
Joined
May 12, 2015
Messages
27
Hi. I need some VBA code to perform the following.

Sheet1 will contain a list of part numbers. There could be up to 100 or more different part numbers, and the same part numbers can appear multiple times (they relate to a barcode being scanned that holds the part number, and the same part number can be scanned a number of times).

I need Sheet2 to 'analyze' Sheet1 so that Sheet2 reports a list of the individual part numbers found in Sheet1 in column A, and the number of times each one occurs in column B. The problem is that we will never know what part numbers are going to appear in Sheet1, so there is no 'source' or reference list to compare against.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
You can do this with formulas and no VBA. Given the sample sheets below:

Excel 2010
A
Parts
abc
def
abc
def
ghi
jkl
mno
pqr
mno
def

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

</tbody>
Sheet1​



Excel 2010
AB
Unique part list
abc
def
ghi
jkl
mno
pqr

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B3[/TH]
[TD="align: left"]=IF(A3<>"",COUNTIF(Sheet1!$A$2:$A$101,A3),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A3[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$A$2:$A$101,AGGREGATE(15,3,IF(MATCH(Sheet1!$A$2:$A$101,Sheet1!$A$2:$A$101,0)=ROW($A$2:$A$101)-ROW($A$2)+1,ROW($A$2:$A$101)-ROW($A$2)+1),ROW()-ROW($A$3)+1)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



You can enter the formulas as indicated in A3 and B3 of sheet 2, then copy and paste them down the column. The $A$2 in the formula refers to the top cell of the range on Sheet1, The $A$2:$A$101 is the range of parts, and the $A$3 refers to the top cell in Sheet2.

Let me know if this works for you.
 
Upvote 0
Or, for a VBA option, something like:

Code: 
Sub MakeList()

Dim myRange1 As Range
Dim myRange2 As Range
Set myRange1 = Sheets("Sheet1").Range("A1")
Set myRange2 = Sheets("Sheet2").Range("A1")

Sheet2.Range("A:B").Clear

Do Until IsEmpty(myRange1)
    If Application.WorksheetFunction.CountIf(Sheets("Sheet2").Range("A:A"), myRange1.Value) = 0 Then
        myRange2.Value = myRange1.Value
        Set myRange2 = myRange2.Offset(1, 0)
    Else
        'do nothing
    End If
    Set myRange1 = myRange1.Offset(1, 0)
Loop

Set myRange2 = Sheets("Sheet2").Range("B1")

Do Until IsEmpty(myRange2.Offset(0, -1))
    myRange2.Formula = "=COUNTIF(Sheet1!A:A,A" & myRange2.Row & ")"
    Set myRange2 = myRange2.Offset(1, 0)
Loop

End Sub

This assumes that your parts are listed in column A of Sheet1, and that the list starts in A1. It makes a list of the unique values on Sheet2 starting in cell A1, and it puts the count of each value in column B.

Example Sheet1:
[table="width: 100, class: grid"]
[tr]
[td]11111[/td]
[/tr]
[tr]
[td]22222[/td]
[/tr]
[tr]
[td]33333[/td]
[/tr]
[tr]
[td]11111[/td]
[/tr]
[tr]
[td]11111[/td]
[/tr]
[tr]
[td]22222[/td]
[/tr]
[/table]

Example output on Sheet2:
[table="width: 100, class: grid"]
[tr]
[td]11111[/td]
[td]3[/td]
[/tr]
[tr]
[td]22222[/td]
[td]2[/td]
[/tr]
[tr]
[td]33333[/td]
[td]1[/td]
[/tr]
[/table]
 
Last edited:
Upvote 0
Eric W said:
You can do this with formulas and no VBA. Given the sample sheets below:

Excel 2010
A
Parts
abc
def
abc
def
ghi
jkl
mno
pqr
mno
def

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

</tbody>
Sheet1​



Excel 2010
AB
Unique part list
abc
def
ghi
jkl
mno
pqr

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B3[/TH]
[TD="align: left"]=IF(A3<>"",COUNTIF(Sheet1!$A$2:$A$101,A3),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A3[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$A$2:$A$101,AGGREGATE(15,3,IF(MATCH(Sheet1!$A$2:$A$101,Sheet1!$A$2:$A$101,0)=ROW($A$2:$A$101)-ROW($A$2)+1,ROW($A$2:$A$101)-ROW($A$2)+1),ROW()-ROW($A$3)+1)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



You can enter the formulas as indicated in A3 and B3 of sheet 2, then copy and paste them down the column. The $A$2 in the formula refers to the top cell of the range on Sheet1, The $A$2:$A$101 is the range of parts, and the $A$3 refers to the top cell in Sheet2.

Let me know if this works for you.
Click to expand...

Thank you for your reply. Sadly, I couldn't get the formula to load into A3 as above. It was probably an error on my part as I don't think I understood your instructions.

Someone else has given some VBA code and that works perfectly.

Thanks again.
 
Upvote 0
I'm just glad you got an acceptable solution. If you ever want to figure out the formula, come back and someone will help you out.
 
Upvote 0
Where would I need to change the code if the list starts from L2 in the sheet with all my data.(This is for the Vba code)(I am a beginner to VBA by the way.)
 
Upvote 0
elmer007 said:
Or, for a VBA option, something like:

Code: 
Sub MakeList()

Dim myRange1 As Range
Dim myRange2 As Range
Set myRange1 = Sheets("Sheet1").Range("A1")
Set myRange2 = Sheets("Sheet2").Range("A1")

Sheet2.Range("A:B").Clear

Do Until IsEmpty(myRange1)
    If Application.WorksheetFunction.CountIf(Sheets("Sheet2").Range("A:A"), myRange1.Value) = 0 Then
        myRange2.Value = myRange1.Value
        Set myRange2 = myRange2.Offset(1, 0)
    Else
        'do nothing
    End If
    Set myRange1 = myRange1.Offset(1, 0)
Loop

Set myRange2 = Sheets("Sheet2").Range("B1")

Do Until IsEmpty(myRange2.Offset(0, -1))
    myRange2.Formula = "=COUNTIF(Sheet1!A:A,A" & myRange2.Row & ")"
    Set myRange2 = myRange2.Offset(1, 0)
Loop

End Sub

This assumes that your parts are listed in column A of Sheet1, and that the list starts in A1. It makes a list of the unique values on Sheet2 starting in cell A1, and it puts the count of each value in column B.

Example Sheet1:
11111
22222
33333
11111
11111
22222

Example output on Sheet2:
111113
222222
333331
Click to expand...
Can you do this without using The myRange2.formula
 
Upvote 0
Please start a thread of your own for this, as requested.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
VBA for Generating Cycle Counts
2
Replies
17
Views
413
kvsrinivasamurthy
K
H
  • Question Question
List out duplicate items in a list with different costs/values
Replies
6
Views
129
H3MSY
H
2
  • Question Question
Using Barcode and Data Validation List to Prevent Errors
Replies
4
Views
674
2KGrafix
2
J
  • Question Question
Counting Number of Consecutive Negative/Positive Numbers in a Row
Replies
6
Views
394
Cubist
Cubist
2
  • Solved
VBA for removing extra characters in Barcode scanning
2 3 4
Replies
39
Views
2K
2KGrafix
2

Forum statistics

Threads
1,222,581
Messages
6,166,887
Members
452,082
Latest member
pmajax

Share this page

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