• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using formula to create list of values from set data without duplicates

P

Preetpal

New Member
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.
 

Attachments

  • create list with no duplicates using criteria.xlsm
    10.5 KB · Views: 7
Hi Preetpal ,

See the attached file for a simple method ; the problem is that this does not avoid duplicates , but when ever you see duplicates , you can press the F9 key to generate another combination of items , which may or may not contain duplicates.

Since all the orders are in the same worksheet tab , pressing the F9 key will regenerate all the orders ; by placing each order in its own tab , you can go to the specific tab which has duplicates and press the F9 key to regenerate only the order on that tab.

Secondly , it is difficult to say whether this will use all of the items in the lists , though in the long run , it should be so.

If you can upload a workbook with real-life data , we can see how serious the problem of duplicates is.

Narayan
 

Attachments

  • create list with no duplicates using criteria.xlsm
    10.9 KB · Views: 7
1] Define Name :

Name : Cold
Range : ='Sample items'!$F$2:$F$11

Name : Dry
Range : ='Sample items'!$C$2:$C$16

Name : Frozen
Range : ='Sample items'!$I$2:$I$6

Name : INDEX1
Range : ='Sample items'!$A$2:$A$16

Name : INDEX2
Range : ='Sample items'!$D$2:$D$11

Name : INDEX3
Range : ='Sample items'!$G$2:$G$6

2] Sheet : "Sample items" in A2, array formula ( Confirm entered with SHIFT+CTRL+ENTER instead of just ENTER) copy down :

=IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:"&COUNTA(B$2:B$16))),A$1:A1,)),ROW(INDIRECT("1:"&COUNTA(B$2:B$16)))),RANDBETWEEN(1,COUNTA(B$2:B$16)-ROWS(A$1:A1)+1)),"")

Then, copy to D2 an G2.

All copy down.

3] Sheet : "Orders" in C3, formula copy down :

=CHOOSE(MATCH(B3,{"Dry";"Cold";"Frozen"},0),INDEX(Dry,MATCH(COUNTIF(E$3:O$7,B3)+COUNTIF(B$3:B3,B3),INDEX1,0)),INDEX(Cold,MATCH(COUNTIF(E$3:O$7,B3)+COUNTIF(B$3:B3,B3),Index2,0)),INDEX(Frozen,MATCH(COUNTIF(E$3:O$7,B3)+COUNTIF(B$3:B3,B3),INDEX3,0)))

Then, copy to F3, I3, L3 and O3.

All copy down.

Regards
 

Attachments

  • Copy of create list with no duplicates using criteria.xlsm
    13.5 KB · Views: 2
Using INDIRECT() can shortern the formula.

1] Define Name :
Name : ColdA
Range : ='Sample items'!$F$2:$F$11

Name : DryA
Range : ='Sample items'!$C$2:$C$16

Name : FrozenA
Range : ='Sample items'!$I$2:$I$6

Name : DryB
Range : ='Sample items'!$A$2:$A$16

Name : ColdB
Range : ='Sample items'!$D$2:$D$11

Name : FrozenB
Range : ='Sample items'!$G$2:$G$6

2] Sheet : "Sample items" in A2, array formula ( Confirm entered with SHIFT+CTRL+ENTER instead of just ENTER) copy down :

=IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:"&COUNTA(B$2:B$17))),A$1:A1,)),ROW(INDIRECT("1:"&COUNTA(B$2:B$17)))),RANDBETWEEN(1,COUNTA(B$2:B$17)-ROWS(A$1:A1)+1)),"")

Then, copy to D2 and G2.

All copy down.

3] Sheet : "Orders" in C3, formula copy down :

=INDEX(INDIRECT(B3&"A"),MATCH(COUNTIF(E$3:O$7,B3)+COUNTIF(B$3:B3,B3),INDIRECT(B3&"B"),0))

Then, copy to F3, I3, L3 and O3.

All copy down.

Regards
 

Attachments

  • Copy of create list with no duplicates using criteria A.xlsm
    13.3 KB · Views: 1
Hi to all!

Another option in the file. Blessings!
 

Attachments

  • Copy of create list with no duplicates using criteria.xlsm
    13.5 KB · Views: 8

Attachments

  • preetpal_chandooreply.xlsm
    54.8 KB · Views: 2
Hi bosco, thanks for getting back to me with this.

Gave it a go but I think I have an issue with the number of orders I try to replicate making the countif part of the formula returning a large number to lookup against the sample data which is not big enough to cover this?

Maybe I need to adjust this somehow to stop the formula getting broken when doing the countif part?

I have attached the file for you to see. Hope it makes sense.




bosco_yip said:
1] Define Name :

Name : Cold
Range : ='Sample items'!$F$2:$F$11

Name : Dry
Range : ='Sample items'!$C$2:$C$16

Name : Frozen
Range : ='Sample items'!$I$2:$I$6

Name : INDEX1
Range : ='Sample items'!$A$2:$A$16

Name : INDEX2
Range : ='Sample items'!$D$2:$D$11

Name : INDEX3
Range : ='Sample items'!$G$2:$G$6

2] Sheet : "Sample items" in A2, array formula ( Confirm entered with SHIFT+CTRL+ENTER instead of just ENTER) copy down :

=IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:"&COUNTA(B$2:B$16))),A$1:A1,)),ROW(INDIRECT("1:"&COUNTA(B$2:B$16)))),RANDBETWEEN(1,COUNTA(B$2:B$16)-ROWS(A$1:A1)+1)),"")

Then, copy to D2 an G2.

All copy down.

3] Sheet : "Orders" in C3, formula copy down :

=CHOOSE(MATCH(B3,{"Dry";"Cold";"Frozen"},0),INDEX(Dry,MATCH(COUNTIF(E$3:O$7,B3)+COUNTIF(B$3:B3,B3),INDEX1,0)),INDEX(Cold,MATCH(COUNTIF(E$3:O$7,B3)+COUNTIF(B$3:B3,B3),Index2,0)),INDEX(Frozen,MATCH(COUNTIF(E$3:O$7,B3)+COUNTIF(B$3:B3,B3),INDEX3,0)))

Then, copy to F3, I3, L3 and O3.

All copy down.

Regards
Click to expand...
 

Attachments

  • chandooreply2.xlsm
    32.5 KB · Views: 4
Preetpal said:
Hi bosco, thanks for getting back to me with this.......I have attached the file for you to see...........
Click to expand...

Attached herewith revised file.

1] Define Name :

Name : Ambient
Range : ='Sample items'!$C$2:$C$89

Name : Chill
Range : ='Sample items'!$F$2:$F$62

Name : Freezer
Range : ='Sample items'!$I$2:$I$9

Name : AmbientA
Range : ='Sample items'!$A$2:$A$89

Name : ChillA
Range : ='Sample items'!$D$2:$D$62

Name : FreezerA
Range : ='Sample items'!$G$2:$G$9

2] Sheet : "Sample items" in A2, array formula ( Confirm entered with SHIFT+CTRL+ENTER instead of just ENTER) copy down :

=IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:"&COUNTA(B$2:B$89))),A$1:A1,)),ROW(INDIRECT("1:"&COUNTA(B$2:B$89)))),RANDBETWEEN(1,COUNTA(B$2:B$89)-ROWS(A$1:A1)+1)),"")

Then, copy to D2 and G2.

All copy down.

3] Sheet : "Orders" in D3, formula copy down :

=IF(C3="","",IFERROR(INDEX(INDIRECT(C3),MATCH(COUNTIF(C$3:C3,C3)+COUNTIF($A$3:B$54,C3),INDIRECT(C3&"A"),0)),""))

Then, copy to F3, H3, J3, L3………... until BD3.

All copy down.

Regards
Bosco
 

Attachments

  • Copy of chandooreply3.xlsm
    67.8 KB · Views: 3
Thanks again Bosco.

Had a look at the file but there are blank cells from Order 4 onwards? Does this mean there is still an issue?
 
Preetpal said:
Thanks again Bosco.

Had a look at the file but there are blank cells from Order 4 onwards? Does this mean there is still an issue?
Click to expand...
Maybe I misunderstanding your question,

herewith the revised file being Data Non-Duplicate per Order only.

1] Define Name :

Same as in post # 9

2] Sample items Sheet in A2, formula copy down :

=RAND()

Then, copy to D2 and G2.

All copy down.

3] Sheet : "Orders" in D3, formula copy down :

=IF(C3="","",INDEX(INDIRECT(C3),MATCH(SMALL(INDIRECT(C3&"A"),COUNTIF(C$3:C3,C3)),INDIRECT(C3&"A"),0)))

Then, copy to F3, H3, J3, L3………... until BD3.

All copy down.

Regards
Bosco
 

Attachments

  • Copy of chandooreply4.xlsm
    62.5 KB · Views: 3
Hi Bosco,

Sorry I may have caused the misunderstanding! Your reply yesterday (number #9 on the thread) was more to what I was hoping for. Each order was trying to avoid duplicate items appearing on the same order but trying to make each order slightly different from each other.

Your most recent reply works with no duplicates and all items showing values where needed but the same items appear in almost all orders, e.g. first ambient item is usually item 10011051.

I would like to work with the answer you provided yesterday but when I checked your attachment, the formula did not seem to return any more results from Order 5 onwards. I think the issue being with the countif part of the formula when it goes past a certain value that exceeds the total number of sample items this causes the formula to go into error?

Thanks again for replying, appreciate your help on this.



bosco_yip said:
Maybe I misunderstanding your question,

herewith the revised file being Data Non-Duplicate per Order only.

1] Define Name :

Same as in post # 9

2] Sample items Sheet in A2, formula copy down :

=RAND()

Then, copy to D2 and G2.

All copy down.

3] Sheet : "Orders" in D3, formula copy down :

=IF(C3="","",INDEX(INDIRECT(C3),MATCH(SMALL(INDIRECT(C3&"A"),COUNTIF(C$3:C3,C3)),INDIRECT(C3&"A"),0)))

Then, copy to F3, H3, J3, L3………... until BD3.

All copy down.

Regards
Bosco
Click to expand...
 
You must log in or register to reply here.
Back
Top