Auto Numbering based on 2 columns value.

bentxoxo

New Member
Joined
Jan 13, 2016
Messages
29
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,
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
RANAMEPO
4525ABC2001
4525ABC2001
4526DEF3045
4527DEF3048
4528GHI1908
4529JKL1702
4529JKL1702
4530MNO1909
4530MNO1909
4530MNO1909
4530MNO1909

<colgroup><col width="72" span="3" style="width:54pt"> </colgroup><tbody>
</tbody>
A3=if(and(c3=c2, b3=b2), a2, if(or(c3<>c2, b3<>b2),a2+1))
 

Nishant94

Well-known Member
Joined
May 8, 2015
Messages
508
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="color: #333333;;">RA N</td><td style=";">AME</td><td style=";">PO</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">4525</td><td style=";">ABC</td><td style="text-align: right;;">2001</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">4525</td><td style=";">ABC</td><td style="text-align: right;;">2001</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">4526</td><td style=";">DEF</td><td style="text-align: right;;">3045</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">4527</td><td style=";">DEF</td><td style="text-align: right;;">3048</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">4528</td><td style=";">GHI</td><td style="text-align: right;;">1908</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">4529</td><td style=";">JKL</td><td style="text-align: right;;">1702</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">4529</td><td style=";">JKL</td><td style="text-align: right;;">1702</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">4530</td><td style=";">MNO</td><td style="text-align: right;;">1909</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">4530</td><td style=";">MNO</td><td style="text-align: right;;">1909</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">4530</td><td style=";">MNO</td><td style="text-align: right;;">1909</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">4530</td><td style=";">MNO</td><td style="text-align: right;;">1909</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A3</th><td style="text-align:left">=IF(<font color="Blue">COUNTIFS(<font color="Red">B$2:B3,B3,C$2:C3,C3</font>)-1,A2,A2+1</font>)</td></tr></tbody></table></td></tr></table><br />
 

bentxoxo

New Member
Joined
Jan 13, 2016
Messages
29
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,367
Messages
5,468,208
Members
406,571
Latest member
carrie1994

This Week's Hot Topics

Top