| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I Count repeating numbers in the previous column?
I have this set up:
Market Count 1501 1501 1501 1501 1501 5 1506 1506 1506 3 1507 1507 1507 3 I did this manually for a file of over 1000 lines. Now I need to do the same thing for a file of 5000 lines. The file is sorted by "Market". I just need to create a formula that will Count the numbers repeated in column A and put the answer in column B. Can you help? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how do I Count repeating numbers in the previous column?
You want Countif. In b1, put:
=countif(a:a,a1) Copy down to the end of column A (you can do this by double-clicking on the fill handle). If you want the entry in column B to appear only when there's a change in column A, use something like: =if(a1=a2,"",countif(a:a,a1)) Regards, Fred "Texas Nuckols" wrote in message ... I have this set up: Market Count 1501 1501 1501 1501 1501 5 1506 1506 1506 3 1507 1507 1507 3 I did this manually for a file of over 1000 lines. Now I need to do the same thing for a file of 5000 lines. The file is sorted by "Market". I just need to create a formula that will Count the numbers repeated in column A and put the answer in column B. Can you help? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how do I Count repeating numbers in the previous column?
Try this:
=IF(A2=A3,"",COUNTIF(A$2:A$12,A2)) Copy down as needed -- Biff Microsoft Excel MVP "Texas Nuckols" wrote in message ... I have this set up: Market Count 1501 1501 1501 1501 1501 5 1506 1506 1506 3 1507 1507 1507 3 I did this manually for a file of over 1000 lines. Now I need to do the same thing for a file of 5000 lines. The file is sorted by "Market". I just need to create a formula that will Count the numbers repeated in column A and put the answer in column B. Can you help? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how do I Count repeating numbers in the previous column?
Not very good at some of these formulas. Can you write the formula using the
data in my example? "Fred Smith" wrote: You want Countif. In b1, put: =countif(a:a,a1) Copy down to the end of column A (you can do this by double-clicking on the fill handle). If you want the entry in column B to appear only when there's a change in column A, use something like: =if(a1=a2,"",countif(a:a,a1)) Regards, Fred "Texas Nuckols" wrote in message ... I have this set up: Market Count 1501 1501 1501 1501 1501 5 1506 1506 1506 3 1507 1507 1507 3 I did this manually for a file of over 1000 lines. Now I need to do the same thing for a file of 5000 lines. The file is sorted by "Market". I just need to create a formula that will Count the numbers repeated in column A and put the answer in column B. Can you help? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how do I Count repeating numbers in the previous column?
This worked for the numbers shown. But when I copied it down the next counts
were 15, 6 and 27, but the formula gave me "0" for each. ??? "T. Valko" wrote: Try this: =IF(A2=A3,"",COUNTIF(A$2:A$12,A2)) Copy down as needed -- Biff Microsoft Excel MVP "Texas Nuckols" wrote in message ... I have this set up: Market Count 1501 1501 1501 1501 1501 5 1506 1506 1506 3 1507 1507 1507 3 I did this manually for a file of over 1000 lines. Now I need to do the same thing for a file of 5000 lines. The file is sorted by "Market". I just need to create a formula that will Count the numbers repeated in column A and put the answer in column B. Can you help? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how do I Count repeating numbers in the previous column?
I guess I don't give myself enough credit. I copied over your formula
exactly as below and it worked beautifully! Thank you for all the hours of work you saved me!!! "Fred Smith" wrote: You want Countif. In b1, put: =countif(a:a,a1) Copy down to the end of column A (you can do this by double-clicking on the fill handle). If you want the entry in column B to appear only when there's a change in column A, use something like: =if(a1=a2,"",countif(a:a,a1)) Regards, Fred "Texas Nuckols" wrote in message ... I have this set up: Market Count 1501 1501 1501 1501 1501 5 1506 1506 1506 3 1507 1507 1507 3 I did this manually for a file of over 1000 lines. Now I need to do the same thing for a file of 5000 lines. The file is sorted by "Market". I just need to create a formula that will Count the numbers repeated in column A and put the answer in column B. Can you help? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how do I Count repeating numbers in the previous column?
You have change/adjust the cell references to suit your application. The
formula I posted is based on the sample data you posted. If your data is in the range A2:A1000 then: =IF(A2=A3,"",COUNTIF(A$2:A$1000,A2)) -- Biff Microsoft Excel MVP "Texas Nuckols" wrote in message ... This worked for the numbers shown. But when I copied it down the next counts were 15, 6 and 27, but the formula gave me "0" for each. ??? "T. Valko" wrote: Try this: =IF(A2=A3,"",COUNTIF(A$2:A$12,A2)) Copy down as needed -- Biff Microsoft Excel MVP "Texas Nuckols" wrote in message ... I have this set up: Market Count 1501 1501 1501 1501 1501 5 1506 1506 1506 3 1507 1507 1507 3 I did this manually for a file of over 1000 lines. Now I need to do the same thing for a file of 5000 lines. The file is sorted by "Market". I just need to create a formula that will Count the numbers repeated in column A and put the answer in column B. Can you help? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how do I Count repeating numbers in the previous column?
Your data appears to already be sorted by market number, inteh furture you
might try the subtotal feature, just change from default of "sum" to "count". HTH "Texas Nuckols" wrote: I have this set up: Market Count 1501 1501 1501 1501 1501 5 1506 1506 1506 3 1507 1507 1507 3 I did this manually for a file of over 1000 lines. Now I need to do the same thing for a file of 5000 lines. The file is sorted by "Market". I just need to create a formula that will Count the numbers repeated in column A and put the answer in column B. Can you help? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how do I Count repeating numbers in the previous column?
PIVOT table is a right tool for this. No formula is required.
"Texas Nuckols" wrote: I have this set up: Market Count 1501 1501 1501 1501 1501 5 1506 1506 1506 3 1507 1507 1507 3 I did this manually for a file of over 1000 lines. Now I need to do the same thing for a file of 5000 lines. The file is sorted by "Market". I just need to create a formula that will Count the numbers repeated in column A and put the answer in column B. Can you help? |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how do I Count repeating numbers in the previous column?
Glad I was of assistance. I'm glad you got it working. In the future, if you
want "Can you write the formula using the data in my example?", you need to tell us *where* your data is. It's true you told us your data was in column A, but you didn't say what row it started on. So we either take our best guess, or give examples which you have to modify to suit your situation. We would much rather give the exact formula, but are more often than not stymied by the lack of information in the original post. Regards, Fred. "Texas Nuckols" wrote in message ... I guess I don't give myself enough credit. I copied over your formula exactly as below and it worked beautifully! Thank you for all the hours of work you saved me!!! "Fred Smith" wrote: You want Countif. In b1, put: =countif(a:a,a1) Copy down to the end of column A (you can do this by double-clicking on the fill handle). If you want the entry in column B to appear only when there's a change in column A, use something like: =if(a1=a2,"",countif(a:a,a1)) Regards, Fred "Texas Nuckols" wrote in message ... I have this set up: Market Count 1501 1501 1501 1501 1501 5 1506 1506 1506 3 1507 1507 1507 3 I did this manually for a file of over 1000 lines. Now I need to do the same thing for a file of 5000 lines. The file is sorted by "Market". I just need to create a formula that will Count the numbers repeated in column A and put the answer in column B. Can you help? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| formula to count sets of repeating numbers | Excel Worksheet Functions | |||
| add 1 count to a column of numbers | Excel Worksheet Functions | |||
| How Do I change repeating numbers in a column | Excel Discussion (Misc queries) | |||
| Match Last Occurrence of two numbers and Count to Previous Occurence | Excel Worksheet Functions | |||
| Repeating a sequence of numbers down a column | Excel Discussion (Misc queries) | |||
Linear Mode
