CODEWITHSUNDEEP
excel
rhlchd
rhlchd

Reputation: 31

Excel increment serial number based on string match

Auto increment based on string match

How can I generate the next CST number automatically to be 1238 and 204 for VAT. I tried INDEX&MATCH, MAX, COUNTIF all throwing circular reference error.

CST & VAT is entered manually in random order.

Hope there is a formula. Unfortunately I don't know VBA. Thanks in advance.

Upvotes: 0

Views: 470

Answers (2)

Nitesh Halai
Nitesh Halai

Reputation: 927

Enter with Ctrl + Shift + Enter the following array formula in B8 and drag it down:

=IF(A8<>"",IF(A8="CST",INDEX($B$2:B7,MAX(IF($A$2:A7="CST",ROW($A$2:A7)-ROW(INDEX($A$2:A7,1,1))+1)),0)+1,INDEX($B$2:B7,MAX(IF($A$2:A7="VAT",ROW($A$2:A7)-ROW(INDEX($A$2:A7,1,1))+1)),0)+1),"")

EDIT: The above fomula has a flaw in that if you include anything other than CST or VAT in column A, it will return last VAT value + 1. Use the following to avoid this:

=IF(A12="CST",INDEX(B6:B11,MAX(IF(A6:A11="CST",ROW(A6:A11)-ROW(INDEX(A6:A11,1,1))+1)),0)+1,IF(A12="VAT",INDEX(B6:B11,MAX(IF(A6:A11="VAT",ROW(A6:A11)-ROW(INDEX(A6:A11,1,1))+1)),0)+1,""))

Upvotes: 1

areklipno
areklipno

Reputation: 528

In B4 formula will be:

=MAX(IF($A$2:$A3=A4;$B$2:$B3;0))+1

this is array formula and when you confirm this formula you must press ctrl+shift+Enter

Upvotes: 2

Related Questions