Formula to Increment a number upon a match in an adjacent cell
Try this for column B, enter with Ctrl + Shift + Enter then copy down.
=IF(A3="","",IF(SUMPRODUCT(--($A$2:$A3=$A3))-10,TEXT(MAX(VALUE($B$2:B2))+1,"00000"),B2))
Not sure how this leaves column A now?
Peter
"Nolene" wrote:
Oops, it only works once. If I add additional cells below it doesn't continue
to add. Here's what the cells might look like
__A___________B____
CO-ER-01 ....... 09001
GR-LG-03 ....... 09001
CO-ER-01 ....... 09002
CO-IS-01 ....... New (will be manually replaced with 09001)
CO-ER-01 ...... ????? <-- cell where formula is. I need it to return 09003.
This formula returns 09002 again.
[blank] .......... New <-- a concatenation/lookup formula will fill in cell
when data is entered into cells in columns D, E & F. Formula should search
all column A above, and if it comes to a match, stop and add 1 to the value
in the B-cell is, otherwise "New"
[blank] .......... New
[blank] .......... New
[blank] .......... New <-- I'd like these to stay blank until something
appears in column A.
etc.
"Billy Liddel" wrote:
Will this do?
=IF(ISNUMBER(MATCH(A2,$A$1:A2,0)),A2&"-"&INDIRECT(ADDRESS(MATCH(A2,$A$1:A2,0),2,4))+1,"Ne w")
in c2 and copied down
|