This one has me stumped. Here is a sample of my data. There are actually on
the order of 10^5 lines. This set was ordered by "OrderNum" but the normal
order is by "SeqNumAAE". I hope the data displays well enough to see.

Anyway, what I need is to determine the initial quantity. This would
normally be the "OffQty" of "MachNum" 100. Except when #100 isn't used. Then
it would be the quantity of: "QtyOff" for the machine matching MIN(Pass#)
for that "OrderNum". So, how do I get that? I am trying OFFSET and MATCH but
haven't had any luck. Please help?


OrderNum SeqNumAAE RunDateSQL FileNum MachNum FedInQty OffQty FedInNumOut Length Width Pass #
020188A 571132 3/13/2007 190980 100 0 718 1 51.75 40.25 0
020188A 571456 3/14/2007 190980 118 0 0 1 51.75 40.25 1
020188A 571501 3/14/2007 190980 118 665 665 1 51.75 40.25 1
020188A 571603 3/15/2007 190980 125 643 643 1 51.75 40.25 2
020188A 573951 3/27/2007 190980 100 0 401 1 51.75 40.25 0
020188A 574040 3/28/2007 190980 118 406 406 1 51.75 40.25 1
020188A 574108 3/28/2007 190980 125 400 400 1 51.75 40.25 2
024278A 571974 3/16/2007 190804 100 0 11212 4 44.125 18.1875 0
024278A 571988 3/16/2007 190804 131 11110 11110 1 44.125 18.1875 3
024335A 570730 3/12/2007 180996 100 0 1710 2 48.6875 41.875 0
024335A 571163 3/13/2007 180996 122 1673 3346 2 48.6875 41.875 2
048297A 570755 3/12/2007 186293 118 0 0 1 33.6875 34.125 1
048297A 570801 3/12/2007 186293 118 5420 5420 1 33.6875 34.125 1
048297A 570900 3/12/2007 186293 125 3375 27000 8 33.6875 34.125 2
048297A 570904 3/13/2007 186293 125 1925 15400 8 33.6875 34.125 2
048342A 570874 3/12/2007 186514 118 1700 1700 1 43.25 22.0625 1
048342A 570903 3/13/2007 186514 118 2651 2651 1 43.25 22.0625 1
048342A 571007 3/13/2007 186514 124 5060 20240 4 43.25 22.0625 2
055708A 569682 3/6/2007 94290 131 595 595 1 39.125 14.375 3
063579A 568861 3/1/2007 189727 100 0 1655 1 45.625 15.25 0

One crack at this ..

Assumptions:
Col A = OrderNum, Col E = MachNum, Col G = OffQty*, col K = Pass #
data from row2 to row10000**
*QtyOff is presumed to mean the same as: OffQty
**my xl2003 cannot hold 10^5 rows (max rows for me is 65k)
(just change the range accordingly to suit the row capacity in your version)

Set the calc mode to manual first (via Tools Options Calculation tab)

Then put in L2, array-enter the formula by pressing CTRL+SHIFT+ENTER:

=IF(A2="","",IF(ISNA(INDEX($G$2:$G$10000,MATCH(1,( $A$2:$A$10000=A2)*($E$2:$E$10000=100),0))),INDEX($ G$2:$G$10000,MATCH(MIN(IF(($A$2:$A$10000=A2)*($E$2 :$E$10000<100),$K$2:$K$10000)),IF(($A$2:$A$10000= A2)*($E$2:$E$10000<100),$K$2:$K$10000),0)),INDEX( $G$2:$G$10000,MATCH(1,($A$2:$A$10000=A2)*($E$2:$E$ 10000=100),0))))

Copy L2 down all the way. Press F9 to calc. Col L should return the required
results from col G (OffQty) for each OrderNum in col A according to your
specs.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Rayo K" wrote:
This one has me stumped. Here is a sample of my data. There are actually on
the order of 10^5 lines. This set was ordered by "OrderNum" but the normal
order is by "SeqNumAAE". I hope the data displays well enough to see.

Anyway, what I need is to determine the initial quantity. This would
normally be the "OffQty" of "MachNum" 100. Except when #100 isn't used. Then
it would be the quantity of: "QtyOff" for the machine matching MIN(Pass#)
for that "OrderNum". So, how do I get that? I am trying OFFSET and MATCH but
haven't had any luck. Please help?


OrderNum SeqNumAAE RunDateSQL FileNum MachNum FedInQty OffQty FedInNumOut Length Width Pass #
020188A 571132 3/13/2007 190980 100 0 718 1 51.75 40.25 0
020188A 571456 3/14/2007 190980 118 0 0 1 51.75 40.25 1
020188A 571501 3/14/2007 190980 118 665 665 1 51.75 40.25 1
020188A 571603 3/15/2007 190980 125 643 643 1 51.75 40.25 2
020188A 573951 3/27/2007 190980 100 0 401 1 51.75 40.25 0
020188A 574040 3/28/2007 190980 118 406 406 1 51.75 40.25 1
020188A 574108 3/28/2007 190980 125 400 400 1 51.75 40.25 2
024278A 571974 3/16/2007 190804 100 0 11212 4 44.125 18.1875 0
024278A 571988 3/16/2007 190804 131 11110 11110 1 44.125 18.1875 3
024335A 570730 3/12/2007 180996 100 0 1710 2 48.6875 41.875 0
024335A 571163 3/13/2007 180996 122 1673 3346 2 48.6875 41.875 2
048297A 570755 3/12/2007 186293 118 0 0 1 33.6875 34.125 1
048297A 570801 3/12/2007 186293 118 5420 5420 1 33.6875 34.125 1
048297A 570900 3/12/2007 186293 125 3375 27000 8 33.6875 34.125 2
048297A 570904 3/13/2007 186293 125 1925 15400 8 33.6875 34.125 2
048342A 570874 3/12/2007 186514 118 1700 1700 1 43.25 22.0625 1
048342A 570903 3/13/2007 186514 118 2651 2651 1 43.25 22.0625 1
048342A 571007 3/13/2007 186514 124 5060 20240 4 43.25 22.0625 2
055708A 569682 3/6/2007 94290 131 595 595 1 39.125 14.375 3
063579A 568861 3/1/2007 189727 100 0 1655 1 45.625 15.25 0