Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 18
Default Formula to Increment a number upon a match in an adjacent cell

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.


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 527
Default Formula to Increment a number upon a match in an adjacent cell

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

HTH, Peter A


"Nolene" wrote:

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.


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 527
Default Formula to Increment a number upon a match in an adjacent cell

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,"Ne w")

Peter

"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

HTH, Peter A


"Nolene" wrote:

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.


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 18
Default Formula to Increment a number upon a match in an adjacent cell

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.

"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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 18
Default Formula to Increment a number upon a match in an adjacent cell

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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 527
Default 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

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 18
Default Formula to Increment a number upon a match in an adjacent cell

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

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 527
Default Formula to Increment a number upon a match in an adjacent cell

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

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 18
Default Formula to Increment a number upon a match in an adjacent cell

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

"Billy Liddel" wrote:

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

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 527
Default Formula to Increment a number upon a match in an adjacent cell

Your welcome, thanks for the feedback

Peter

"Nolene" wrote:

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

"Billy Liddel" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formulas won't increment when dragging to adjacent cells? t2inc Excel Discussion (Misc queries) 3 April 10th 09 07:01 PM
Color Match Adjacent Cell DOUG ECKERT[_2_] Excel Discussion (Misc queries) 3 February 19th 08 06:14 PM
Formula to match 7 columns of non adjacent data [email protected] Excel Worksheet Functions 0 January 11th 08 12:11 AM
Formula for max number and the data adjacent to the cell Sasikiran Excel Discussion (Misc queries) 3 September 26th 07 02:48 PM
When data match, copy adjacent value to adjacent column slimbim Excel Worksheet Functions 2 November 8th 06 09:41 PM


All times are GMT +1. The time now is 09:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"