同一组值下取对应不同单号值 使得同一组值对应单号也相同

雪姣Jasmine

当B列为同一城市的时候，取第一个城市的单号加第二个城市的对应的非重复的A列单号的前三位及后三位，第一个单号和第二个第三个单号用“|”进行连接。
当B列只有一个城市的时候，则直接去A列的发货单号。

请老师们帮忙解决，谢谢。
-------------------------------------------
如果取同城市下，非重复出现单号前三位或后三位，有难度，也可以只取后三位。
如下：

hugeinfo

1\ 建立辅助列


2\结果

雪姣Jasmine

hugeinfo 发表于 2023-7-5 08:45
1\ 建立辅助列

2016版本，没有unique和filter函数。

hugeinfo

雪姣Jasmine 发表于 2023-7-5 09:06
2016版本，没有unique和filter函数。

那就给一火车
=TEXTJOIN("丨",,IFERROR(IF(IFERROR(IF(MATCH(IF($B$2:$B$13=B2,$A$2:$A$13,""),A:A,)=ROW($2:$13),ROW($2:$13),""),"")=MIN(IFERROR(IF(MATCH(IF($B$2:$B$13=B2,$A$2:$A$13,""),A:A,)=ROW($2:$13),ROW($2:$13),""),"")),INDEX(A:A,IFERROR(IF(MATCH(IF($B$2:$B$13=B2,$A$2:$A$13,""),A:A,)=ROW($2:$13),ROW($2:$13),""),"")),LEFT(INDEX(A:A,IFERROR(IF(MATCH(IF($B$2:$B$13=B2,$A$2:$A$13,""),A:A,)=ROW($2:$13),ROW($2:$13),""),"")),3)&RIGHT(INDEX(A:A,IFERROR(IF(MATCH(IF($B$2:$B$13=B2,$A$2:$A$13,""),A:A,)=ROW($2:$13),ROW($2:$13),""),"")),3)),""))

雪姣Jasmine

hugeinfo 发表于 2023-7-5 09:53
雪姣Jasmine 发表于 2023-7-5 09:06
2016版本，没有unique和filter函数。

我的D2单元格只能带出FRT23062877Z8，D3:D13都是空白。

hugeinfo

雪姣Jasmine 发表于 2023-7-5 10:26
我的D2单元格只能带出FRT23062877Z8，D3:D13都是空白。

=LEFT(CONCAT(IFERROR(IF(IFERROR(IF(MATCH(IF($B$2:$B$13=B2,$A$2:$A$13,""),A:A,)=ROW($2:$13),ROW($2:$13),""),"")=MIN(IFERROR(IF(MATCH(IF($B$2:$B$13=B2,$A$2:$A$13,""),A:A,)=ROW($2:$13),ROW($2:$13),""),"")),INDEX(A:A,IFERROR(IF(MATCH(IF($B$2:$B$13=B2,$A$2:$A$13,""),A:A,)=ROW($2:$13),ROW($2:$13),""),"")),LEFT(INDEX(A:A,IFERROR(IF(MATCH(IF($B$2:$B$13=B2,$A$2:$A$13,""),A:A,)=ROW($2:$13),ROW($2:$13),""),"")),3)&RIGHT(INDEX(A:A,IFERROR(IF(MATCH(IF($B$2:$B$13=B2,$A$2:$A$13,""),A:A,)=ROW($2:$13),ROW($2:$13),""),"")),3))&"丨","")),LEN(CONCAT(IFERROR(IF(IFERROR(IF(MATCH(IF($B$2:$B$13=B2,$A$2:$A$13,""),A:A,)=ROW($2:$13),ROW($2:$13),""),"")=MIN(IFERROR(IF(MATCH(IF($B$2:$B$13=B2,$A$2:$A$13,""),A:A,)=ROW($2:$13),ROW($2:$13),""),"")),INDEX(A:A,IFERROR(IF(MATCH(IF($B$2:$B$13=B2,$A$2:$A$13,""),A:A,)=ROW($2:$13),ROW($2:$13),""),"")),LEFT(INDEX(A:A,IFERROR(IF(MATCH(IF($B$2:$B$13=B2,$A$2:$A$13,""),A:A,)=ROW($2:$13),ROW($2:$13),""),"")),3)&RIGHT(INDEX(A:A,IFERROR(IF(MATCH(IF($B$2:$B$13=B2,$A$2:$A$13,""),A:A,)=ROW($2:$13),ROW($2:$13),""),"")),3))&"丨","")))-1)

雪姣Jasmine

hugeinfo 发表于 2023-7-5 11:03
=LEFT(CONCAT(IFERROR(IF(IFERROR(IF(MATCH(IF($B$2:$B$13=B2,$A$2:$A$13,""),A:A,)=ROW($2:$13),ROW($2: ...

我的D2单元格只能带出FRT23062877Z8，D3:D13都是#VALUE!。

橒♂蝣

=LET(x,FILTER($A$2:$A$13,$B$2:$B$13=B8),
y,IFERROR(UNIQUE(LEFT(INDEX(x,SEQUENCE(ROWS(x)-1,,2,1)),3)&RIGHT(INDEX(x,SEQUENCE(ROWS(x)-1,,2,1)),3)),""),
z,TEXTJOIN("|",1,INDEX(x,1),y),z
)

雪姣Jasmine

橒♂蝣 发表于 2023-7-5 12:34
=LET(x,FILTER($A$2:$A$13,$B$2:$B$13=B8),
y,IFERROR(UNIQUE(LEFT(INDEX(x,SEQUENCE(ROWS(x)-1,,2,1)),3) ...

公司16版本的。