Auto Numbering based on 2 columns value.

[bentxoxo]

Hi All,

Can someone please help me on below formula.

I am trying to add +1 if order number and store number (any one) value are different but if both values are same just use above number.

=IF(OR(C3=C2, B3=B2), A2, IF(OR(C3<>C2, B3<>B2), A2+1))

RA NAME PO
4525 ABC 2001
4525 ABC 2001
4526 DEF 3045
4527 DEF 3048
4528 GHI 1908
4527 JKL 1702
4527 JKL 1702
4528 MNO 1909
4528 MNO 1909
4528 MNO 1909
4528 MNO 1909

Cheers,

 

[mohadin]

[TABLE="width: 216"]
<colgroup><col width="72" span="3" style="width:54pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 72"]RA[/TD]
[TD="class: xl66, width: 72"]NAME[/TD]
[TD="class: xl67, width: 72"]PO[/TD]
[/TR]
[TR]
[TD="class: xl68"]4525[/TD]
[TD="class: xl64"]ABC[/TD]
[TD="class: xl69"]2001[/TD]
[/TR]
[TR]
[TD="class: xl68"]4525[/TD]
[TD="class: xl64"]ABC[/TD]
[TD="class: xl69"]2001[/TD]
[/TR]
[TR]
[TD="class: xl68"]4526[/TD]
[TD="class: xl64"]DEF[/TD]
[TD="class: xl69"]3045[/TD]
[/TR]
[TR]
[TD="class: xl68"]4527[/TD]
[TD="class: xl64"]DEF[/TD]
[TD="class: xl69"]3048[/TD]
[/TR]
[TR]
[TD="class: xl68"]4528[/TD]
[TD="class: xl64"]GHI[/TD]
[TD="class: xl69"]1908[/TD]
[/TR]
[TR]
[TD="class: xl68"]4529[/TD]
[TD="class: xl64"]JKL[/TD]
[TD="class: xl69"]1702[/TD]
[/TR]
[TR]
[TD="class: xl68"]4529[/TD]
[TD="class: xl64"]JKL[/TD]
[TD="class: xl69"]1702[/TD]
[/TR]
[TR]
[TD="class: xl68"]4530[/TD]
[TD="class: xl64"]MNO[/TD]
[TD="class: xl69"]1909[/TD]
[/TR]
[TR]
[TD="class: xl68"]4530[/TD]
[TD="class: xl64"]MNO[/TD]
[TD="class: xl69"]1909[/TD]
[/TR]
[TR]
[TD="class: xl68"]4530[/TD]
[TD="class: xl64"]MNO[/TD]
[TD="class: xl69"]1909[/TD]
[/TR]
[TR]
[TD="class: xl70"]4530[/TD]
[TD="class: xl71"]MNO[/TD]
[TD="class: xl72"]1909[/TD]
[/TR]
</tbody>[/TABLE]
A3=if(and(c3=c2, b3=b2), a2, if(or(c3<>c2, b3<>b2),a2+1))

 

[Nishant94]

Book1
	A	B	C
1	RA N	AME	PO
2	4525	ABC	2001
3	4525	ABC	2001
4	4526	DEF	3045
5	4527	DEF	3048
6	4528	GHI	1908
7	4529	JKL	1702
8	4529	JKL	1702
9	4530	MNO	1909
10	4530	MNO	1909
11	4530	MNO	1909
12	4530	MNO	1909
Sheet1

Cell Formulas
Range	Formula
A3	=IF(COUNTIFS(B$2:B3,B3,C$2:C3,C3)-1,A2,A2+1)

 

[bentxoxo]

Hi Mohadin, Nishant94,

Thank you for your reply.
@Nishant94, Just a small change if possible! Company name always be there which is column "B" but some time if I don't have PO number. So if PO number column "C" is empty it should add +1 because company name is not matching with the last one. Please see below.

RA NAME PO RESULT
4525 ABC 2001 4525
4525 ABC 2001 4525
4526 DEF 3045 4526
4527 DEF 3048 4527
4528 GHI 1908 4528
4529 JKL 1702 4529
4529 JKL 1702 4529
4530 MNO 1909 4530
4530 MNO 1909 4530
4530 MNO 1909 4530
4530 MNO 1909 4530
4530 PQR 4531
4531 STU B12345 4532
4531 STU B12345 4532
4532 VWX I443105 4533
4532 VWX I443105 4533
4533 YZA A930305 4534
4534 YZA 1909 4535
4534 BCD 4536
4535 BCD G45841 4537
4536 STU X984532 4538

Thanks in advance.