Find data with multiple criteria.

[premjeetgupta]

Dear Friends,
Attaching a sample file. Please help with formula which can find data which having least qty. with matching multiple criteria.


Thanks,

 

[bosco_yip]

Perhaps,

In F2, copied down :

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$12:A$22)/(B$12:B$22=A2)/(C$12:C$22=C2)/($F$12:$F$22=AGGREGATE(15,6,F$12:F$22/(F$12:F$22>0)/(B$12:B$22=A2)/(C$12:C$22=C2),1)),1)),"Not Available")

Regards
Bosco

 

[premjeetgupta]

Hi Bosco,
Thanks for your kind support. This complex formula works like a charm. But sorry to disturb you again seems I've not conveyed my requirement properly.

:- In attached excel Balance Qty. means I can assign WO. only times (Balance Qty.) against items of the above table.

:- And the above table should chose least balance qty. WO no. first then it should go for next available Qty.

For example:

IN&C COLLECTOR CARDS-WITH AT OF OADM/TML is having three WOs
WO No. Balance Qty.
I21/630031128 0
I21/630047206 44
I21/630050606 1

In above table has requirement of 2 Qty.

Then it should chose following WO first.
I21/630050606

Second.
I21/630047206

I'll try to explain more if you any further query.

Thanks Again...

 

[bosco_yip]

Please confirm your expected result in E6 (I21/630031128), I think should read as I21/630047206 (see below breakdown)

Since

IN&C PSS32 COLL. DWDM TER. NODE (OADM) have 2 orders (in C3 &C6)
WO No…............. Balance Qty.
I21/630031128…. 2
I21/630047206…. 13

Then,

1st WO will be I21/630031128 (in E3)

and

2nd WO will be I21/630047206 (in E6)

Regards
Bosco

 

[premjeetgupta]

Thanks Bosco for response.

IN&C PSS32 COLL. DWDM TER. NODE (OADM) have 2 orders (in C3 &C6)

WO No…............. Balance Qty.
I21/630031128…. 2

Then Both (E3 and E6) will be I21/630031128.