Hello,

This is a great forum and I appreciate all the help.

In a worksheet I have a ton of data. However in Column B I have the
transaction month of purchases. For most rows the month is the same but
there are times where it reflects the month prior.

In column A I would like to show the billing month for the line. The
question is how do I write a formula to look at the entire column and show
the month that appears most in column B and return this month to column A.

Thanks
Jim

There may be a better way, but this works.

Ctrl/Shift/Enter this in a cell in column A and it will return the month
which appears the most frequently in column B -- it assumes the months are
entered as text: "Apr" or "Sep" instead of 4/1/2009 or 9/14/2009, etc.
=TEXT(MATCH(MAX(COUNTIF(B1:B63,TEXT(ROW(1:12)&"/2000","mmm"))),COUNTIF(B1:B63,TEXT(ROW(1:12)&"/2000","mmm")),0)&"/2000","mmm")


"Jim" wrote in message
...
Hello,

This is a great forum and I appreciate all the help.

In a worksheet I have a ton of data. However in Column B I have the
transaction month of purchases. For most rows the month is the same but
there are times where it reflects the month prior.

In column A I would like to show the billing month for the line. The
question is how do I write a formula to look at the entire column and show
the month that appears most in column B and return this month to column A.

Thanks
Jim

You could try this array** formula:

=MODE(IF(ISNUMBER(B2:B100),MONTH(B2:B100)))

**confirm formula using Ctrl+shift+Enter, not just Enter.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jim" wrote:

Hello,

This is a great forum and I appreciate all the help.

In a worksheet I have a ton of data. However in Column B I have the
transaction month of purchases. For most rows the month is the same but
there are times where it reflects the month prior.

In column A I would like to show the billing month for the line. The
question is how do I write a formula to look at the entire column and show
the month that appears most in column B and return this month to column A.

Thanks
Jim

Bob,

This is a great formula. Thank you. I have faced one issue. Although
August is shown is about 90 lines, July in only four I am getting a return of
January from the formula.

The reason for this, I think, is that there are about 50 lines referring to
another page that are blank (I have a formula in the cell). Is there another
way to write the formula?

Thanks
Jim

"Bob Umlas" wrote:

There may be a better way, but this works.

Ctrl/Shift/Enter this in a cell in column A and it will return the month
which appears the most frequently in column B -- it assumes the months are
entered as text: "Apr" or "Sep" instead of 4/1/2009 or 9/14/2009, etc.
=TEXT(MATCH(MAX(COUNTIF(B1:B63,TEXT(ROW(1:12)&"/2000","mmm"))),COUNTIF(B1:B63,TEXT(ROW(1:12)&"/2000","mmm")),0)&"/2000","mmm")


"Jim" wrote in message
...
Hello,

This is a great forum and I appreciate all the help.

In a worksheet I have a ton of data. However in Column B I have the
transaction month of purchases. For most rows the month is the same but
there are times where it reflects the month prior.

In column A I would like to show the billing month for the line. The
question is how do I write a formula to look at the entire column and show
the month that appears most in column B and return this month to column A.

Thanks
Jim

Luke, I did enter as array, but received an error (#N/A)

any other suggestions - and thanks a bunch



"Luke M" wrote:

You could try this array** formula:

=MODE(IF(ISNUMBER(B2:B100),MONTH(B2:B100)))

**confirm formula using Ctrl+shift+Enter, not just Enter.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jim" wrote:

Hello,

This is a great forum and I appreciate all the help.

In a worksheet I have a ton of data. However in Column B I have the
transaction month of purchases. For most rows the month is the same but
there are times where it reflects the month prior.

In column A I would like to show the billing month for the line. The
question is how do I write a formula to look at the entire column and show
the month that appears most in column B and return this month to column A.

Thanks
Jim

I should also mention that the formula you are using is refering to a cell
that has an existing formula in it: =IF(G2=0,"",TEXT(G2,"mmmm"))

Does that make a difference?



"Luke M" wrote:

You could try this array** formula:

=MODE(IF(ISNUMBER(B2:B100),MONTH(B2:B100)))

**confirm formula using Ctrl+shift+Enter, not just Enter.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jim" wrote:

Hello,

This is a great forum and I appreciate all the help.

In a worksheet I have a ton of data. However in Column B I have the
transaction month of purchases. For most rows the month is the same but
there are times where it reflects the month prior.

In column A I would like to show the billing month for the line. The
question is how do I write a formula to look at the entire column and show
the month that appears most in column B and return this month to column A.

Thanks
Jim

Yes, it does make a difference. MODE will not work with text; it wants to
see a number.
--
David Biddulph

"Jim" wrote in message
...
I should also mention that the formula you are using is refering to a cell
that has an existing formula in it: =IF(G2=0,"",TEXT(G2,"mmmm"))

Does that make a difference?



"Luke M" wrote:

You could try this array** formula:

=MODE(IF(ISNUMBER(B2:B100),MONTH(B2:B100)))

**confirm formula using Ctrl+shift+Enter, not just Enter.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jim" wrote:

Hello,

This is a great forum and I appreciate all the help.

In a worksheet I have a ton of data. However in Column B I have the
transaction month of purchases. For most rows the month is the same
but
there are times where it reflects the month prior.

In column A I would like to show the billing month for the line. The
question is how do I write a formula to look at the entire column and
show
the month that appears most in column B and return this month to column
A.

Thanks
Jim

Replace the B2:B100 reference (which is text apparently) with G2:G100 (which
is where your dates are really at, it appears).
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jim" wrote:

I should also mention that the formula you are using is refering to a cell
that has an existing formula in it: =IF(G2=0,"",TEXT(G2,"mmmm"))

Does that make a difference?



"Luke M" wrote:

You could try this array** formula:

=MODE(IF(ISNUMBER(B2:B100),MONTH(B2:B100)))

**confirm formula using Ctrl+shift+Enter, not just Enter.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jim" wrote:

Hello,

This is a great forum and I appreciate all the help.

In a worksheet I have a ton of data. However in Column B I have the
transaction month of purchases. For most rows the month is the same but
there are times where it reflects the month prior.

In column A I would like to show the billing month for the line. The
question is how do I write a formula to look at the entire column and show
the month that appears most in column B and return this month to column A.

Thanks
Jim