Selecting items when certain criteria are met

A

aymfung

New Member
Joined
Jul 27, 2014
Messages
23
Assort No. A01001 A01002 A01003
Item No. Item Desc. Co-pack Assort A Assort B Assort C
W01001 Widget 1 60
W01002 Widget 2 18 48
W01003 Widget 3 6 24
W01004 Widget 4 60
W01005 Widget 5 Yes 12 12
W01006 Widget 6 Yes 12
W01007 Widget 7 12
W01008 Widget 8 60
W01009 Widget 9 24
W01010 Widget 10 24 24


Total units 120 120 156
No. of items 5 2 7

Item 1 W01001 W01004 W01002
Item 2 W01002 W01008 W01003
Item 3 W01003 W01005
Item 4 W01005 W01006
Item 5 W01010 W01007
Item 6 W01009
Item 7 W01010
Item 8



Co-pack 1 W01005 W01005
Co-pack 2 W01006
Co-pack 3

My company sells assortments, which make up of 2 or items.
In my examples above I have 3 assortments and the items are listed below (W01001 to W01010).
Some items need co-packing - W01005 and W01006.
For each assortment, I would like to Excel to show those items that need co-packing (if any) automatically.
In the above example, W01005 for Assort A and B, and W01006 for Assort B are results I expect to see.
Please help me to solve this problem.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
you are getting no replies because what you want is not clear

we like to see a bit of the spreadsheet with an example of the output you want

you say "my company sells assortments which make up OF 2 OR ITEMS" - also define an assortment - is it a mixture of different widgets?
 
Upvote 0
oldbrewer said:
you are getting no replies because what you want is not clear

we like to see a bit of the spreadsheet with an example of the output you want

you say "my company sells assortments which make up OF 2 OR ITEMS" - also define an assortment - is it a mixture of different widgets?
Click to expand...


Yes, an assortment is made up of 2 or more different widgets. My company sells 10 widgets but can bundle them into assortments.
My apology for not being able to load a better worksheet as some of the details are not align properly. Let me explain an assortment below.
Assortment A (A01001) is made up 5 different widgets and on each widget there is a quantity:
Item No.Item DescCo-packAssort A
W01001Widget 160
W01002Widget 218
W01003Widget 36
W01004Widget 4
W01005Widget 5Yes12
W01006Widget 6Yes
W01007Widget 7
W01008Widget 8
W01009Widget 9
W01010Widget 1024
Total120
Co-pack 1
Co-pack 2
Co-pack 3

<tbody>
</tbody>

Widget 5 and 6 need co-packing. I would like Excel to auto populate item no. on the co-pack row - W01005 in the example above.
 
Upvote 0
oldbrewer said:
...but there is no widget 6 in the assortment....
Click to expand...
There is widget 6 in Assortment C. Let me put all the information in a table.

Item No.Item DescCo-packAssort AAssort BAssort C
W01001Widget 160
W01002Widget 21848
W01003Widget 3624
W01004Widget 460
W01005Widget 5Yes1212
W01006Widget 6Yes12
W01007Widget 712
W01008Widget 860
W01009Widget 924
W01010Widget 102424
Total120120156
Co-pack 1W01005W01005
Co-pack 2W01006
Co-pack 3

<tbody>
</tbody>

I would like to know the formula to use to enable me to get the answers in blue above.
 
Upvote 0
so against any number in columns D,E,F if there is a yes in the Co-pack column you want the W01.... number at the foot of the relevant column ?

what is co-pack c
 
Upvote 0
oldbrewer said:
so against any number in columns D,E,F if there is a yes in the Co-pack column you want the W01.... number at the foot of the relevant column ?

what is co-pack c
Click to expand...

Yes, as long as any of the items that make up the assortment in column D, E & F require co-packing, the item no. W01... appear at the foot of the relevant columns.
If there is no co-packing 2 and 3 as in assortment A, then just "" in those cells. Same for the rest of the assortments. Sorry, I forgot to mention this earlier.
 
Upvote 0
but assortment A does have a co pack - - - I am not yet grasping your rules - -- - how do you identify items that need co packing
 
Upvote 0
oldbrewer said:
but assortment A does have a co pack - - - I am not yet grasping your rules - -- - how do you identify items that need co packing
Click to expand...

I replied your question using quick reply about an hour ago but it is not showing up. I will do it again.
Items requiring co-packing have been determined from the beginning e.g. widget 5 and 6. Some assortments have 1, 2 or 3 items requiring co-packing. In my example, only Assortment A and C contained items requiring co-packing but not Assortment B. Only those items requiring co-packing are required to auto populate in Co-pack 1, 2 and/or 3. Otherwise, "" in those cells. In reality, there more than 3 assortments and expects new ones to be created in the future. Hence, I would like Excel to pull those items requiring co-packing automatically.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
populate dynamic list in dropdown without duplicates items based on sheet name
Replies
3
Views
154
kevin9999
K
W
  • Solved
Removing Data Validation List Items Once Selected In Another Cell
Replies
3
Views
398
James006
James006
M
  • Question
subtraction items between two sheets when show data in listbox on userform
Replies
1
Views
298
Mussa
M
K
  • Question
merge duplicates item for 70K rows and re-price for each sheet individually
Replies
7
Views
396
KalilMe
K
C
  • Question
Formula to find Max number of items in orders
Replies
1
Views
482
fjns
F

Forum statistics

Threads
1,214,920
Messages
6,122,272
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top