1. Define
Lrow in Name Manager as referring to:
=MATCH(9.99999999999999E+307,Sheet1!$B:$B)
2. Define
Code in Name Manager as referring to:
=Sheet1!$A$2:INDEX(Sheet1!$A:$A,Lrow)
3. Define
Order in Name Manager as referring to:
=Sheet1!$B$2:INDEX(Sheet1!$B:$B,Lrow)
4. Define
Ivec in Name Manager as referring to:
=ROW(Order)-ROW(INDEX(Order,1,1))+1
Adjust the sheet name in the above formulas if needed.
Sheet2 (the processing sheet)
| Row\Col | A | B |
1 | PT | 4 |
2 | MSW | order list |
3 | MSP | 10073301 |
4 | WCP | 10073302 |
5 | | 10073304 |
6 | | 10073305 |
7 | | |
8 | | |
<tbody>
</tbody>
5. In B1 of Sheet2 control+shift+enter, not just enter:
=SUM(IF(ISNUMBER(MATCH(Code,$A$1:$A$4,0)),1))
6. In B3 of Sheet2 control+shift+enter, not just enter, and copy down:
=IF(ROWS($B$4:B4)>$B$1,"",INDEX(Order,SMALL(IF(ISNUMBER(MATCH(Code,$A$1:$A$4,0)),Ivec),ROWS($B$4:B4))))
7. If you would like to know which code exactly is associated with each order (not shown in the above exhibit), in C3 just enter and copy down:
=IF($B3="","",INDEX(Code,MATCH($B3,Order,0)))
