Count #of times an ID(name) appears and place the value in another column

SeanDD

New Member
Joined
Feb 18, 2018
Messages
6
Did a search, but did not find an answer that appeared to be what I was looking for.
I have a weekly report that is sent to me (.csv file), the report is 8 rows long and 8 columns wide (A-H).
The table essentially looks like this:

Date:2/16/2018blankTime7:00:00blankSystem Name:XXCCDS
NameXXCCDS
OperatorGNE\caguyonj
ActionStartup
Comment
Revision
Location
Seq Number

<tbody>
</tbody>

Depending on how many users (operators) log on and log off during a week, this report could be extremely long.
What I'm trying to do is determine how many times a specific 'operator' shows up within a weekly report. I thought of using a vlookup, but I do not have a table in which to base my 'lookup' information off. 'Operators' are constantly being added and removed with their own specific 'ID'.
Is there anyway in which I could input a formula in which in another column it would show me the 'Operator' ID and the number of times their ID shows up within the report next to their name?
If this is beyond what a 'newbie' should be working on, I understand, just finding more and more tasks in which Excel is very useful to me in this job.
Thank you for any assistance.
 
Believe me when I say, I'm not being critical, this is awesome what you sent. I'm just trying to see why it appears to count and place the same data twice over in the right hand column. This is great learning for me, I'm just not at this level yet. This is what I'm trying to figure out why it is counting, and/or placing it twice.
Date:2/20/2018Time:17:36:26System Name:CTXSSFBASPRD02#OperatorIDNumber of actions
Name:CTXSSFBASPRD021GNE\dempsays2
Operator:GNE\dempsays2GNE\dempsays2
Action:Startup3GNE\bernaldf2
Comment:Main Menu successful user logon4GNE\bernaldf2
Revision:15GNE\mendezr31
Location:CTXSSFBASPRD026GNE\skacelj1
Seq Number:4734807GNE\sharpn2
8GNE\sharpn2
Date:2/20/2018Time:17:36:26System Name:CTXSSFBASPRD029GNE\shnaydes1
Name:CTXSSFBASPRD0210GNE\shnaydes1
Operator:GNE\dempsays11GNE\mendezr31
Action:Startup12GNE\mendezr31
Comment:Main Menu successful user logon13GNE\karkis0
Revision:114GNE\biekk0
Location:CTXSSFBASPRD0215GNE\biekk0
Seq Number:47347916GNE\isaacsj0
17GNE\isaacsj0
Date:2/20/2018Time:17:21:59System Name:CTXSSFBASPRD0418GNE\patelg60
Name:CTXSSFBASPRD0419GNE\dionellm0
Operator:GNE\bernaldf20GNE\cantoj10
Action:Shutdown21GNE\cantoj10
Comment:Main Menu shutdown22GNE\clinej20
Revision:123GNE\clinej20
Location:CTXSSFBASPRD0424GNE\sharpn2
Seq Number:47327725GNE\cantoj10

<colgroup><col><col><col span="4"><col><col><col><col><col><col></colgroup><tbody>
</tbody>


I modified the range to accommodate all of the data, but how it is obtaining the data at first is what I'm looking through my excel book to figure out. The whole report is about 4000 cells long. It took that data just fine, but the question is why ID's are showing up twice with the same overall count.
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
The left column is just listing all the user ID's in sequence as they appear in the log. The right column is just a countif formula to count how many times each user ID shows up.
 
Upvote 0

Forum statistics

Threads
1,214,832
Messages
6,121,847
Members
449,051
Latest member
excelquestion515

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