counting the number of times shown

I am trying to figure out a way to put a numerical value next to the id that 
is shown, suggestions?

ex:
ID   This is what I am hoping to get to show up in the next column
55   1
55   2
37   1
46   1
62   1
55   3
89   1
82   1


0
Utf
2/23/2010 7:55:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
750 Views

Similar Articles

[PageSpeed] 39

Hi

With ID in column A, insert this formula in B2 and copy it down as required:

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

Regards,
Per

"klh84" <klh84@discussions.microsoft.com> skrev i meddelelsen 
news:14D13B5C-FEE8-4489-B91E-AC65F2ABB090@microsoft.com...
> I am trying to figure out a way to put a numerical value next to the id 
> that
> is shown, suggestions?
>
> ex:
> ID   This is what I am hoping to get to show up in the next column
> 55   1
> 55   2
> 37   1
> 46   1
> 62   1
> 55   3
> 89   1
> 82   1
>
> 
0
Per
2/23/2010 8:01:41 PM
Hi,

Assuming your data starts in a2 try this in B2 and drag down

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


-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"klh84" wrote:

> I am trying to figure out a way to put a numerical value next to the id that 
> is shown, suggestions?
> 
> ex:
> ID   This is what I am hoping to get to show up in the next column
> 55   1
> 55   2
> 37   1
> 46   1
> 62   1
> 55   3
> 89   1
> 82   1
> 
> 
0
Utf
2/23/2010 8:08:01 PM
In B1 enter:   1
in B2 enter:   =COUNTIF($A$1:$A2,A2) and copy down

-- 
Gary''s Student - gsnu201001


"klh84" wrote:

> I am trying to figure out a way to put a numerical value next to the id that 
> is shown, suggestions?
> 
> ex:
> ID   This is what I am hoping to get to show up in the next column
> 55   1
> 55   2
> 37   1
> 46   1
> 62   1
> 55   3
> 89   1
> 82   1
> 
> 
0
Utf
2/23/2010 8:10:02 PM
Reply:

Similar Artilces:

auto number index snafu
I have a 2003 DB I've been adding to for 2 years. It's at 423 records = now. I export to a PDF for simpler searching and viewing. Using the search the other day, I went to grab DVD 186, on carousel tower= 2. And what came up didn't match the record. The index was totally screwed. I went thru all 423 records and created a numeric index by hand. The auto number field is useless. But I would still prefer an auto number field. Trouble is when I create the auto number field, it's still off the actual numbers of the records.. ie: 99-100 is actually 99-186,187, 188, 189 100-385,...

Auto assign Lot Numbers in PO receiving
Hi! Has anyone made a customization to auto assign Lot numbers upon item receipt? My client's lot number is numeric, they want the system to auto assign this. Any ideas? Thanks! -- Marisol Mortera Marisol, Yes indeed, I created one for a client. You probably will not be able to use it as-is because of some client specific functionality but I'm sure you could use this as a stepping stone. The key features were that it would allow for rapid lot number entry by a. Auto-incrementing the lot number - we had Alphanumeric lot numbers so we added a suffix and b. copy the previous lo...

Sequential ticket numbers and printing 4 to a page
Hi, i have mail merged my numbers from excel into my publisher ticket. Thanks that worked well, now i want to print 4 tickets to an A4 page and in print preview, i get 4 tickets all with the same number on one page. how do i get around this? Many thanks Print preview showing all the same is a bug. Print a test page. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "Neyol" <Neyol@discussions.microsoft.com> wrote in message news:90034D4C-9951-4550-9DAC...

Dates in fomula showing as whole number
I have a fomula in a cell that takes the name of a person (from cell 2B), their License number (from another cell 2C)and the Date that License Expires (From cell 2D). The expire date in "2D" is either the word "none" or a date that that persons license needs to be renewed. Those instructors with "None" come out in the calculated field fine, however the ones with dates come back as whole numbers, Example 8/6/10 shows 40396. any help will be appreciated Hi, You need to change the format of that cell or column, highlight the cell or the column, right click o...

Word 2007: word count wrong?
Hello, I have an issue with some .doc files when opening in Word 2007. In some cases the word count in the status bar is different of the word count of the "Word Count"- window (CTRL+SHIFT+G) or the word count in Word 2003 Example file: http://go.microsoft.com/fwlink/?LinkId=79595 Word 2007 (status bar) show 61019 words Word 2007 (Word count window) show 61010 words Word 2003 also show 61010 words This issue I have not with all documents, but only with some files and it seems that I have this problem only with .doc files but not with .docx files. (installed ve...

attachments shown for all incoming mail
All of a sudden I am now showing all incoming mail with an attachment when there actually is none. Any ideas? ...

Time performance Chart
Greetings. (help) I need to develop a chart that graphically depicts an employees start time and end time each day. This chart would be a bar chart. The bar chart would have days of the month along the bottom, and the side would start at 00:00 at the bottom and end at 24:00 at the top. I need to be able to enter an employees start time and end time and have it charted along the bar. So if the employee started at 07:00 and ended work at 18:00, the chart would show a colored bar that corresponds to the start time and the end time. Also, if an employee came back to work the chart would refl...

Simple Access counting queries
Hi, hoping someone can help a relative newbie with a pretty simple query. My database (Access 2007) has three tables: Customers Products Purchases (many-to-one links to both of the other tables, this is basically a linking table) I have two simple queries I'd like to get out of this database, but I'm a bit stuck on how to construct the SQL. Any direction you can give me would be helpful. 1. List of all customers who have purchased 2 or more products (or 3 or more products, or 4+, etc.) 2. List of all customers who have purchased both Product A and Product B (or A, B, and C, or B an...

Count # of cells b/w cells ...
Hello, I have the following data in a column: 7 0 0 0 7 0 0 0 0 0 7 0 0 7 0 0 0 0 0 0 7 etc. The number of zero's between the 7's is random. I want a formula tha would count the number of zeros between the 7's. Thanks, Ari Bar -- AriBar ----------------------------------------------------------------------- AriBari's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2504 View this thread: http://www.excelforum.com/showthread.php?threadid=38806 Assume A5:A20 is the data, try this: B5 = A5+B4 (copy formula down) Now make a table with 2 column...

Ordering by number and text
I use a report to print out checklists that in the detail section have item number to delineate each checklist item. The item numbers as an example are 1-1, 1-2, 1-2a, 1-2b, or 2-1, 2-2, 2-3, 2-3a, 2-3b and so on. The report is grouped by checklist section and the grouping works perfectly but when the report is printed the item numbers are ordered in this order 1-1, 1-10, 1-11, 1-2, 1-3, 1-3a, 1-3b, 1-4, 1-5, 1-6, 1-7, 1-9. Obviously I want the the order to be in proper numerical sequence where 1-10 comes after 1-9. but even replacing the '-' with a decimal point doesn't ...

matching columns of numbers
In EXCEL 2000 for Windows, I have two columns of numbers. Column A has 500 numbers, Column B has 1000 numbers. I need to know which cells in Column A have a match in Column B, and if so, what is the Cell (or row number) in B that matches to that particular cell in A. How can I do this? Thank you for your help. ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi try the following: - insert a new column between A and B (so make B the new C column) enter the following in B1 =IF(ISNA(MATCH...

Zero filling a number field
I have an auto number field that I want to zero fill to six digits but can't figure out how. Any ideas? You don''t say where you're trying to do this, but basically Format(YourAutoNumber, "000000") pdlginternet@aol.com wrote: >I have an auto number field that I want to zero fill to six digits but >can't figure out how. Any ideas? -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000/2003 Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200711/1 You can't do that wi...

Time Conversion
I need help in converting time. I am using data from a time clock which currently formats time as 1.5. When I input this data into a cell how can I have it formated to read 1 hour and 30 minutes (1:30) T.I.A. Ed What you could do in an adjacent row or column is format them as Time and then using a formula, divide the input time by 24(hours in a day). >-----Original Message----- >I need help in converting time. I am using data from a time clock which >currently formats time as 1.5. When I input this data into a cell how can I >have it formated to read 1 hour and 30 minut...

date/time formatting
I download a csv report from a web based program. Everything works great but the date comes through as "Jul 21 2009 1:51pm". I do not need the time in my report. I have tried reformatting the cells, tried to copy to a new file with the cells already formatted to "7/21/09", opening the cell format using the F2 key and deleting the time (time will still not be formatted correctly). The only way I get get just the time is to retype every cell. What am I doing wrong? I am using Excel 2002 SP3 TIA, Cindy When data is downloaded from the web, a lot of "other&quo...

count if a match occurs
Hi! I have two rows - say Row 1 and Row 2. In the first row I have a answer key. In the second row I have answers from a student. I would like to write a formula where it counts how many answers student got write. Here is a example: Row 0: Q1 Q2 Q3 Q4 Q5 Q6 Row 1: 1 4 2 3 4 1 Row 2: 3 4 1 3 4 1 So student marked 4 questions correctly: Q2, Q4, Q5 and Q6. Hence, the formula should return 4. I know one way to do it but I am sure there is more efficient way to do it. The way I know: Use if statement to compare two corresponding entries and output 1 if...

How do I change numbers to negative without re-typing?
I have a large range of data that needs to be changed to negative numbers, Can I do this in Excel? ...

Determine number of rows with data
Hi I am using the macro below to pull some data from an external workbook. The 2 issues I need to sort are: 1. The number of rows in the external workbook can vary. How do I amend this code to pull all of the rows with data? 2. The number of rows in the autofill also may vary. How do I autofill only the number of rows required? i.e the number of rows in column A that contain data. 'Lookup Previous Month Sales Columns("K:K").Select Selection.NumberFormat = "General" Range("K4").Select Selection.FormulaArray = _ "=S...

Count Age Grouping
I have an access 2k database in which I need to count groups of records of individuals by that age groups such as 14- 20 no of individuals 21-30 no of individuals 31-40 no of individuals 41-50 no of individuals 51-60 no of individuals 61-70 no of individuals 71-80 no of individuals 80+ no of individuals I have both DOB and Age fields in the table I have tried several queries but with no luck and ideas On 19 Mar 2007 16:51:49 -0700, "Nemesis_uk" <nemesis_uk@ntlworld.com> wrote: >I have an access 2k ...

Count the text in a column
I would like to count the text in a column then for it to add a figure in another cell if it meets the text criteria Thanks! Do you mean count the characters? If so =SUM(NOT(ISNUMBER(A1:A20))*LEN(A1:A20)) as an array formula (committed with Ctrl-Shift-Enter) -- HTH RP (remove nothere from the email address if mailing direct) "Peter Curtis" <PeterCurtis@discussions.microsoft.com> wrote in message news:94C093C4-38DC-4989-846A-9352F3298B7C@microsoft.com... > I would like to count the text in a column then for it to add a figure in > another cell if it meets the t...

Converting number to text
Is it possible to convert, say 1234 to one thousand two hundred and thirty four Thanks There is no direct functions to convert this. For a VBA solution check out the below links http://www.ozgrid.com/VBA/ValueToWords.htm http://support.microsoft.com/kb/213360 http://www.xldynamic.com/source/xld.xlFAQ0004.html -- Jacob (MVP - Excel) "booshi" wrote: > Is it possible to convert, say 1234 to one thousand two hundred and thirty > four > > > Thanks > > > . > ...

Sequentially number lines automatically
Version: v.X Operating System: Mac OS X 10.5 (Leopard) Processor: Intel How can automatically number the lines as I enter data. <br><br>so box A1 would automatically be 1 <br> and the next time I created an entry on another line that line would be 2, etc. <br><br>The reason is so that I can sort, delete and the numbers will remain, so I can also go back to the order than things were entered into. <br><br>Could do this manually, but a pain. <br><br>Or could possibly do this with a date and time? <br><br>Thanks Steevee Se...

Sum Times
I have four fields on a form to show time. I want a seprate "Total" field to add the time between the first two fields and then add the time between the second two fields. Like this: In LunchOut LunchIn Out Total 6:00am 12:00pm 12:30pm 4:30pm 10 hours The first four fields are stored as medium times. Can someone let me know how to do this? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200707/1 "ladybug via AccessMonster.com&quo...

time formats #3
I have an Excel sheet with a long list of times spent on various projects. The times should all be in minutes and seconds. The first time reads 2:29 and I know that is accurate, for 2 min, 29 seconds. In the formula bar, it reads, 2:29:00 AM. Another...the cell displays 0:40 and I know that is right, for 40 seconds. But in the formula bar, I see 12:40:00 AM I want numbers, but apparently I am looking at times. I tried changing the format. When I check the format for 2:29, it comes up Custom and says it is hh:mm, not mm:ss. I tried changing it to mm:ss, but that changes the displa...

Excel Number Format Codes
I can't figure out how to format numbers so that when you type 3220 it will look like 32.20 kinda like how you can enter numbers on an adding machine. I would greatly appreciate this number format code. Thank you. -- 1:~ Hi, I think you want to go Tools | Options | Edit | Fixed Decimals - 2. That will divide all the numbers you enter by 100. You will also have to format the row or column to display 2 decimals. Select the cells or range, right mouse click, format cells, numbers, 2 decimals. Hope that helps. Best regards, Kevin "MBB" <MBB@discussions.microsoft.com> w...

Winmail takes 4 minutes to load at boot time
Running Windows Vista Home Premium on Lenovo Laptop 2 gig mem. After logging in after rebooting, it takes 4 minutes to load Win mail. I used the "event Viewer" to see what was happening. It showed ESENT with ID 102--start Win mail, then the next event is ESENT ID 103 4 minutes later--terminate Win mail. Anyone have a clue why it is taking Win mail so long to load? Win mail works fine after computer loads. Fred Dwight Fred wrote: > Running Windows Vista Home Premium on Lenovo Laptop 2 gig mem. After > logging in after rebooting, it takes 4 minutes ...