在Sheet2工作表的A2输入公式右拉再下拉,这是数组公式,按三键CTRL+SHIFT+回车。
=INDEX(Sheet1!A:A,SMALL(IF(MATCH(Sheet1!$A$2:$A$999&Sheet1!$B$2:$B$999&Sheet1!$C$2:$C$999&Sheet1!$D$2:$D$999&Sheet1!$E$2:$E$999,Sheet1!$A$2:$A$999&Sheet1!$B$2:$B$999&Sheet1!$C$2:$C$999&Sheet1!$D$2:$D$999&Sheet1!$E$2:$E$999,)=ROW($1:$998),ROW($2:$999),4^8),ROW(A1)))&""
在Sheet2!F2输入公式下拉:
=IF(A2="","",SUMPRODUCT(N(A2&B2&(--C2)&D2&(--E2)=Sheet1!$A$2:$A$999&Sheet1!$B$2:$B$999&Sheet1!$C$2:$C$999&Sheet1!$D$2:$D$999&Sheet1!$E$2:$E$999)))
![]()
我上传附件上来,看看是否符合要求吧。
![]()