How to show a range or values in one cell?

[afc171]

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

 

[Zot]

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]

StockPick.xlsx
	A	B	C	D
1	Product	QTY Required	Lot	Received
2	1704159	2	45	30/Mar/2018
3	1704275		42324	01/Feb/2021
4	1705061		346	01/Feb/2021
5	5678084	3	345	01/Feb/2021
6	5678124		235235	01/Feb/2021
7	12004271		8	21/Mar/2021
8	12004272		32536	01/Feb/2021
9	12004403	4	7	01/Feb/2021
10	15879152		2	01/Feb/2021
11	30389256		788	30/Mar/2018
12	30389279	1	435	01/Feb/2021
13	30389283		534	01/Feb/2021
14	30389287		35	01/Feb/2021
15	30389291		22	30/Mar/2018
16	30389292	2	3543	01/Feb/2021
17	30389299		6322	01/Feb/2021
18	30389303		1232	01/Feb/2021
19	1001-8910		435	01/Feb/2021
20	1004-4870	4	1234	01/Feb/2021
21	1005-4870		3	30/Mar/2018
22	1012-7400		5	01/Feb/2021
23	1024-8264		5	30/Mar/2018
24	DYE0001	4	1234	01/Feb/2021
25	DYE0002		65	04/Mar/2021
26	DYE0003		42	01/Feb/2021
27	DYE0004		1234	05/Aug/2020
28	DYE0005		574	06/Mar/2019
29	DYE0006		1234	01/Feb/2021
30	DYE0007		24236	01/Feb/2021
31	DYE0008		1234	08/Sep/2020
32	DYE0009		64654	01/Feb/2021
33	DYE0010	4	1234	14/Jul/2020
34	DYE0011		321235	01/Feb/2021
35	DYE0012		1234	01/Feb/2021
36	DYE0013		342	01/Feb/2021
37	DYE0014		1234	01/Feb/2021
38	DYE0015		234	21/Mar/2021
39	DYE0016		3	01/Feb/2021
40	DYE0017		342	01/Feb/2021
41	DYE0018		333	01/Feb/2021
42	DYE0019	3	435	30/Mar/2018
43	DYE0020		1234	01/Feb/2021
44	DYE0021		3	04/Aug/2019
45	DYE0022		56	01/Feb/2021
46	DYE0023		2	05/Jun/2018
47	DYE0024	2	24	01/Feb/2021
Products

Cell Formulas
Range	Formula
C2:C47	C2	=IFERROR(VLOOKUP(A:A, Stk_QtyLB!A:H, 4, FALSE), IFERROR(VLOOKUP(A:A, Stk_QtyLB!A:E, 4, FALSE),"Not found"))
D2:D47	D2	=IFERROR(VLOOKUP(A:A, Stk_QtyLB!A:H, 5, FALSE), IFERROR(VLOOKUP(A:A, Stk_QtyLB!A:E, 5, FALSE),"Not found"))

Named Ranges
Name	Refers To	Cells
Stk_QtyLB!Query_from_CRSS	=Stk_QtyLB!$A$1:$C$53	C2:D47

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D:D	Cell Value	=0	text	NO

 

[afc171]

StockPick.xlsx
	A	B	C	D	E
1	product	bin_number	physical_qty	lot_number	receipted
2	1704159	LC3	2	45	30/Mar/2018
3	1704275	LC9	10	42324	01/Feb/2021
4	1705061	LC9	24	346	01/Feb/2021
5	5678084	LC1	10	345	01/Feb/2021
6	5678124	LC9	9	235235	01/Feb/2021
7	12004271	LC3	10	8	21/Mar/2021
8	12004272	LC9	1	32536	01/Feb/2021
9	12004403	LC1	1	7	01/Feb/2021
10	15879152	LC3	1	2	01/Feb/2021
11	30389256	LC9	1	788	30/Mar/2018
12	30389279	LC1	5	435	01/Feb/2021
13	30389283	LC9	2	534	01/Feb/2021
14	30389287	LC1	5	35	01/Feb/2021
15	30389291	LC3	1	22	30/Mar/2018
16	30389291	LC3	4	3	01/Feb/2021
17	30389292	LC1	53	3543	01/Feb/2021
18	30389292	LC1	1	3	01/Feb/2021
19	30389299	LC9	34	6322	01/Feb/2021
20	30389299	LC9	10	5	21/Mar/2021
21	30389303	LC3	4	1232	01/Feb/2021
22	30389303	LC3	24	4	01/Feb/2021
23	1001-8910	LC1	10	435	01/Feb/2021
24	1004-4870	LC3	9	1234	01/Feb/2021
25	1005-4870	LC9	10	3	30/Mar/2018
26	1012-7400	LC9	1	5	01/Feb/2021
27	1024-8264	LC1	1	5	30/Mar/2018
28	DYE0001	LBPRO201	0	1234	01/Feb/2021
29	DYE0002	LBPRO201	5	65	04/Mar/2021
30	DYE0003	LBPRO201	7	42	01/Feb/2021
31	DYE0004	LBPRO201	8	1234	05/Aug/2020
32	DYE0005	LBPRO401	2	574	06/Mar/2019
33	DYE0006	LBPRO401	4	1234	01/Feb/2021
34	DYE0007	LBPRO201	10	24236	01/Feb/2021
35	DYE0008	LBPRO201	24	1234	08/Sep/2020
36	DYE0009	LBPRO201	10	64654	01/Feb/2021
37	DYE0010	LBPRO201	9	1234	14/Jul/2020
38	DYE0011	LBPRO201	10	321235	01/Feb/2021
39	DYE0012	LBPRO201	1	1234	01/Feb/2021
40	DYE0013	LBPRO201	1	342	01/Feb/2021
41	DYE0014	LBPRO201	1	1234	01/Feb/2021
42	DYE0015	LBPRO201	1	234	21/Mar/2021
43	DYE0016	LBPRO401	5	3	01/Feb/2021
44	DYE0017	LBPRO401	2	342	01/Feb/2021
45	DYE0018	LBPRO201	5	333	01/Feb/2021
46	DYE0019	LBPRO201	4	435	30/Mar/2018
47	DYE0020	LBPRO201	2	1234	01/Feb/2021
48	DYE0021	LBPRO201	14	3	04/Aug/2019
49	DYE0022	LBPRO401	0	56	01/Feb/2021
50	DYE0023	LBPRO201	3	2	05/Jun/2018
51	DYE0024	LBPRO201	3	24	01/Feb/2021
52	DYE0024	LBPRO201	6	112	04/Jul/2011
Stk_QtyLB

 

[yky]

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]

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]

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]

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]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	product	bin_number	physical_qty	lot_number	receipted				product pick	bin_number	physical_qty	lot_number	receipted
2	1704159	LC3	2	45	30/Mar/2018				30389299	LC9	34	6322	1/Feb/2021
3	1704275	LC9	10	42324	1/Feb/2021					LC9	10	5	21/Mar/2021
4	1705061	LC9	24	346	1/Feb/2021
5	5678084	LC1	10	345	1/Feb/2021
6	5678124	LC9	9	235235	1/Feb/2021
7	12004271	LC3	10	8	21/Mar/2021
8	12004272	LC9	1	32536	1/Feb/2021
9	12004403	LC1	1	7	1/Feb/2021
10	15879152	LC3	1	2	1/Feb/2021
11	30389256	LC9	1	788	30/Mar/2018
12	30389279	LC1	5	435	1/Feb/2021
13	30389283	LC9	2	534	1/Feb/2021
14	30389287	LC1	5	35	1/Feb/2021
15	30389291	LC3	1	22	30/Mar/2018
16	30389291	LC3	4	3	1/Feb/2021
17	30389292	LC1	53	3543	1/Feb/2021
18	30389292	LC1	1	3	1/Feb/2021
19	30389299	LC9	34	6322	1/Feb/2021
20	30389299	LC9	10	5	21/Mar/2021
21	30389303	LC3	4	1232	1/Feb/2021
22	30389303	LC3	24	4	1/Feb/2021
23	1001-8910	LC1	10	435	1/Feb/2021
24	1004-4870	LC3	9	1234	1/Feb/2021
25	1005-4870	LC9	10	3	30/Mar/2018
26	1012-7400	LC9	1	5	1/Feb/2021
27	1024-8264	LC1	1	5	30/Mar/2018
28	DYE0001	LBPRO201	0	1234	1/Feb/2021
29	DYE0002	LBPRO201	5	65	4/Mar/2021
30	DYE0003	LBPRO201	7	42	1/Feb/2021
31	DYE0004	LBPRO201	8	1234	5/Aug/2020
32	DYE0005	LBPRO401	2	574	6/Mar/2019
33	DYE0006	LBPRO401	4	1234	1/Feb/2021
34	DYE0007	LBPRO201	10	24236	1/Feb/2021
35	DYE0008	LBPRO201	24	1234	8/Sep/2020
36	DYE0009	LBPRO201	10	64654	1/Feb/2021
37	DYE0010	LBPRO201	9	1234	14/Jul/2020
38	DYE0011	LBPRO201	10	321235	1/Feb/2021
39	DYE0012	LBPRO201	1	1234	1/Feb/2021
40	DYE0013	LBPRO201	1	342	1/Feb/2021
41	DYE0014	LBPRO201	1	1234	1/Feb/2021
42	DYE0015	LBPRO201	1	234	21/Mar/2021
43	DYE0016	LBPRO401	5	3	1/Feb/2021
44	DYE0017	LBPRO401	2	342	1/Feb/2021
45	DYE0018	LBPRO201	5	333	1/Feb/2021
46	DYE0019	LBPRO201	4	435	30/Mar/2018
47	DYE0020	LBPRO201	2	1234	1/Feb/2021
48	DYE0021	LBPRO201	14	3	4/Aug/2019
49	DYE0022	LBPRO401	0	56	1/Feb/2021
50	DYE0023	LBPRO201	3	2	5/Jun/2018
51	DYE0024	LBPRO201	3	24	1/Feb/2021
52	DYE0024	LBPRO201	6	112	4/Jul/2011
53
Sheet1

Cell Formulas
Range	Formula
J2:J52	J2	=IFERROR(INDEX(Table1[bin_number], SMALL(IF($I$2=Table1[product], ROW(Table1[product])-ROW($I$2)+1), ROW(1:1))),"" )
K2:K52	K2	=IFERROR(INDEX(Table1[physical_qty], SMALL(IF($I$2=Table1[product], ROW(Table1[product])-ROW($I$2)+1), ROW(1:1))),"" )
L2:L52	L2	=IFERROR(INDEX(Table1[lot_number], SMALL(IF($I$2=Table1[product], ROW(Table1[product])-ROW($I$2)+1), ROW(1:1))),"" )
M2:M52	M2	=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]

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.