Nolene, sorry for the delay but I had to take my better half shoping.

I could not reproduce your should be column and decided that 09001 for each
new Item in column A should be should as 09001. The table below shows the
results compared with your list.

Concat # Number# Should be
CO-ER-01 09001 09001
GR-LG-03 09001 00000
CO-ER-01 09002 09002
GR-LG-03 09002 00002
LE-IS-01 09001 00000
CO-ER-01 09003 00003
GR-LG-03 09003 00003
LE-CO-06 09001 00000
CO-ER-01 09004 00004
LE-CO-01 09001 00000
CO-ER-01 09005 00005
GR-LG-03 09004 00006

The last number seems more accurate than yours?

If you can live with this the formula, entered normally in B3 is:

=IF(A3="","",IF(COUNTIF($A$2:$A3,$A3)=1,TEXT(0,"09 001")+0,IF(COUNTIF($A$2:$A3,A3)1,INDEX($B$2:$B3,M ATCH($A3,$A$2:$A3,0))+COUNTIF($A$2:$A3,A3)-1)))

HTH, Peter A



"Nolene" wrote:

This one doesn't seem to work at all, the first formula was closer. The
formula has the { } around it.

{=IF(ISBLANK(E3),"
",IF(SUMPRODUCT(--($A$2:$A3=$A3))-10,TEXT(MAX(VALUE($B$2:B2))+1,"00000"),B2))}

___A________B____
CO-ER-01.....09001......Manually entered this number
GR-LG-03......09001...<[Formula in B2]. Data already in A when formula
entered <-- s/b 00000 or NEW
CO-ER-01.....09002......Data already in A when formula dragged <-- correct
GR-LG-03.....09003.......Data already in A when formula dragged <--s/b 09002
(or 00002)
LE-IS-01.......09003......Data already in A when formula dragged <-- s/b
00000 or NEW
CO-ER-01.....09004......Data already in A when formula dragged <-- s/b 09003
GR-LG-03.....09005.......Data already in A when formula dragged <-- s/b
09004 (or 00003)
LE-CO-06.....09005.......Data already in A when formula dragged <-- s/b
00000 or NEW
CO-ER-01.....09006......Data already in A when formula dragged <-- s/b 09004
LE-CO-01.....09006......Data already in A when formula dragged <-- s/b 00000
or NEW
CO-ER-01.....09007......Data entered in A after formula <-- s/b 09005
GR-LG-03.....09008......Data entered in A after formula <-- s/b 06005 (or
00005)

(1) This is where data for concatenation formula in Col A comes from

I did figure out how to keep the cells from displaying "NEW" if nothing is
in column A ... since there is a formula in column A it isn't blank. I can
change that part to =IF(ISBLANK(E2)," ", ..... I tried this formula with this
and with =IF(A3=" ",.....

"Billy Liddel" wrote:

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