## Printing items that have been checked and sorted by company

238 Views 5 Replies Latest reply: Nov 30, 2012 1:08 PM by twinjayhawks
Calculating status...
Currently Being Moderated
Nov 29, 2012 5:32 PM

I need to create an order form.

The form will have company A and company B. Both companies will have different prices for each item that they sell. As I go through my inventory each week I need to create a form of things I need to order. I would like to check an item to order from the cheapest company... Then print out all of the items I need to order from each company.

Does anyone know if this is possible?

• Level 6 (10,525 points)

I took a stab at it. Lots of formulas. I'll do my best to explain. I did it all in one sheet to make it easier. You can drag and drop the various tables to different sheets later.

Now for the setup and the formulas:

"Company A Price List" and "Company B Price List" tables:

This is data you enter. No formulas.

"Items to Purchase" table:

You enter the item name in column A.  Note that it each must be spelled exaclty as it appears in the price list(s) or it won't find it.

You enter the quantity to purchase in column B

C2 =IF(A2="","",IFERROR(VLOOKUP(A2,Company A Price List :: A:B,2,0),"Not Available"))

The meat of this is the VLOOKUP. If an item is listed in column A it tries to find the item in Company A's price list and return the price of that item. If it can't find it the lookup creates an error which is caught by the IFERROR function and the result is "not available".

D2 =IF(A2="","",IFERROR(VLOOKUP(A2,Company B Price List :: A:B,2,0),"Not Available"))

Same idea as in C2 but is looking at Company B's price list.

E2 =IF(A2="","",MIN(C2,D2))

Picks the minimum of the two prices

F2 =IF(OR(A2="",E2=0),"",IF(\$E2=C2,C\$1,D\$1))

Finds which column has the same price as the minimum price and returns the value from the header of that column. I named columns C and D "Company A" and "Company B" for this reason. Note that Company A will be the default if the two companies have the same price for the item. The IF(OR(...) part at the beginning is for if no item was listed in the row or the product is unavailable (price in column E is 0).

G2 =IF(\$F2=G\$1,ROW(),9999)

Indicates which rows have Company A items. Again, I named the header of this column "Company A" to use it in this formula.

H2 =IF(\$F2=H\$1,ROW(),9999)

Indicates which rows have Company B items.

Fill all these formulas to the bottom of the table. You can hide the last two columns after everything is complete, but not just yet. You can hide C & D too if you don't want to see them.

"Company A Order Form" table:

The last row is a footer row. the formula in it is =SUM(D)

A2 =IFERROR(OFFSET(Items to Purchase :: A\$1,SMALL(Items to Purchase :: G,ROW()-1)-1,0),"")

B2 =IFERROR(OFFSET(Items to Purchase :: B\$1,SMALL(Items to Purchase :: G,ROW()-1)-1,0),"")

C2 =IFERROR(OFFSET(Items to Purchase :: E\$1,SMALL(Items to Purchase :: G,ROW()-1)-1,0),"")

Each of these looks for a number in column G of the "Items to Purchase" table and returns the data from that row. Trying to explain this would take some time.

D2 =IF(A2="","",C2*B2)

Fill these formulas to the bottom of the table (not including the footer row)

"Company B Order Form" table:

Once you have Company A's table done, copy it, paste it, change the table name, change all the cell references in row 2 from "Items to Purchase :: G" to "Items to Purchase :: H" then fill down with the modified formulas.

I hope this is somewhat what you were looking for. It is simple in theory but gets slightly complicated to implement, at least if you are new to spreadsheets. With more time I'm sure it could be simplified. This was my first stab at it.

• Level 6 (10,525 points)

Yes and that would simplify things a lot.

In the "Items to Purchase" table I had to add a second header row so "Company A' and "Company B" would span two columns each while not messing up the formulas and while still allowing the table to be sorted.

You enter all possible items in column A, the prices from each company in their respective columns (or NA if not available from that company), check one of the two boxes in the row (not both or you'll double your order), and the quantity. So, columns A-G are user data, no formulas.

H2 =IF(B3,ROW(),9999)

I2 =IF(E3,ROW(),9999)

Fill down with these formulas.

In the "Order Form" table I came up with better formulas, using INDEX instead of OFFSET,

A2 =IFERROR(INDEX(Items to Purchase :: A,SMALL(Items to Purchase :: H,ROW()-1)),"")

B2 =IFERROR(INDEX(Items to Purchase :: C,SMALL(Items to Purchase :: H,ROW()-1)),"")

C2 =IFERROR(INDEX(Items to Purchase :: G,SMALL(Items to Purchase :: H,ROW()-1)),"")

D2 =IF(OR(A2="",B2="NA"),"",B2*C2)

Fill down with these formulas.

Copy/Paste the table and make the necessary changes like before to create Company B's table

If you didn't think of it already, you can copy/paste the formulas from here into your document to make it easier. Use the same table names. You can change the names later if you want to.

Also, Sheet 1 in the screenshot above isn't necessary. It was the one I did last night.

#### More Like This

• Retrieving data ...

#### Legend

• This solved my question - 10 points
• This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.