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.

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" <(E-Mail Removed)>
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.

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" <(E-Mail Removed)> wrote in message
news:740C9ED3-81FB-4741-B852-(E-Mail Removed)...
> 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.

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" <(E-Mail Removed)>
> 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.
>
>

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" <(E-Mail Removed)> wrote in message
> news:740C9ED3-81FB-4741-B852-(E-Mail Removed)...
> > 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.
>
>

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"
<(E-Mail Removed)>
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

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" <(E-Mail Removed)> wrote in message
news:E62BAC1A-744A-4195-B3E5-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:740C9ED3-81FB-4741-B852-(E-Mail Removed)...
>> > 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.
>>
>>

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"
> <(E-Mail Removed)>
> 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
>
>

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" <(E-Mail Removed)> wrote in message
> news:E62BAC1A-744A-4195-B3E5-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:740C9ED3-81FB-4741-B852-(E-Mail Removed)...
> >> > 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.
> >>
> >>
>
>