Dumb Question

[wazzulu1]

Hi;

What is the best way to insert a count of the file number in the 3rd column (VBA or Formula)?

I have 22,000 records that I need to group, and figured that if I can count each file number, and place the number in the 3rd column, it would work, just not sure how to do it (Example below)

File Number TheDate TheCount 8114344 7/30/2018 8114344 4/17/2018 8114344 7/10/2018 8140397 6/27/2016 8702054 10/27/2016 8702054 12/27/2016 8702054 5/26/2017 8702054 2/14/2017 8702054 1/27/2017 8702054 3/28/2017 8951128 3/25/2018 8939187 1/18/2016 3565194 1/11/2016 3529427 9/11/2017 3510635 9/18/2017 3504885 3/7/2016 3504885 2/29/2016 3835087 11/1/2016 3835087 2/27/2017 3835087 10/13/2017 3835087 4/4/2018 3835087 4/12/2017 3835087 6/26/2017 3830017 9/18/2017 3825126 9/1/2016 3324479 11/2/2015 3302125 5/23/2016 3614666 2/9/2016 3671019 1/22/2016 3671019 2/8/2016 3297700 8/22/2016 <colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[etaf]

a pivot table would be better
otherwise a countif()

 

[alansidman]

Maybe use the SubTotal Function found on the Ribbon

Data Range

	A​	B​	C​	D​
1​		File Number​	TheDate​	TheCount​
2​		8114344​	7/30/2018​
3​		8114344​	4/17/2018​
4​		8114344​	7/10/2018​
5​	8114344 Count​	3​
6​		8140397​	6/27/2016​
7​	8140397 Count​	1​
8​		8702054​	10/27/2016​
9​		8702054​	12/27/2016​
10​		8702054​	5/26/2017​
11​		8702054​	2/14/2017​
12​		8702054​	1/27/2017​
13​		8702054​	3/28/2017​
14​	8702054 Count​	6​
15​		8951128​	3/25/2018​
16​	8951128 Count​	1​
17​		8939187​	1/18/2016​
18​	8939187 Count​	1​
19​		3565194​	1/11/2016​
20​	3565194 Count​	1​
21​		3529427​	9/11/2017​
22​	3529427 Count​	1​
23​		3510635​	9/18/2017​
24​	3510635 Count​	1​
25​		3504885​	3/7/2016​
26​		3504885​	2/29/2016​
27​	3504885 Count​	2​
28​		3835087​	11/1/2016​
29​		3835087​	2/27/2017​
30​		3835087​	10/13/2017​
31​		3835087​	4/4/2018​
32​		3835087​	4/12/2017​
33​		3835087​	6/26/2017​
34​	3835087 Count​	6​
35​		3830017​	9/18/2017​
36​	3830017 Count​	1​
37​		3825126​	9/1/2016​
38​	3825126 Count​	1​
39​		3324479​	11/2/2015​
40​	3324479 Count​	1​
41​		3302125​	5/23/2016​
42​	3302125 Count​	1​
43​		3614666​	2/9/2016​
44​	3614666 Count​	1​
45​		3671019​	1/22/2016​
46​		3671019​	2/8/2016​
47​	3671019 Count​	2​
48​		3297700​	8/22/2016​
49​	3297700 Count​	1​
50​	Grand Count​	31​

<tbody>
</tbody>

EDIT: I like Etaf's suggestion of a Pivot Table. Cleaner and Neater Solution.

 

[wazzulu1]

I need the count on each actual row, not just a sub total.

I have to group the rows, so I need the number in the third column on each row.

 

[Fluff]

In C2 copied down
=COUNTIF(A:A,A2)
will give you a count of each File Num, but I'm not sure that's what you want.

 

[wazzulu1]

I was trying to place the number on each row, 1,2,3, etc...

 

[alansidman]

I need the count on each actual row, not just a sub total.

Show us an example of what you are looking for. This may require a VBA or PQ solution.

 

[Fluff]

Another possibilty
=IF(COUNTIF(A$2:A2,A2)>1,C1,N(C1)+1)

Otherwise as alansidman has said please show us the output that you want along with an explanation

 

[wazzulu1]

[TABLE="width: 192"]
 <colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
  [TD="class: xl67, width: 64"]File  Number[/TD]
  [TD="class: xl67, width: 64"]TheDate[/TD]
  [TD="class: xl67, width: 64"]TheCount[/TD]
 [/TR]
 [TR]
  [TD="class: xl65"]8114344[/TD]
  [TD="class: xl66, align: right"]7/30/2018[/TD]
  [TD="class: xl65, align: right"]1[/TD]
 [/TR]
 [TR]
  [TD="class: xl65"]8114344[/TD]
  [TD="class: xl66, align: right"]4/17/2018[/TD]
  [TD="class: xl65, align: right"]2[/TD]
 [/TR]
 [TR]
  [TD="class: xl65"]8114344[/TD]
  [TD="class: xl66, align: right"]7/10/2018[/TD]
  [TD="class: xl65, align: right"]3[/TD]
 [/TR]
 [TR]
  [TD="class: xl65"]8140397[/TD]
  [TD="class: xl66, align: right"]6/27/2016[/TD]
  [TD="class: xl65, align: right"]1[/TD]
 [/TR]
 [TR]
  [TD="class: xl65"]8702054[/TD]
  [TD="class: xl66, align: right"]10/27/2016[/TD]
  [TD="class: xl65, align: right"]1[/TD]
 [/TR]
 [TR]
  [TD="class: xl65"]8702054[/TD]
  [TD="class: xl66, align: right"]12/27/2016[/TD]
  [TD="class: xl65, align: right"]2[/TD]
 [/TR]
 [TR]
  [TD="class: xl65"]8702054[/TD]
  [TD="class: xl66, align: right"]5/26/2017[/TD]
  [TD="class: xl65, align: right"]3[/TD]
 [/TR]
 [TR]
  [TD="class: xl65"]8702054[/TD]
  [TD="class: xl66, align: right"]2/14/2017[/TD]
  [TD="class: xl65, align: right"]4[/TD]
 [/TR]
 [TR]
  [TD="class: xl65"]8702054[/TD]
  [TD="class: xl66, align: right"]1/27/2017[/TD]
  [TD="class: xl65, align: right"]5[/TD]
 [/TR]
 [TR]
  [TD="class: xl65"]8702054[/TD]
  [TD="class: xl66, align: right"]3/28/2017[/TD]
  [TD="class: xl65, align: right"]6[/TD]
 [/TR]
 [TR]
  [TD="class: xl65"]8951128[/TD]
  [TD="class: xl66, align: right"]3/25/2018[/TD]
  [TD="class: xl65, align: right"]1[/TD]
 [/TR]
 [TR]
  [TD="class: xl65"]8939187[/TD]
  [TD="class: xl66, align: right"]1/18/2016[/TD]
  [TD="class: xl65, align: right"]1[/TD]
 [/TR]
 [TR]
  [TD="class: xl65"]3565194[/TD]
  [TD="class: xl66, align: right"]1/11/2016[/TD]
  [TD="class: xl65, align: right"]1[/TD]
 [/TR]
 [TR]
  [TD="class: xl65"]3529427[/TD]
  [TD="class: xl66, align: right"]9/11/2017[/TD]
  [TD="class: xl65, align: right"]1[/TD]
 [/TR]
 [TR]
  [TD="class: xl65"]3510635[/TD]
  [TD="class: xl66, align: right"]9/18/2017[/TD]
  [TD="class: xl65, align: right"]1[/TD]
 [/TR]
 [TR]
  [TD="class: xl65"]3504885[/TD]
  [TD="class: xl66, align: right"]3/7/2016[/TD]
  [TD="class: xl65, align: right"]1[/TD]
 [/TR]
 [TR]
  [TD="class: xl65"]3504885[/TD]
  [TD="class: xl66, align: right"]2/29/2016[/TD]
  [TD="class: xl65, align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

How about
=COUNTIF(A$2:A2,A2)