Using criteria to create list of data avoiding duplicates in each order

[Preetpal]

Hi,

Trying to find an answer to this item I am working on.

I am trying to build 10 separate shopping orders which could vary from 5 - 10 items in each order.

I have a separate tab with the data of the items I can use to create the orders with but these are broken into three categories: Dry, Cold, Frozen
These three categories are being used to create the shopping order.

I have a table on another tab to allocate the split of the order, for example if there are 15 items in order 1:
5 can be Dry items
5 can be Cold items
5 can be Frozen items

What I am trying to do is to create the orders of shopping by referencing the category but to avoid having duplicates on any one order (so same item appears more than once in each order).

I am also trying to use as much of the sample data as I can.

I have uploaded a sample file which gives the gist of what I am trying to do.

The formulas I have used do the job of allocating items fine but I keep getting duplicates with the formula.

Formula i was trying to use was
=IF(B8="Ambient",INDEX('SKU file'!$A$2:$A$89,RANDBETWEEN(1,COUNTA('SKU file'!$A$2:$A$89)),1),IF(B8="Chill",INDEX('SKU file'!$D$2:$D$62,RANDBETWEEN(1,COUNTA('SKU file'!$D$2:$D$62)),1),IF(B8="Freezer",INDEX('SKU file'!$G$2:$G$9,RANDBETWEEN(1,COUNTA('SKU file'!$G$2:$G$9)),1),"")))

SKU file refers to the full file I was working on.

 

[eibi]

Greetings --- and welcome to the Forum!

I propose that the following formula will achieve Task 1 (allocate items from Sample tab) and Task 2 (avoid duplicates on a given order).

=INDEX(OFFSET('Sample items'!$A$2,,MATCH(Orders!B3,'Sample items'!$A$2:$F$2,0),COUNTIF('Sample items'!$A$2:$F$16,Orders!B3)),MOD(COUNTIF($B$3:B3,B3),COUNTIF('Sample items'!$A$2:$F$16,Orders!B3))+1)

If your Orders were listed vertically instead of horizontally, the same formula would also achieve Task 3 (use as much of the sample data as possible), I think.

See attached.