Counting Macro

[MCheru]

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.

 

[Jim Cone]

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" <[email protected]>
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.

 

[Rick Rothstein]

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))

 

[MCheru]

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.

 

[MCheru]

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?

 

[Jim Cone]

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"
<[email protected]>
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.

 

[Rick Rothstein]

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.

 

[MCheru]

I see. My apologies. I can see where I might not have been clear. However
thank you very much for you're help!

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"
<[email protected]>
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.

 

[MCheru]

Fascinating and very cool! Thanks for helping me to understand this formula!
I appreciate you're help.