Formula to Increment a number upon a match in an adjacent cell

[Nolene]

I need to create a block numbering scheme, when the blocks do not necessarily
fall one after the other. How would I create a formula for the following:

Look at the value in the cell to the left (in column A, which contains a
concatenation formula-the value will be in the format AA-AA-##), compare that
value with all the other values above it in column A. When I find the first
match (going from the bottom up), stop and look at the adjacent number in
column B and add 1 to it. If there is no match, display the word NEW or leave
blank or even an error would be OK.

I'm using Excel 2003

Thanks in advance for any help I can get.

 

[Billy Liddel]

Will this do?

=IF(ISNUMBER(MATCH(A2,$A$1:A2,0)),A2&"-"&INDIRECT(ADDRESS(MATCH(A2,$A$1:A2,0),2,4))+1,"New")

in c2 and copied down

HTH, Peter A

 

[Billy Liddel]

Oh dear! I should have poseted A2=

=IF(ISNUMBER(MATCH(A2,$A$1:A1,0)),A2&"-"&INDIRECT(ADDRESS(MATCH(A2,$A$1:A1,0),2,4))+1,"New")

Peter

 

[Nolene]

Yes it does, thanks. How would I modify it to leave the cell blank if A2 is
blank. I tried adding =IF(ISBLANK(A2),"",IF(ISNUMBER ...)), but that didn't
work.

 

[Nolene]

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]

Try this for column B, enter with Ctrl + Shift + Enter then copy down.

=IF(A3="","",IF(SUMPRODUCT(--($A$2:$A3=$A3))-1>0,TEXT(MAX(VALUE($B$2:B2))+1,"00000"),B2))

Not sure how this leaves column A now?
Peter

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.

Will this do?

=IF(ISNUMBER(MATCH(A2,$A$1:A2,0)),A2&"-"&INDIRECT(ADDRESS(MATCH(A2,$A$1:A2,0),2,4))+1,"New")

in c2 and copied down

 

[Nolene]

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))-1>0,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=" ",.....

Try this for column B, enter with Ctrl + Shift + Enter then copy down.

=IF(A3="","",IF(SUMPRODUCT(--($A$2:$A3=$A3))-1>0,TEXT(MAX(VALUE($B$2:B2))+1,"00000"),B2))

Not sure how this leaves column A now?
Peter

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.

Will this do?

=IF(ISNUMBER(MATCH(A2,$A$1:A2,0)),A2&"-"&INDIRECT(ADDRESS(MATCH(A2,$A$1:A2,0),2,4))+1,"New")

in c2 and copied down

 

[Billy Liddel]

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,"09001")+0,IF(COUNTIF($A$2:$A3,A3)>1,INDEX($B$2:$B3,MATCH($A3,$A$2:$A3,0))+COUNTIF($A$2:$A3,A3)-1)))

HTH, Peter A

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))-1>0,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=" ",.....

Try this for column B, enter with Ctrl + Shift + Enter then copy down.

=IF(A3="","",IF(SUMPRODUCT(--($A$2:$A3=$A3))-1>0,TEXT(MAX(VALUE($B$2:B2))+1,"00000"),B2))

Not sure how this leaves column A now?
Peter

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.


:

Will this do?

=IF(ISNUMBER(MATCH(A2,$A$1:A2,0)),A2&"-"&INDIRECT(ADDRESS(MATCH(A2,$A$1:A2,0),2,4))+1,"New")

in c2 and copied down

 

[Nolene]

By George, I think you've got it!! Thanks so much for all your help.

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,"09001")+0,IF(COUNTIF($A$2:$A3,A3)>1,INDEX($B$2:$B3,MATCH($A3,$A$2:$A3,0))+COUNTIF($A$2:$A3,A3)-1)))

HTH, Peter A

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))-1>0,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=" ",.....

Try this for column B, enter with Ctrl + Shift + Enter then copy down.

=IF(A3="","",IF(SUMPRODUCT(--($A$2:$A3=$A3))-1>0,TEXT(MAX(VALUE($B$2:B2))+1,"00000"),B2))

Not sure how this leaves column A now?
Peter

:

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.


:

Will this do?

=IF(ISNUMBER(MATCH(A2,$A$1:A2,0)),A2&"-"&INDIRECT(ADDRESS(MATCH(A2,$A$1:A2,0),2,4))+1,"New")

in c2 and copied down

 

[Billy Liddel]

Your welcome, thanks for the feedback

Peter

By George, I think you've got it!! Thanks so much for all your help.

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,"09001")+0,IF(COUNTIF($A$2:$A3,A3)>1,INDEX($B$2:$B3,MATCH($A3,$A$2:$A3,0))+COUNTIF($A$2:$A3,A3)-1)))

HTH, Peter A

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))-1>0,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=" ",.....

:

Try this for column B, enter with Ctrl + Shift + Enter then copy down.

=IF(A3="","",IF(SUMPRODUCT(--($A$2:$A3=$A3))-1>0,TEXT(MAX(VALUE($B$2:B2))+1,"00000"),B2))

Not sure how this leaves column A now?
Peter

:

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.


:

Will this do?

=IF(ISNUMBER(MATCH(A2,$A$1:A2,0)),A2&"-"&INDIRECT(ADDRESS(MATCH(A2,$A$1:A2,0),2,4))+1,"New")

in c2 and copied down