Hi
I have a straight table with 5 different columns showing 5 different supplier prices for items we sell with a column with the items SKU.
Is it possible to add an extra column for each supplier and display the price if they are the cheapest or equal cheapest of the 5 suppliers for that item SKU? So the price would only be displayed in this new column for each supplier if they are the cheapest. The fields for each supplier may also contain a null value if they don't supply the item.
So the original table would look like this
| Item SKU |
Supplier 1 Price |
Supplier 2 Price |
Supplier 3 Price |
Supplier 4 Price |
Supplier 5 Price |
| F001709-5G |
17.29 |
45.20 |
17.29 |
11.40 |
- |
| F001709-10G |
30.94 |
90.40 |
30.94 |
50.40 |
- |
| F003977-1G |
- |
- |
41.86 |
32.20 |
- |
| F003977-5G |
160.16 |
99.60 |
141.96 |
109.20 |
- |
| F004478-1G |
97.37 |
67.80 |
74.62 |
- |
- |
| F005897-1G |
4.55 |
14.69 |
- |
3.60 |
- |
| F005897-5G |
- |
45.20 |
- |
10.80 |
- |
| F005897-10G |
24.57 |
79.10 |
38.22 |
19.20 |
16.23 |
| F006320-1G |
65.52 |
22.60 |
47.32 |
- |
- |
| F006341-5G |
- |
45.20 |
163.80 |
- |
- |
| F008202-1G |
39.13 |
22.60 |
42.77 |
- |
- |
| F008202-5G |
159.25 |
- |
147.42 |
- |
89.99 |
| F008297-1G |
- |
- |
- |
- |
10.01 |
And the new table with the added columns would look like this
| Item SKU |
Supplier 1 Price |
Supplier 1 Cheapest |
Supplier 2 Price |
Supplier 2 Cheapest |
Supplier 3 Price |
Supplier 3 Cheapest |
Supplier 4 Price |
Supplier 4 Cheapest |
Supplier 5 Price |
Supplier 5 Cheapest |
| F001709-5G |
17.29 |
|
45.20 |
|
17.29 |
|
11.40 |
11.40 |
- |
|
| F001709-10G |
30.94 |
30.94 |
90.40 |
|
30.94 |
30.94 |
50.40 |
|
- |
|
| F003977-1G |
- |
|
- |
|
41.86 |
|
32.20 |
32.20 |
- |
|
| F003977-5G |
160.16 |
|
99.60 |
99.60 |
141.96 |
|
109.20 |
|
- |
|
| F004478-1G |
97.37 |
|
67.80 |
67.80 |
74.62 |
|
- |
|
- |
|
| F005897-1G |
4.55 |
|
14.69 |
|
- |
|
3.60 |
3.60 |
- |
|
| F005897-5G |
- |
|
45.20 |
|
- |
|
10.80 |
10.80 |
- |
|
| F005897-10G |
24.57 |
|
79.10 |
|
38.22 |
|
19.20 |
|
16.23 |
16.23 |
| F006320-1G |
65.52 |
|
22.60 |
22.60 |
47.32 |
|
- |
|
- |
|
| F006341-5G |
- |
|
45.20 |
45.20 |
163.80 |
|
- |
|
- |
|
| F008202-1G |
39.13 |
|
22.60 |
22.60 |
42.77 |
|
- |
|
- |
|
| F008202-5G |
159.25 |
|
- |
|
147.42 |
|
- |
|
89.99 |
89.99 |
| F008297-1G |
- |
|
- |
|
- |
|
- |
|
10.01 |
10.01 |
I've tried using the following expression for Supplier 1 to create a new column 'Supplier 1 Cheapest' but when the other suppliers have a null value it doesn't work
if([Supplier 1 Price]<[Supplier 2 Price] and [Supplier 1 Price]<[Supplier 3 Price] and [Supplier 1 Price]<[Supplier 4 Price] and [Supplier 1 Price]<[Supplier 5 Price], [Supplier 1 Price])
I've also attached an excel file which contains both tables.
Any help would be much appreciated.
Thanks
Paul
