How to find the earliest shipment?

[Rickdu]

Hi guys,

I have a problem to resolve. as the image below, on the left hand side are bunch of data with product, week & shipment. what I wanted to achieve is to find the earliest shipment week as well as volume in the right hand side with formulas.

thanks in advance for the help.

 

[Peter_SSs]

A couple of suggestions that should help you get best answers in the future:

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

See if this helps, assuming Product A & B are in cells F2:F3
G2: =VLOOKUP(F2,A$2:B$100,2,0)
H2: =VLOOKUP(F2,A$2:C$100,3,0)

 

[Rickdu]

A couple of suggestions that should help you get best answers in the future:

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

See if this helps, assuming Product A & B are in cells F2:F3
G2: =VLOOKUP(F2,A$2:B$100,2,0)
H2: =VLOOKUP(F2,A$2:C$100,3,0)

Thanks Peter_SSs for your suggestions. all been taken care of now. The solution proposed will find the first value even the value has a 0. what I tried to achieve is finding the week for the first shipment with a value ,i.e. the answer for the earliest shipment of B is week 32. I have reattached the mini sheet.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Product	Week	Shipment					Product	earliest shipment	volume
2	A	31.2021	50					A
3	A	32.2021						B
4	A	33.2021	80
5	A	34.2021	50
6	A	35.2021
7	A	36.2021	100
8	B	31.2021
9	B	32.2021	40
10	B	33.2021
11	B	34.2021	30
12	B	35.2021	30
13	B	36.2021	30
Sheet1

 

[Peter_SSs]

Thanks for updating your profile.

The solution proposed will find the first value even the value has a 0.

You did not have any examples where the first value was empty so I didn't know that was possible.

Try this instead.

21 08 04.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Product	Week	Shipment					Product	earliest shipment	volume
2	A	31.2021	50					A	31.2021	50
3	A	32.2021						B	32.2021	40
4	A	33.2021	80
5	A	34.2021	50
6	A	35.2021
7	A	36.2021	100
8	B	31.2021
9	B	32.2021	40
10	B	33.2021
11	B	34.2021	30
12	B	35.2021	30
13	B	36.2021	30
Earliest

Cell Formulas
Range	Formula
I2:J3	I2	=INDEX(FILTER(B$2:B$13,($A$2:$A$13=$H2)*($C$2:$C$13<>""),""),1)

 

[Rickdu]

Thanks for updating your profile.


You did not have any examples where the first value was empty so I didn't know that was possible.

Try this instead.

21 08 04.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Product	Week	Shipment					Product	earliest shipment	volume
2	A	31.2021	50					A	31.2021	50
3	A	32.2021						B	32.2021	40
4	A	33.2021	80
5	A	34.2021	50
6	A	35.2021
7	A	36.2021	100
8	B	31.2021
9	B	32.2021	40
10	B	33.2021
11	B	34.2021	30
12	B	35.2021	30
13	B	36.2021	30
Earliest

Cell Formulas
Range	Formula
I2:J3	I2	=INDEX(FILTER(B$2:B$13,($A$2:$A$13=$H2)*($C$2:$C$13<>""),""),1)

works great. thanks for the help.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.