Excel Formula for creating a Batch Number

Hi,

I have to create a continuous batch number for every product (for a Given Date)
- i.e. All Receipts of One Product in Same Day is ONE Batch .

Example and conditions are shown below.
- Result in Column 'C'
- Guideline is in Column 'D'


Please help me to create a formula or macro.
Batch Number.png

For Column C, put in C2 and copied down

=DAY(B2)

For COlumn D is difficult with formula because its merged column

Hi,

a) What I need a batch number. But not the day.
One Batch Number for One Product
b) The output expected is entered manually in COLUMN 'C' . But need a formula for it.

Please refer to the Excel attached for reference.
http://www.excelforum.com/attachment...1&d=1460444731

Regards,

B.Srinivasa Rao

Hi,

a) Please refer to the Excel attached for reference.
http://www.excelforum.com/attachment...1&d=1460444731

What I need a batch number.
One Batch Number for One Product

b) The output expected is entered manually in COLUMN 'C' . But need a formula for it.

Thanks in advance.

B.Srinivasa Rao

Try this in Cell C2:

=SUMPRODUCT(($A$2:A2=A2)/COUNTIFS($A$2:A2,$A$2:A2,$B$2:B2,$B$2:B2))

Copy down

Originally Posted by Phuocam

Try this in Cell C2:

=SUMPRODUCT(($A$2:A2=A2)/COUNTIFS($A$2:A2,$A$2:A2,$B$2:B2,$B$2:B2))

Copy down

THANK YOU.

The formula worked well.

Originally Posted by seenai

THANK YOU.
The formula worked well.

You're welcome.