| Home |
| Search |
| Today's Posts |
|
#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 |
| 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 | |||
Threaded Mode
