Select by lowest cell value & group by column

Solved/Closed
Shital - Mar 23, 2010 at 09:56 PM
 Shital - Mar 24, 2010 at 12:28 AM
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
Are you using Excel for this?
0
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
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
805
Mar 23, 2010 at 11:32 PM
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
0

Didn't find the answer you are looking for?

Ask a question
Thank you Venkat. Your help much appreicated.
0