Forum Discussion
Auto Increment cell based on another Vaue
Hi all firstly i am no Excel expert so please treat me gently!!!!
I have inherited a big excel job were the finance division are enter large data into SQL thats being worked but by question here is
Column A contains data that relates to SQL Database
SAU
SEP
etc
Coumn B contains a 12 digit number that comes from existing records in the database but no it appears they want to enter and string in A
CRT
what i want to do in Column B is have a formula that says
If Cell Ax = 'CRT ' then Bx look for last CRT record and increment by 1
So supposing you have
CRT 100000
CRT 100001
If the user then enters CRT formula would be 1000002 🙂
thanks
I suggest to enter formula below in the first empty cell of column B (in my example B27). Please see attached file.
Formula searches for the last occurence of e.g. "BTP" in column A and looks up the corresponding value in column B (and increments by 1).
=XLOOKUP(A27,$A$1:$A26,$B$1:$B26,,0,-1)+1
In my example i have a small dataset but formula can be adapted to bigger ranges.
2 Replies
I suggest to enter formula below in the first empty cell of column B (in my example B27). Please see attached file.
Formula searches for the last occurence of e.g. "BTP" in column A and looks up the corresponding value in column B (and increments by 1).
=XLOOKUP(A27,$A$1:$A26,$B$1:$B26,,0,-1)+1
In my example i have a small dataset but formula can be adapted to bigger ranges.
- Many thanks it seems to work, i am actually now looking at some VBA Code.... 🙂
Appreciate the help and suggestion
