Dumb Question

W

wazzulu1

Board Regular
Joined
Oct 4, 2006
Messages
164
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:confused: (Example below)
[TABLE="width: 8"]
<tbody>[TR]
[TD][TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]File Number[/TD]
[TD="class: xl65, width: 64"]TheDate[/TD]
[TD="class: xl65, width: 64"]TheCount[/TD]
[/TR]
[TR]
[TD="class: xl63"]8114344[/TD]
[TD="class: xl64, align: right"]7/30/2018[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]8114344[/TD]
[TD="class: xl64, align: right"]4/17/2018[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]8114344[/TD]
[TD="class: xl64, align: right"]7/10/2018[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]8140397[/TD]
[TD="class: xl64, align: right"]6/27/2016[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]8702054[/TD]
[TD="class: xl64, align: right"]10/27/2016[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]8702054[/TD]
[TD="class: xl64, align: right"]12/27/2016[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]8702054[/TD]
[TD="class: xl64, align: right"]5/26/2017[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]8702054[/TD]
[TD="class: xl64, align: right"]2/14/2017[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]8702054[/TD]
[TD="class: xl64, align: right"]1/27/2017[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]8702054[/TD]
[TD="class: xl64, align: right"]3/28/2017[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]8951128[/TD]
[TD="class: xl64, align: right"]3/25/2018[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]8939187[/TD]
[TD="class: xl64, align: right"]1/18/2016[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3565194[/TD]
[TD="class: xl64, align: right"]1/11/2016[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3529427[/TD]
[TD="class: xl64, align: right"]9/11/2017[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3510635[/TD]
[TD="class: xl64, align: right"]9/18/2017[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3504885[/TD]
[TD="class: xl64, align: right"]3/7/2016[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3504885[/TD]
[TD="class: xl64, align: right"]2/29/2016[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3835087[/TD]
[TD="class: xl64, align: right"]11/1/2016[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3835087[/TD]
[TD="class: xl64, align: right"]2/27/2017[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3835087[/TD]
[TD="class: xl64, align: right"]10/13/2017[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3835087[/TD]
[TD="class: xl64, align: right"]4/4/2018[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3835087[/TD]
[TD="class: xl64, align: right"]4/12/2017[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3835087[/TD]
[TD="class: xl64, align: right"]6/26/2017[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3830017[/TD]
[TD="class: xl64, align: right"]9/18/2017[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3825126[/TD]
[TD="class: xl64, align: right"]9/1/2016[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3324479[/TD]
[TD="class: xl64, align: right"]11/2/2015[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3302125[/TD]
[TD="class: xl64, align: right"]5/23/2016[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3614666[/TD]
[TD="class: xl64, align: right"]2/9/2016[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3671019[/TD]
[TD="class: xl64, align: right"]1/22/2016[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3671019[/TD]
[TD="class: xl64, align: right"]2/8/2016[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3297700[/TD]
[TD="class: xl64, align: right"]8/22/2016[/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
a pivot table would be better
otherwise a countif()
 
Upvote 0
Maybe use the SubTotal Function found on the Ribbon

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


EDIT: I like Etaf's suggestion of a Pivot Table. Cleaner and Neater Solution.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
I need the count on each actual row, not just a sub total.
Click to expand...

Show us an example of what you are looking for. This may require a VBA or PQ solution.
 
Upvote 0
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
 
Last edited:
Upvote 0
Code: 
[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]
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Delete all rows including cells in column "A" that are identical to the values in cells "D1", "E1","F1" and "A6".
2
Replies
10
Views
586
Peter_SSs
Peter_SSs
C
  • Solved
Extract Dates from 2 columns
Replies
2
Views
655
Peter_SSs
Peter_SSs
H
  • Solved
Modify content of cells in column "F" according to the content of cells in column "A"
Replies
5
Views
520
igold
igold
H
  • Solved
Find Value of a cell in column "B"
Replies
4
Views
455
Akuini
Akuini
skyh3ck
  • Question Question
1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help
Replies
2
Views
571
skyh3ck
skyh3ck

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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