I have some data where I need to calculate the "best deal". I have the basic math worked out, I just need to find a way to implement it into an excel sheet or macro. Here is how my data is structured:
item 2 2000, 3, 2, 1, JOHNS COMPANY, 2500 ...
...
Those are the 6 important attributes my data has. It is organized on one sheet in a quasi-tabular form like above, for each company we have pricing on (so it repeats across the sheet lengthwise). The goal is to determine whether to buy item 1 from JOHNS COMPANY or one of the other ones, after some comparisons.
Sometimes buying 1,000 even if you only need 750 is better. I could do a min function for each row, but then I have the lowest price without any seller information. If I could get the cell address of the lowest price, I could find who has the lowest price. At this point though transforming the data into pivot tables or creating a vba macro starts to seem more appealing.
Ideally I won't have to change the format of the data, even if it isn't optimal. The formula I would need to make has to compare 10+ companies so it would probably turn out like a =IF(AND(OR(... lots of stuff ))) nightmare. Let me know if I left anything out or if you know of anything off of the top of your head to get me pointed in the right direction. Thanks
minimum buy quantity, rate 1, rate 2, rate 3, company, order quantity ...
item 1 1000, 5, 4, 3, JOHNS COMPANY, 750 ...item 2 2000, 3, 2, 1, JOHNS COMPANY, 2500 ...
...
Those are the 6 important attributes my data has. It is organized on one sheet in a quasi-tabular form like above, for each company we have pricing on (so it repeats across the sheet lengthwise). The goal is to determine whether to buy item 1 from JOHNS COMPANY or one of the other ones, after some comparisons.
Sometimes buying 1,000 even if you only need 750 is better. I could do a min function for each row, but then I have the lowest price without any seller information. If I could get the cell address of the lowest price, I could find who has the lowest price. At this point though transforming the data into pivot tables or creating a vba macro starts to seem more appealing.
Ideally I won't have to change the format of the data, even if it isn't optimal. The formula I would need to make has to compare 10+ companies so it would probably turn out like a =IF(AND(OR(... lots of stuff ))) nightmare. Let me know if I left anything out or if you know of anything off of the top of your head to get me pointed in the right direction. Thanks
