Help comparing prices and returning with conditions

Hi!

Needing some help on this sheet I am working on for quoting from vendors. As you'll see on the attached, my quotes get a little complex. I have multiple vendors, quoting different parts, at various price breaks.

Right now I have the min price showing up for each quantity (5, 10, 20, etc.) in columns K:AD paired with the vendor that offers the part at the lowest price.

That's all nice and dandy, but what I would really like is if column C could return the vendor if it is the same at each quantity for one part, and if they have in stock (D:J).

Sure, you could say well, why don't you just go through and compare manually. Well, this one quote I'm about to get started on has 663 different parts, so it can be very time consuming without this tool.

Optionally, I'd like to know what the formula would be to have the lowest price show up only if the part is in stock at that vendor, that way the prices shown are for parts in stock.

Thanks!

Hi

Can you please update your example file to show some actual results and explain why they calculated. From what I can see of the 2 examples, you won't get a result because you don't have a situation where all the vendors are the same (row 4), or the vendor that has all the parts (row 3 vendor4) has no stock (G3 = 0).

rylo

Thanks for helping me out. I've filled out some numbers. There should be scenarios where there is no stock for a vendor with lowest price, some where there is no stock for two vendors with lowest price, some where there is two lowest prices, three lowest prices.

End result is I would like to be able to have returned the lowest priced vendor that has stock, or optionally just the lowest price vendor (sometimes I can accept the lead time) depending on what I am looking for. Two formulas would be ideal.

Hi

OK, here's a start.

K3: =MIN(IF((D3:J3<>0)*(AE3:AK3<>0),AE3:AK3,999))
L3: =INDEX($1:$1,MIN(IF((D3:J3<>0)*(AE3:AK3=K3),COLUMN(AE3:AK3),999)))
Both these formulas are array entered.

This should give you the lowest price for a vendor with stock, and bring back the first relevant vendor (if there is more than 1 candidate).

See how it goes and let me know.

rylo

Hi

Here's one way. It will put the number of vendors with that price after the vendor. It doesn't distinguish between vendors with stock, or vendors without stock, just a vendor with that price.

L3: =INDEX($1:$1,MIN(IF((D3:J3<>0)*(AE3:AK3=K3),COLUMN(AE3:AK3),999))) & " (" & COUNTIF(AE3:AK3,K3) & ")" (Array entered of course)

Filter: think so, but would have to know exactly what you are filtering and where to test it out.

rylo

Works great! Thanks.

One last item I believe I need. How can I get supplier column C to show the vendor or vendors that have the best price(s) across the row?

I.E. line 1 would say Vendor1 line 2 would say Vendor4 Vendor2 Vendor 6 line 3 Vendor2

Hi

Easiest way to do that is to build your own function. Are you able to have a macro in the file? Some organisations don't allow macros to be actioned.

rylo

Macros are not an issue.

Hi

OK using the example file from #3, the K3 formula from #4 and the L3 formula from #6 then do

Insert the following code into a general module in the workbook.

Please Login or Register  to view this content.

Invoke the function.
C3: =myfunc(K3,AE3:AK3)

Copy down all the relevant formulas to row 19. You should find a comma separated list of the relevant vendors in column C

Remember that you will have to save the file as a .xlsm (macro file).

rylo

Hi

1) not stock question - good enough, though this is a change from the original brief.
2) Put in an IF statement, so that if min price is 0, then result is "" else do the formula. O10: =IF(N10=0,"",INDEX($1:$1,MIN(IF((AH10:AN10=N10),COLUMN(AH10:AN10)))) & " (" & COUNTIF(AH10:AN10,N10) & ")")

3) concatenate the results from the individual myfunc functions. F3: =myfunc(N3,AH3:AN3)&", "&myfunc(P3,AO3:AU3) and so on...

rylo

Rylo, the problem if I concatenate is that I get 10 times the same vendor in F, I only want each vendor to show once.

Also in F10, each vendor now shows up ten times lol. Attached new file for you to take a look.

Hi

OK, try this

Please Login or Register  to view this content.

Using your example file, F3: =myfunc2(N3,AH3:AN3,P3,AO3:AU3,R3,AV3:BB3,T3,BC3:BI3,V3,BJ3:BP3,X3,BQ3:BW3,Z3,BX3:CD3,AB3,CE3:CK3,AD3,CL3:CR3,AF3,CS3:CY3)

See how that goes.

rylo

Doesn't work... See line 2. Only Future and Avnet are shown, yet I would like to see Future, Avnet, Mouser and Newark.

Hi

Oops, sorry. Forgot to complete the array of ranges to action after testing.

Please Login or Register  to view this content.

rylo

Now we're talking!