How to show a range or values in one cell?

afc171

Board Regular
Joined
Jan 14, 2017
Messages
78
Office Version
  1. 2019
Platform
  1. Windows
Hi guys,
I get sent a Product pick sheet (first image) which will have the quantities required.
I then pull the information (from Sage) for the products to show what is in stock with batches and date receipted (see 2nd image)
If there is a product which needs to be picked from stock that has 2 or more different batches (e.g DYE0024 in 2nd image) but also need to pick the earliest receipted item how can I populate this information into Product pick sheet or the one I pull the information into from Sage?

Basically I need to copy the products and quantities requested into a sheet which will show me the earliest receipted product and batch.

Thank you
 

Attachments

  • Screenshot 2021-05-08 at 17.55.33.png
    Screenshot 2021-05-08 at 17.55.33.png
    48.9 KB · Views: 15
  • Screenshot 2021-05-08 at 17.56.15.png
    Screenshot 2021-05-08 at 17.56.15.png
    160 KB · Views: 16

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,560
Office Version
  1. 2016
Platform
  1. Windows
Welcomed to MrExcel.
Please update you personal info by filling in Excel version and your OS. Don't forget to save it.

As much as many people in this forum are eager to help, but probably many are just lazy to retype the data you presented to verify their code 😁

Explore how to use XL2BB (you can just click icon to out more) or at least copy and paste your sheet here
 

afc171

Board Regular
Joined
Jan 14, 2017
Messages
78
Office Version
  1. 2019
Platform
  1. Windows
StockPick.xlsx
ABCD
1ProductQTY RequiredLotReceived
2170415924530/Mar/2018
317042754232401/Feb/2021
4170506134601/Feb/2021
55678084334501/Feb/2021
6567812423523501/Feb/2021
712004271821/Mar/2021
8120042723253601/Feb/2021
9120044034701/Feb/2021
1015879152201/Feb/2021
113038925678830/Mar/2018
1230389279143501/Feb/2021
133038928353401/Feb/2021
14303892873501/Feb/2021
15303892912230/Mar/2018
16303892922354301/Feb/2021
1730389299632201/Feb/2021
1830389303123201/Feb/2021
191001-891043501/Feb/2021
201004-48704123401/Feb/2021
211005-4870330/Mar/2018
221012-7400501/Feb/2021
231024-8264530/Mar/2018
24DYE00014123401/Feb/2021
25DYE00026504/Mar/2021
26DYE00034201/Feb/2021
27DYE0004123405/Aug/2020
28DYE000557406/Mar/2019
29DYE0006123401/Feb/2021
30DYE00072423601/Feb/2021
31DYE0008123408/Sep/2020
32DYE00096465401/Feb/2021
33DYE00104123414/Jul/2020
34DYE001132123501/Feb/2021
35DYE0012123401/Feb/2021
36DYE001334201/Feb/2021
37DYE0014123401/Feb/2021
38DYE001523421/Mar/2021
39DYE0016301/Feb/2021
40DYE001734201/Feb/2021
41DYE001833301/Feb/2021
42DYE0019343530/Mar/2018
43DYE0020123401/Feb/2021
44DYE0021304/Aug/2019
45DYE00225601/Feb/2021
46DYE0023205/Jun/2018
47DYE002422401/Feb/2021
Products
Cell Formulas
RangeFormula
C2:C47C2=IFERROR(VLOOKUP(A:A, Stk_QtyLB!A:H, 4, FALSE), IFERROR(VLOOKUP(A:A, Stk_QtyLB!A:E, 4, FALSE),"Not found"))
D2:D47D2=IFERROR(VLOOKUP(A:A, Stk_QtyLB!A:H, 5, FALSE), IFERROR(VLOOKUP(A:A, Stk_QtyLB!A:E, 5, FALSE),"Not found"))
Named Ranges
NameRefers ToCells
Stk_QtyLB!Query_from_CRSS=Stk_QtyLB!$A$1:$C$53C2:D47
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D:DCell Value=0textNO
 

afc171

Board Regular
Joined
Jan 14, 2017
Messages
78
Office Version
  1. 2019
Platform
  1. Windows
StockPick.xlsx
ABCDE
1productbin_numberphysical_qtylot_numberreceipted
21704159LC324530/Mar/2018
31704275LC9104232401/Feb/2021
41705061LC92434601/Feb/2021
55678084LC11034501/Feb/2021
65678124LC9923523501/Feb/2021
712004271LC310821/Mar/2021
812004272LC913253601/Feb/2021
912004403LC11701/Feb/2021
1015879152LC31201/Feb/2021
1130389256LC9178830/Mar/2018
1230389279LC1543501/Feb/2021
1330389283LC9253401/Feb/2021
1430389287LC153501/Feb/2021
1530389291LC312230/Mar/2018
1630389291LC34301/Feb/2021
1730389292LC153354301/Feb/2021
1830389292LC11301/Feb/2021
1930389299LC934632201/Feb/2021
2030389299LC910521/Mar/2021
2130389303LC34123201/Feb/2021
2230389303LC324401/Feb/2021
231001-8910LC11043501/Feb/2021
241004-4870LC39123401/Feb/2021
251005-4870LC910330/Mar/2018
261012-7400LC91501/Feb/2021
271024-8264LC11530/Mar/2018
28DYE0001LBPRO2010123401/Feb/2021
29DYE0002LBPRO20156504/Mar/2021
30DYE0003LBPRO20174201/Feb/2021
31DYE0004LBPRO2018123405/Aug/2020
32DYE0005LBPRO401257406/Mar/2019
33DYE0006LBPRO4014123401/Feb/2021
34DYE0007LBPRO201102423601/Feb/2021
35DYE0008LBPRO20124123408/Sep/2020
36DYE0009LBPRO201106465401/Feb/2021
37DYE0010LBPRO2019123414/Jul/2020
38DYE0011LBPRO2011032123501/Feb/2021
39DYE0012LBPRO2011123401/Feb/2021
40DYE0013LBPRO201134201/Feb/2021
41DYE0014LBPRO2011123401/Feb/2021
42DYE0015LBPRO201123421/Mar/2021
43DYE0016LBPRO4015301/Feb/2021
44DYE0017LBPRO401234201/Feb/2021
45DYE0018LBPRO201533301/Feb/2021
46DYE0019LBPRO201443530/Mar/2018
47DYE0020LBPRO2012123401/Feb/2021
48DYE0021LBPRO20114304/Aug/2019
49DYE0022LBPRO40105601/Feb/2021
50DYE0023LBPRO2013205/Jun/2018
51DYE0024LBPRO20132401/Feb/2021
52DYE0024LBPRO201611204/Jul/2011
Stk_QtyLB
 

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,875
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Let's say you need 6 units of product 1 and you have 3 units received on date 1, 2 units received on date 2, leaving 1 unit unfilled. Your sheet doesn't seem to handle this.
 

afc171

Board Regular
Joined
Jan 14, 2017
Messages
78
Office Version
  1. 2019
Platform
  1. Windows
Let's say you need 6 units of product 1 and you have 3 units received on date 1, 2 units received on date 2, leaving 1 unit unfilled. Your sheet doesn't seem to handle this.

True there could be multiple batches booked in on different days, this would show up on the Stk_QTYLB sheet but would require someone to pick the earliest batch.
Not sure what the best way to tackle that would be.
 

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,875
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

True there could be multiple batches booked in on different days, this would show up on the Stk_QTYLB sheet but would require someone to pick the earliest batch.
Not sure what the best way to tackle that would be.
That's not what I meant. What I meant to say is the earliest batch may not have enough quantity to satisfy the request. If the request is 6 and the earliest batch has only 3, what'd you do?
 

afc171

Board Regular
Joined
Jan 14, 2017
Messages
78
Office Version
  1. 2019
Platform
  1. Windows
That's not what I meant. What I meant to say is the earliest batch may not have enough quantity to satisfy the request. If the request is 6 and the earliest batch has only 3, what'd you do?
Book out the next 3, 2 or 1. May have to check stock and the enter the batch in manually.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,560
Office Version
  1. 2016
Platform
  1. Windows
Book out the next 3, 2 or 1. May have to check stock and the enter the batch in manually.
You can book from different lot number based on info from sage but if the qty required is more than 1 batch or 1 line, how to populate the Product Pick sheet. You have to insert line but you never know how many may line upfront.

I guess you can just create a table where you either type in or copy/paste the product number and you get all the list on that product extracted from Sage data.

Something like this perhaps:
Book1
ABCDEFGHIJKLM
1productbin_numberphysical_qtylot_numberreceiptedproduct pickbin_numberphysical_qtylot_numberreceipted
21704159LC324530/Mar/201830389299LC93463221/Feb/2021
31704275LC910423241/Feb/2021LC910521/Mar/2021
41705061LC9243461/Feb/2021    
55678084LC1103451/Feb/2021    
65678124LC992352351/Feb/2021    
712004271LC310821/Mar/2021    
812004272LC91325361/Feb/2021    
912004403LC1171/Feb/2021    
1015879152LC3121/Feb/2021    
1130389256LC9178830/Mar/2018    
1230389279LC154351/Feb/2021    
1330389283LC925341/Feb/2021    
1430389287LC15351/Feb/2021    
1530389291LC312230/Mar/2018    
1630389291LC3431/Feb/2021    
1730389292LC15335431/Feb/2021    
1830389292LC1131/Feb/2021    
1930389299LC93463221/Feb/2021    
2030389299LC910521/Mar/2021    
2130389303LC3412321/Feb/2021    
2230389303LC32441/Feb/2021    
231001-8910LC1104351/Feb/2021    
241004-4870LC3912341/Feb/2021    
251005-4870LC910330/Mar/2018    
261012-7400LC9151/Feb/2021    
271024-8264LC11530/Mar/2018    
28DYE0001LBPRO201012341/Feb/2021    
29DYE0002LBPRO2015654/Mar/2021    
30DYE0003LBPRO2017421/Feb/2021    
31DYE0004LBPRO201812345/Aug/2020    
32DYE0005LBPRO40125746/Mar/2019    
33DYE0006LBPRO401412341/Feb/2021    
34DYE0007LBPRO20110242361/Feb/2021    
35DYE0008LBPRO2012412348/Sep/2020    
36DYE0009LBPRO20110646541/Feb/2021    
37DYE0010LBPRO2019123414/Jul/2020    
38DYE0011LBPRO201103212351/Feb/2021    
39DYE0012LBPRO201112341/Feb/2021    
40DYE0013LBPRO20113421/Feb/2021    
41DYE0014LBPRO201112341/Feb/2021    
42DYE0015LBPRO201123421/Mar/2021    
43DYE0016LBPRO401531/Feb/2021    
44DYE0017LBPRO40123421/Feb/2021    
45DYE0018LBPRO20153331/Feb/2021    
46DYE0019LBPRO201443530/Mar/2018    
47DYE0020LBPRO201212341/Feb/2021    
48DYE0021LBPRO2011434/Aug/2019    
49DYE0022LBPRO4010561/Feb/2021    
50DYE0023LBPRO201325/Jun/2018    
51DYE0024LBPRO2013241/Feb/2021    
52DYE0024LBPRO20161124/Jul/2011    
53
Sheet1
Cell Formulas
RangeFormula
J2:J52J2=IFERROR(INDEX(Table1[bin_number], SMALL(IF($I$2=Table1[product], ROW(Table1[product])-ROW($I$2)+1), ROW(1:1))),"" )
K2:K52K2=IFERROR(INDEX(Table1[physical_qty], SMALL(IF($I$2=Table1[product], ROW(Table1[product])-ROW($I$2)+1), ROW(1:1))),"" )
L2:L52L2=IFERROR(INDEX(Table1[lot_number], SMALL(IF($I$2=Table1[product], ROW(Table1[product])-ROW($I$2)+1), ROW(1:1))),"" )
M2:M52M2=IFERROR(INDEX(Table1[receipted], SMALL(IF($I$2=Table1[product], ROW(Table1[product])-ROW($I$2)+1), ROW(1:1))),"" )
Press CTRL+SHIFT+ENTER to enter array formulas.
 

afc171

Board Regular
Joined
Jan 14, 2017
Messages
78
Office Version
  1. 2019
Platform
  1. Windows
Yes Zot, i think it will have to be done manually as it will be easier for the picker to check, insert another line(s) in for different batches. Also the requester will only see the physical total quantity not any batch info.
 

Forum statistics

Threads
1,141,299
Messages
5,705,571
Members
421,399
Latest member
hjweiss00

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
Top