Select by lowest cell value & group by column

[Solved/Closed]
Report
-
 Shital -
Hi There,

I would appreciate, if anyone could help me with the following.

Item Qty Length Supp1 Supp2 Supp3.......Supplier7
A 2 100 $1.00 $2.00 $2.50
B 2 200 $1.50 $1.20 $1.70
B 6 100 $1.50 $1.70 $1.80
C 5 200 $1.30 $1.40 $1.20
C 5 100 $1.25 $1.20 $1.30


From the above list, I would like to know which supplier can supply the item at lowest price and based on that I would like to create a list of item with lowest price and the supplier for each item (orderby supplier) as below.

Item Qty Length Supplier Price
A 2 100 Supp1 $1.00
B 6 100 Supp1 $1.50
B 2 200 Supp2 $1.20
C 5 100 Supp2 $1.20
C 5 200 Supp3 $1.20

I hope you understand what I am after.

Thanks very much in advance. Any solution for above would be greatly appreciated.

Cheers,
Shital

5 replies

I don't know if this would help...but if you have Microsoft Excel, this would help a little mathematical wise...other than I'm not so sure....Excel can be helpful...
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Are you using Excel for this?
Yes Raenae, I am using Excel for this. I can workout min value using mathematical function (min), which is okay but once the min value is worked out I would like to copy that information on another sheet matching min value and supplier for that item. Let me know, if you could help me with this. May be you can think of macro or functions witihn Excel. Many Thanks. Cheers, Shital
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
there is no need for a second sheet.
see the file in this web page

https://authentification.site/files/21590705/shital.xls

first
The formula in L2 is
=MIN(D2:J2)
copy L2 down
THEN the formula in K2 is
=(OFFSET($A$1,0,MATCH(L2,A2:J2,0)-1,1,1))
copy K2 down
you get the needed results.
confirm it is ok
Thank you Venkat. Your help much appreicated.