|
Counting Macro
Every cell in Column A starting in row 2 is filled with seven digit numbers.
Often times each seven digit number will occur more than once. I want to create a macro that covers every cell in Columns A:B starting in row 2 so that if I have a seven digit number that appears for the first time in cell A5 a 1 will appear next to it in B5. If that same seven digit number appears again by chance in cell A25 a 2 will appear next to it in cell B25. Finally if that same seven digit number appears by chance in cell A39 then a 3 will appear next to it in cell B39. |
Counting Macro
F1 has the number you are looking for. B2 contains this formula... =IF(A2=$F$1,COUNTIF($A$2:A2, $F$1),"") Fill the formula down column B -- Jim Cone Portland, Oregon USA "MCheru" wrote in message Every cell in Column A starting in row 2 is filled with seven digit numbers. Often times each seven digit number will occur more than once. I want to create a macro that covers every cell in Columns A:B starting in row 2 so that if I have a seven digit number that appears for the first time in cell A5 a 1 will appear next to it in B5. If that same seven digit number appears again by chance in cell A25 a 2 will appear next to it in cell B25. Finally if that same seven digit number appears by chance in cell A39 then a 3 will appear next to it in cell B39. |
Counting Macro
If I understand you correctly, you don't need a macro to do that. Put this
formula in B2 and copy it down as far as you like... =IF(A2="","",COUNTIF(A$2:A2,A2)) -- Rick (MVP - Excel) "MCheru" wrote in message ... Every cell in Column A starting in row 2 is filled with seven digit numbers. Often times each seven digit number will occur more than once. I want to create a macro that covers every cell in Columns A:B starting in row 2 so that if I have a seven digit number that appears for the first time in cell A5 a 1 will appear next to it in B5. If that same seven digit number appears again by chance in cell A25 a 2 will appear next to it in cell B25. Finally if that same seven digit number appears by chance in cell A39 then a 3 will appear next to it in cell B39. |
Counting Macro
I may have made a mistake but when I put this into B2 and drag it down a
number of cells in Column B the formula returns a blank cell. "Jim Cone" wrote: F1 has the number you are looking for. B2 contains this formula... =IF(A2=$F$1,COUNTIF($A$2:A2, $F$1),"") Fill the formula down column B -- Jim Cone Portland, Oregon USA "MCheru" wrote in message Every cell in Column A starting in row 2 is filled with seven digit numbers. Often times each seven digit number will occur more than once. I want to create a macro that covers every cell in Columns A:B starting in row 2 so that if I have a seven digit number that appears for the first time in cell A5 a 1 will appear next to it in B5. If that same seven digit number appears again by chance in cell A25 a 2 will appear next to it in cell B25. Finally if that same seven digit number appears by chance in cell A39 then a 3 will appear next to it in cell B39. |
Counting Macro
Wow, you were right. It works; I didn't think it was possible without a
macro. If possible, could you explain this formula to me? "Rick Rothstein" wrote: If I understand you correctly, you don't need a macro to do that. Put this formula in B2 and copy it down as far as you like... =IF(A2="","",COUNTIF(A$2:A2,A2)) -- Rick (MVP - Excel) "MCheru" wrote in message ... Every cell in Column A starting in row 2 is filled with seven digit numbers. Often times each seven digit number will occur more than once. I want to create a macro that covers every cell in Columns A:B starting in row 2 so that if I have a seven digit number that appears for the first time in cell A5 a 1 will appear next to it in B5. If that same seven digit number appears again by chance in cell A25 a 2 will appear next to it in cell B25. Finally if that same seven digit number appears by chance in cell A39 then a 3 will appear next to it in cell B39. |
Counting Macro
Yes, you will get some blank cells, as the formula only counts the number in cell F1. However, it appears that Rick R. interpreted your question differently than I did and provided the answer you need. -- Jim Cone Portland, Oregon USA "MCheru" wrote in message I may have made a mistake but when I put this into B2 and drag it down a number of cells in Column B the formula returns a blank cell. "Jim Cone" wrote: F1 has the number you are looking for. B2 contains this formula... =IF(A2=$F$1,COUNTIF($A$2:A2, $F$1),"") Fill the formula down column B -- Jim Cone Portland, Oregon USA |
Counting Macro
The IF(A2="","" part of the formula just makes sure if the cell in Column A
is blank, nothing will be displayed in Column B at that same row. The real workhorse of the formula is this... COUNTIF(A$2:A2,A2) which would produce a 0 result if the cell in Column A is blank (hence, the above IF function part). The way COUNTIF works is it looks at the range in its first argument, cell by cell, and sees if the contents of any of those cells equals what is specified in the second argument. Let's look at the range argument first (A$2:A2). The $ sign in front of the first 2 in the start cell for the range makes that row reference absolute (that is, it will *not* change when the formula is copied down); however, the 2 in the end cell for the range does not have a $ sign in front of it (which means it is a relative row reference and it *will* change when the formula is copied down). So, let's assume we have copied the formula down for a few hundred cells or so. The formula in, say, B100 will be this... IF(A100="","",COUNTIF(A$2:A100,A100)) So, if A100 is blank, then B100 will be blank; otherwise, it will display the result from this function call... COUNTIF(A$2:A100,A100) So, it will count all the cells in the range A$2:A100 (the first argument) that are equal to the contents of A100 (the second argument). This is what you asked to be counted and displayed. -- Rick (MVP - Excel) "MCheru" wrote in message ... Wow, you were right. It works; I didn't think it was possible without a macro. If possible, could you explain this formula to me? "Rick Rothstein" wrote: If I understand you correctly, you don't need a macro to do that. Put this formula in B2 and copy it down as far as you like... =IF(A2="","",COUNTIF(A$2:A2,A2)) -- Rick (MVP - Excel) "MCheru" wrote in message ... Every cell in Column A starting in row 2 is filled with seven digit numbers. Often times each seven digit number will occur more than once. I want to create a macro that covers every cell in Columns A:B starting in row 2 so that if I have a seven digit number that appears for the first time in cell A5 a 1 will appear next to it in B5. If that same seven digit number appears again by chance in cell A25 a 2 will appear next to it in cell B25. Finally if that same seven digit number appears by chance in cell A39 then a 3 will appear next to it in cell B39. |
Counting Macro
I see. My apologies. I can see where I might not have been clear. However
thank you very much for you're help! "Jim Cone" wrote: Yes, you will get some blank cells, as the formula only counts the number in cell F1. However, it appears that Rick R. interpreted your question differently than I did and provided the answer you need. -- Jim Cone Portland, Oregon USA "MCheru" wrote in message I may have made a mistake but when I put this into B2 and drag it down a number of cells in Column B the formula returns a blank cell. "Jim Cone" wrote: F1 has the number you are looking for. B2 contains this formula... =IF(A2=$F$1,COUNTIF($A$2:A2, $F$1),"") Fill the formula down column B -- Jim Cone Portland, Oregon USA |
Counting Macro
Fascinating and very cool! Thanks for helping me to understand this formula!
I appreciate you're help. "Rick Rothstein" wrote: The IF(A2="","" part of the formula just makes sure if the cell in Column A is blank, nothing will be displayed in Column B at that same row. The real workhorse of the formula is this... COUNTIF(A$2:A2,A2) which would produce a 0 result if the cell in Column A is blank (hence, the above IF function part). The way COUNTIF works is it looks at the range in its first argument, cell by cell, and sees if the contents of any of those cells equals what is specified in the second argument. Let's look at the range argument first (A$2:A2). The $ sign in front of the first 2 in the start cell for the range makes that row reference absolute (that is, it will *not* change when the formula is copied down); however, the 2 in the end cell for the range does not have a $ sign in front of it (which means it is a relative row reference and it *will* change when the formula is copied down). So, let's assume we have copied the formula down for a few hundred cells or so. The formula in, say, B100 will be this... IF(A100="","",COUNTIF(A$2:A100,A100)) So, if A100 is blank, then B100 will be blank; otherwise, it will display the result from this function call... COUNTIF(A$2:A100,A100) So, it will count all the cells in the range A$2:A100 (the first argument) that are equal to the contents of A100 (the second argument). This is what you asked to be counted and displayed. -- Rick (MVP - Excel) "MCheru" wrote in message ... Wow, you were right. It works; I didn't think it was possible without a macro. If possible, could you explain this formula to me? "Rick Rothstein" wrote: If I understand you correctly, you don't need a macro to do that. Put this formula in B2 and copy it down as far as you like... =IF(A2="","",COUNTIF(A$2:A2,A2)) -- Rick (MVP - Excel) "MCheru" wrote in message ... Every cell in Column A starting in row 2 is filled with seven digit numbers. Often times each seven digit number will occur more than once. I want to create a macro that covers every cell in Columns A:B starting in row 2 so that if I have a seven digit number that appears for the first time in cell A5 a 1 will appear next to it in B5. If that same seven digit number appears again by chance in cell A25 a 2 will appear next to it in cell B25. Finally if that same seven digit number appears by chance in cell A39 then a 3 will appear next to it in cell B39. |
| All times are GMT +1. The time now is 05:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com