Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulas won't increment when dragging to adjacent cells? | Excel Discussion (Misc queries) | |||
Color Match Adjacent Cell | Excel Discussion (Misc queries) | |||
Formula to match 7 columns of non adjacent data | Excel Worksheet Functions | |||
Formula for max number and the data adjacent to the cell | Excel Discussion (Misc queries) | |||
When data match, copy adjacent value to adjacent column | Excel Worksheet Functions |