Help with handling large amounts of data - determine "MIN IF" ?

[Rayo K]

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

 

[Max]

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.