如果你正在寻找一个基于单单元格公式的解决方案，这个疯狂的公式应该可以做到：

=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))

它覆盖了从B1到B100的列表。如果你想增加覆盖范围，在公式中相应地改变引用$B$1:$B100。

评论后编辑

以下是根据注解更新的公式：

=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