Search code examples
excelexcel-formulaexcel-2010

How to create a formula for a dynamic string in a column with dynamic sequence numbering

How can I construct the formula to get the below desired output.

enter image description here

Currently, the formula will give the current output with the sequence order numbering of the column F (Sub-processname), but the issue is when the Project type column row in between starts with "New" the sequence order numbering should start again from sequence number 1 followed by same sub-process name that is (Example in image :- pc3-1,pc5-1), in between if there is "Existing" with new sub process name then the number changes to 2 or 3 based on new sub process name(Example in image pc4-2, pc4 is there in first column that is Request code).

=IF(COUNTIF(F$2:F2,F2)=1,MAX(G$1:G1)+1,VLOOKUP(F2,F$1:G1,2,0))

The above formula is used in G column but that will only validate F column, but I need B column also should be included to get the correct dynamic sequence number, is it possible to achieve this through formula ?

Solution

  • If you are looking for a single-cell-formula-based solution, this crazy formula should do the trick:

    =A2&"-"&IF(COUNTIF($B$1:B2,"New")<>COUNTIF($B$1:B1,"New"),1,IFERROR(MID(INDEX(INDIRECT("R"&AGGREGATE(15,6,ROW($B$1:$B100)/($B$1:$B100="New"),COUNTIF($B$1:B2,"New"))&"C[0]:R[-1]C[0]",FALSE),MATCH(F2,INDIRECT("F"&AGGREGATE(15,6,ROW($B$1:$B100)/($B$1:$B100="New"),COUNTIF($B$1:B2,"New"))&":"&CELL("address",F1)),0)),FIND("-",INDEX(INDIRECT("R"&AGGREGATE(15,6,ROW($B$1:$B100)/($B$1:$B100="New"),COUNTIF($B$1:B2,"New"))&"C[0]:R[-1]C[0]",FALSE),MATCH(F2,INDIRECT("F"&AGGREGATE(15,6,ROW($B$1:$B100)/($B$1:$B100="New"),COUNTIF($B$1:B2,"New"))&":"&CELL("address",F1)),0)))+1,999),AGGREGATE(14,6,MID(INDIRECT("R"&AGGREGATE(15,6,ROW($B$1:$B100)/($B$1:$B100="New"),COUNTIF($B$1:B2,"New"))&"C[0]:R[-1]C[0]",FALSE),FIND("-",INDIRECT("R"&AGGREGATE(15,6,ROW($B$1:$B100)/($B$1:$B100="New"),COUNTIF($B$1:B2,"New"))&"C[0]:R[-1]C[0]",FALSE))+1,999)*1,1)+1))

    It covers a list from B1 to B100. If you want to increase the coverage, in the formula change the reference $B$1:$B100 accordingly.

    POST-COMMENTS EDIT

    Here is the formula updated accordingly to the comments:

    =A2&"-"&IF(COUNTIF($C$1:C2,"New")<>COUNTIF($C$1:C1,"New"),1,IFERROR(MID(INDEX(INDIRECT("R"&AGGREGATE(15,6,ROW($C$1:$C100)/($C$1:$C100="New"),COUNTIF($C$1:C2,"New"))&"C[0]:R[-1]C[0]",FALSE),MATCH(G2,INDIRECT("G"&AGGREGATE(15,6,ROW($C$1:$C100)/($C$1:$C100="New"),COUNTIF($C$1:C2,"New"))&":"&CELL("address",G1)),0)),FIND("-",INDEX(INDIRECT("R"&AGGREGATE(15,6,ROW($C$1:$C100)/($C$1:$C100="New"),COUNTIF($C$1:C2,"New"))&"C[0]:R[-1]C[0]",FALSE),MATCH(G2,INDIRECT("G"&AGGREGATE(15,6,ROW($C$1:$C100)/($C$1:$C100="New"),COUNTIF($C$1:C2,"New"))&":"&CELL("address",G1)),0)))+1,FIND(".",INDEX(INDIRECT("R"&AGGREGATE(15,6,ROW($C$1:$C100)/($C$1:$C100="New"),COUNTIF($C$1:C2,"New"))&"C[0]:R[-1]C[0]",FALSE),MATCH(G2,INDIRECT("G"&AGGREGATE(15,6,ROW($C$1:$C100)/($C$1:$C100="New"),COUNTIF($C$1:C2,"New"))&":"&CELL("address",G1)),0)))-FIND("-",INDEX(INDIRECT("R"&AGGREGATE(15,6,ROW($C$1:$C100)/($C$1:$C100="New"),COUNTIF($C$1:C2,"New"))&"C[0]:R[-1]C[0]",FALSE),MATCH(G2,INDIRECT("G"&AGGREGATE(15,6,ROW($C$1:$C100)/($C$1:$C100="New"),COUNTIF($C$1:C2,"New"))&":"&CELL("address",G1)),0)))-1),AGGREGATE(14,6,MID(INDIRECT("R"&AGGREGATE(15,6,ROW($C$1:$C100)/($C$1:$C100="New"),COUNTIF($C$1:C2,"New"))&"C[0]:R[-1]C[0]",FALSE),FIND("-",INDIRECT("R"&AGGREGATE(15,6,ROW($C$1:$C100)/($C$1:$C100="New"),COUNTIF($C$1:C2,"New"))&"C[0]:R[-1]C[0]",FALSE))+1,FIND(".",INDIRECT("R"&AGGREGATE(15,6,ROW($C$1:$C100)/($C$1:$C100="New"),COUNTIF($C$1:C2,"New"))&"C[0]:R[-1]C[0]",FALSE))-FIND("-",INDIRECT("R"&AGGREGATE(15,6,ROW($C$1:$C100)/($C$1:$C100="New"),COUNTIF($C$1:C2,"New"))&"C[0]:R[-1]C[0]",FALSE))-1)*1,1)+1))&"."&D2

    2nd POST-COMMENTS EDIT

    I can find no way to make a single formula to obtain the new objective. It can be achieved placing this formula in cell B2:

    =IF(COUNTIFS($C$1:C2,"New",$E$1:E2,E2)>0,A2&"-"&IF(C2="New",1,COUNTIF(INDIRECT("R"&AGGREGATE(15,6,ROW($C$1:$C$100)/($C$1:$C$100="New")/($E$1:$E$100=E2),COUNTIFS($C$1:C2,"New",$E$1:E2,E2))&"C"&RIF.COLONNA(E2)&":R[-1]C"&RIF.COLONNA(E2),FALSE),E2)+1)&"."&D2,H2)

    And this one (which is basically the previous one) in cell H2:

    =A2&"-"&IF(COUNTIF($C$1:C2,"New")<>COUNTIF($C$1:C1,"New"),1,IFERROR(MID(INDEX(INDIRECT("R"&AGGREGATE(15,6,ROW($C$1:$C$100)/($C$1:$C$100="New"),COUNTIF($C$1:C2,"New"))&"C[0]:R[-1]C[0]",FALSE),MATCH(G2,INDIRECT("G"&AGGREGATE(15,6,ROW($C$1:$C$100)/($C$1:$C$100="New"),COUNTIF($C$1:C2,"New"))&":"&CELL("address",G1)),0)),FIND("-",INDEX(INDIRECT("R"&AGGREGATE(15,6,ROW($C$1:$C$100)/($C$1:$C$100="New"),COUNTIF($C$1:C2,"New"))&"C[0]:R[-1]C[0]",FALSE),MATCH(G2,INDIRECT("G"&AGGREGATE(15,6,ROW($C$1:$C$100)/($C$1:$C$100="New"),COUNTIF($C$1:C2,"New"))&":"&CELL("address",G1)),0)))+1,FIND(".",INDEX(INDIRECT("R"&AGGREGATE(15,6,ROW($C$1:$C$100)/($C$1:$C$100="New"),COUNTIF($C$1:C2,"New"))&"C[0]:R[-1]C[0]",FALSE),MATCH(G2,INDIRECT("G"&AGGREGATE(15,6,ROW($C$1:$C$100)/($C$1:$C$100="New"),COUNTIF($C$1:C2,"New"))&":"&CELL("address",G1)),0)))-FIND("-",INDEX(INDIRECT("R"&AGGREGATE(15,6,ROW($C$1:$C$100)/($C$1:$C$100="New"),COUNTIF($C$1:C2,"New"))&"C[0]:R[-1]C[0]",FALSE),MATCH(G2,INDIRECT("G"&AGGREGATE(15,6,ROW($C$1:$C$100)/($C$1:$C$100="New"),COUNTIF($C$1:C2,"New"))&":"&CELL("address",G1)),0)))-1),AGGREGATE(14,6,MID(INDIRECT("R"&AGGREGATE(15,6,ROW($C$1:$C$100)/($C$1:$C$100="New"),COUNTIF($C$1:C2,"New"))&"C[0]:R[-1]C[0]",FALSE),FIND("-",INDIRECT("R"&AGGREGATE(15,6,ROW($C$1:$C$100)/($C$1:$C$100="New"),COUNTIF($C$1:C2,"New"))&"C[0]:R[-1]C[0]",FALSE))+1,FIND(".",INDIRECT("R"&AGGREGATE(15,6,ROW($C$1:$C$100)/($C$1:$C$100="New"),COUNTIF($C$1:C2,"New"))&"C[0]:R[-1]C[0]",FALSE))-FIND("-",INDIRECT("R"&AGGREGATE(15,6,ROW($C$1:$C$100)/($C$1:$C$100="New"),COUNTIF($C$1:C2,"New"))&"C[0]:R[-1]C[0]",FALSE))-1)*1,1)+1))&"."&D2