Here is a formula way.
The formula for the
Row # (cells A21 and J21) are array formula and must be entered with
CTRL-SHIFT-ENTER then drag down as needed.
Formulas in B21 and J21 just copy down and across.Change ranges to match your data. They can be put on separate worksheets.
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q |
1 | Material | Size | VT | Stor | Location | Qty | unit | | | | | | | | | | |
2 | 7317515 | 750 | VT | 400 | 19-11-1 | 5 | CS | | | | | | | | | | |
3 | 5986815 | 750 | VT | 400 | 19-11-2 | 8 | CS | | | | | | | | | | |
4 | 2836015 | 750 | VT | 400 | 19-11-3 | 9 | CS | | | | | | | | | | |
5 | 6548715 | 750 | 12 | 400 | 19-11-4 | 8 | CS | | | | | | | | | | |
6 | 1589714 | 1 | 12 | 400 | 19-12-1 | 5 | CS | | | | | | | | | | |
7 | 1113410 | 1.75 | 6 | 400 | 19-12-2 | 3 | CS | | | | | | | | | | |
8 | 395915 | 750 | 12 | 400 | 19-12-3 | 9 | CS | | | | | | | | | | |
9 | 7117326 | 375 | 12 | 400 | 19-12-4 | 9 | CS | | | | | | | | | | |
10 | 5553015 | 750 | VT | 400 | 19-13-1 | 5 | CS | | | | | | | | | | |
11 | 6250815 | 750 | VT | 400 | 19-13-2 | 4 | CS | | | | | | | | | | |
12 | 703510 | 1.75 | 6 | 400 | 19-13-3 | 7 | CS | | | | | | | | | | |
13 | 673815 | 750 | 12 | 400 | 19-13-4 | 7 | CS | | | | | | | | | | |
14 | 262710 | 1.75 | 6 | 400 | 19-14-1 | 5 | CS | | | | | | | | | | |
15 | 2197115 | 750 | 12 | 400 | 19-14-2 | 4 | CS | | | | | | | | | | |
16 | 10050648 | 750 | VT | 400 | 19-14-3 | 7 | CS | | | | | | | | | | |
17 | 10001796 | 3 | VT | 400 | 19-14-4 | 4 | CS | | | | | | | | | | |
18 | | | | | | | | | | | | | | | | | |
19 | Even #'s | | | | | | | | | ODD #'s | | | | | | | |
20 | Row # | Material | Size | VT | Stor | Location | Qty | unit | | Row # | Material | Size | VT | Stor | Location | Qty | unit |
21 | 5 | 1589714 | 1 | 12 | 400 | 19-12-1 | 5 | CS | | 1 | 7317515 | 750 | VT | 400 | 19-11-1 | 5 | CS |
22 | 6 | 1113410 | 1.75 | 6 | 400 | 19-12-2 | 3 | CS | | 2 | 5986815 | 750 | VT | 400 | 19-11-2 | 8 | CS |
23 | 7 | 395915 | 750 | 12 | 400 | 19-12-3 | 9 | CS | | 3 | 2836015 | 750 | VT | 400 | 19-11-3 | 9 | CS |
24 | 8 | 7117326 | 375 | 12 | 400 | 19-12-4 | 9 | CS | | 4 | 6548715 | 750 | 12 | 400 | 19-11-4 | 8 | CS |
25 | 13 | 262710 | 1.75 | 6 | 400 | 19-14-1 | 5 | CS | | 9 | 5553015 | 750 | VT | 400 | 19-13-1 | 5 | CS |
26 | 14 | 2197115 | 750 | 12 | 400 | 19-14-2 | 4 | CS | | 10 | 6250815 | 750 | VT | 400 | 19-13-2 | 4 | CS |
27 | 15 | 10050648 | 750 | VT | 400 | 19-14-3 | 7 | CS | | 11 | 703510 | 1.75 | 6 | 400 | 19-13-3 | 7 | CS |
28 | 16 | 10001796 | 3 | VT | 400 | 19-14-4 | 4 | CS | | 12 | 673815 | 750 | 12 | 400 | 19-13-4 | 7 | CS |
29 | | | | | | | | | | | | | | | | | |
<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:27px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>
Spreadsheet Formulas |
Cell | Formula | A21 | {=IFERROR(SMALL(IF(ISEVEN(MID($E$2:$E$17,5,1)),ROW($E$2:$E$17)-ROW($E$2)+1),ROWS($A$21:$A21)),"")} | B21 | =IF(A21="","",INDEX($A$2:$G$17,$A21,MATCH(B$20,$A$1:$G$1,0))) | J21 | {=IFERROR(SMALL(IF(ISODD(MID($E$2:$E$17,5,1)),ROW($E$2:$E$17)-ROW($E$2)+1),ROWS($J$3:J3)),"")} | K21 | =IF(J21="","",INDEX($A$2:$G$17,$J21,MATCH(A$1,$K$20:$Q$20,0))) |
<tbody>
</tbody> |
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER! |
<tbody>
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4