MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### Excell Formula

• Follow

```I have a list of over 1000 Product in column A and in column B over 30
Supplies and in column C the price of each product from different Supplies.
Each Supplier may offer same product but with different prices ( e.g. Table
below ). All the information is in sheet 1.

A	B	C
1	Product 1	Supplier 2	£10.00
2	Product 2	Supplier 1	£8.00
3	Product 3	Supplier 2	£8.00
4	Product 2	Supplier 2	£6.00
5	Product 1	Supplier 3	£11.00
6	Product 3	Supplier 1	£7.00

I have created sheet 2 ( e.g. table below ) I need formula which updates
under heading of each supplier to show which product is cheapest from each
supplier for all the  product

A	           B	                  C
1	Supplier 101   Supplier 2         Supplier 3
2	Product 100    Product 14        Product 16
3	Product 9	     Product 25        Product 111
4	Product 590    Product 89        Product 2
5	Product 250    Product 56        Product 15
6	Product 1	     Product 999      Product 8

Thanks for any help I get

```
 0
Reply Utf 2/7/2010 9:39:01 PM

```Hi Farid
I'm not sure if I got the column right but you can change that. Also adjust
range to your needs.
=SUMPRODUCT(--(Sheet1!B2:B7="Product1"),--(Sheet1!A2:A7="Supplier1"),C2:C7)
column C would be your prices.
HTH
John
"Farid" <Farid@discussions.microsoft.com> wrote in message
news:DBDE34F0-FAC4-467A-BB05-990FFBDB1146@microsoft.com...
>I have a list of over 1000 Product in column A and in column B over 30
> Supplies and in column C the price of each product from different Supplies.
> Each Supplier may offer same product but with different prices ( e.g. Table
> below ). All the information is in sheet 1.
>
> A B C
> 1 Product 1 Supplier 2 £10.00
> 2 Product 2 Supplier 1 £8.00
> 3 Product 3 Supplier 2 £8.00
> 4 Product 2 Supplier 2 £6.00
> 5 Product 1 Supplier 3 £11.00
> 6 Product 3 Supplier 1 £7.00
>
>
> I have created sheet 2 ( e.g. table below ) I need formula which updates
> under heading of each supplier to show which product is cheapest from each
> supplier for all the  product
>
>       A            B                   C
> 1 Supplier 101   Supplier 2         Supplier 3
> 2 Product 100    Product 14        Product 16
> 3 Product 9      Product 25        Product 111
> 4 Product 590    Product 89        Product 2
> 5 Product 250    Product 56        Product 15
> 6 Product 1      Product 999      Product 8
>
> Thanks for any help I get
>

```
 0
Reply John 2/7/2010 9:58:39 PM

```Ignore my reply,it's adding up, not giving you the cheapest, I didn't read
properly
Regards
John
"John" <johnd@newlook.com> wrote in message
news:uijH%23DEqKHA.5696@TK2MSFTNGP04.phx.gbl...
> Hi Farid
> I'm not sure if I got the column right but you can change that. Also adjust
> range to your needs.
> =SUMPRODUCT(--(Sheet1!B2:B7="Product1"),--(Sheet1!A2:A7="Supplier1"),C2:C7)
> column C would be your prices.
> HTH
> John
> "Farid" <Farid@discussions.microsoft.com> wrote in message
> news:DBDE34F0-FAC4-467A-BB05-990FFBDB1146@microsoft.com...
>>I have a list of over 1000 Product in column A and in column B over 30
>> Supplies and in column C the price of each product from different Supplies.
>> Each Supplier may offer same product but with different prices ( e.g. Table
>> below ). All the information is in sheet 1.
>>
>> A B C
>> 1 Product 1 Supplier 2 £10.00
>> 2 Product 2 Supplier 1 £8.00
>> 3 Product 3 Supplier 2 £8.00
>> 4 Product 2 Supplier 2 £6.00
>> 5 Product 1 Supplier 3 £11.00
>> 6 Product 3 Supplier 1 £7.00
>>
>>
>> I have created sheet 2 ( e.g. table below ) I need formula which updates
>> under heading of each supplier to show which product is cheapest from each
>> supplier for all the  product
>>
>>       A            B                   C
>> 1 Supplier 101   Supplier 2         Supplier 3
>> 2 Product 100    Product 14        Product 16
>> 3 Product 9      Product 25        Product 111
>> 4 Product 590    Product 89        Product 2
>> 5 Product 250    Product 56        Product 15
>> 6 Product 1      Product 999      Product 8
>>
>> Thanks for any help I get
>>
>

```
 0
Reply John 2/7/2010 10:06:20 PM

2 Replies
188 Views

(page loaded in 0.052 seconds)

8/16/2012 4:25:29 AM

 Reply: