Best cost for same item in subsequent rows and return that value

[rick8899]

I am looking for a formula or vba to find the cost for a single item that has data on 1-3 rows of price plan data. Then it would need to return the item number with the lowest cost and plan.

Test Page1.xlsx
	A	B	C	D	E	F	G
2	Formula picks the best cost for each item and lists it on the right side
3
4	Raw Data				After formula is applied
5	Stock Number	Cost	Price Plan		Stock Number	Cost	Price Plan
6	LEO74541	4.85	GL8		LEO74541	4.35	17A
7	LEO74541	4.35	17A		LEO76310ST	57.45	GL8
8	LEO76310ST	57.45	GL8		LEO76350ST	55.01	17A
9	LEO76350ST	64.55	GL8		LEO76365ST	60.71	W0I
10	LEO76350ST	55.01	17A		LEO77106	3.28	W0I
11	LEO76365ST	108.92	GL8
12	LEO76365ST	60.71	W0I
13	LEO77106	4.78	17A
14	LEO77106	3.28	W0I
15	LEO77106	5.71	GL8
Sheet1

 

[Cubist]

Hi,
Which version of Excel are you using? You should update your Account Details.

 

[MrRajKumar]

Hi,

If you are using 365, here is one way:

=LET(a,SORT(UNIQUE(A6:A15)),b,MAP(a,LAMBDA(x,MINIFS(B6:B15,A6:A15,x))),c,MAP(a,b,LAMBDA(x,y,FILTER(C6:C15,(A6:A15=x)*(B6:B15=y)))),CHOOSE({1,2,3},a,b,c))

Sure, there will be more elegant way.

 

[Fluff]

What version of Excel are you using?

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’)

Another option if you have 365

Fluff.xlsm
	A	B	C	D	E	F	G
1	Stock Number	Cost	Price Plan		Stock Number	Cost	Price Plan
2	LEO74541	4.85	GL8		LEO74541	4.35	17A
3	LEO74541	4.35	17A		LEO76310ST	57.45	GL8
4	LEO76310ST	57.45	GL8		LEO76350ST	55.01	17A
5	LEO76350ST	64.55	GL8		LEO76365ST	60.71	W0I
6	LEO76350ST	55.01	17A		LEO77106	3.28	W0I
7	LEO76365ST	108.92	GL8
8	LEO76365ST	60.71	W0I
9	LEO77106	4.78	17A
10	LEO77106	3.28	W0I
11	LEO77106	5.71	GL8
Data

Cell Formulas
Range	Formula
F2:G6	F2	=TAKE(SORT(FILTER($B$2:$C$11,$A$2:$A$11=E2)),1)
Dynamic array formulas.

 

[rick8899]

Hi,
Which version of Excel are you using? You should update your Account Details.

Sorry the late reply. I am using 365. I just updated my account.

 

[rick8899]

Hi,

If you are using 365, here is one way:

=LET(a,SORT(UNIQUE(A6:A15)),b,MAP(a,LAMBDA(x,MINIFS(B6:B15,A6:A15,x))),c,MAP(a,b,LAMBDA(x,y,FILTER(C6:C15,(A6:A15=x)*(B6:B15=y)))),CHOOSE({1,2,3},a,b,c))

Sure, there will be more elegant way.

Thank you for the example.

 

[rick8899]

What version of Excel are you using?

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’)

Another option if you have 365

Fluff.xlsm
	A	B	C	D	E	F	G
1	Stock Number	Cost	Price Plan		Stock Number	Cost	Price Plan
2	LEO74541	4.85	GL8		LEO74541	4.35	17A
3	LEO74541	4.35	17A		LEO76310ST	57.45	GL8
4	LEO76310ST	57.45	GL8		LEO76350ST	55.01	17A
5	LEO76350ST	64.55	GL8		LEO76365ST	60.71	W0I
6	LEO76350ST	55.01	17A		LEO77106	3.28	W0I
7	LEO76365ST	108.92	GL8
8	LEO76365ST	60.71	W0I
9	LEO77106	4.78	17A
10	LEO77106	3.28	W0I
11	LEO77106	5.71	GL8
Data

Cell Formulas
Range	Formula
F2:G6	F2	=TAKE(SORT(FILTER($B$2:$C$11,$A$2:$A$11=E2)),1)
Dynamic array formulas.

This works perfectly! Thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[smozgur]

This works perfectly! Thank you!

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.