A friend of mine was faced with this problem when preparing an offer to a customer, and asked me to save him from doing tedious manual entries for some 50 products.
He had to make this from his own Product Master File
|SKU||SKU Description||Width mm||Depth mm||Height mm|
look like this in the customer supplied Offer Form
|SKU||SKU Description||Pack Size|
I suggested the following solution based on the KISS-principle.
Step 1 – Concatenate to create a column that matches the customer “Pack Size” format.
You can lock the columns by adding $ before the letters, example $C2 to protect your formula. If the order of measurements in the Product Master file is different from the customer supplied Offer Form just adjust the order in your CONCATENATE.
This column should be in the Product Master file to the right of the SKU id column.
Let’s see what we have done:
CONCATENATE is a formula for combining the content of one cell with the content of one or more other cells. The & is the workhorse here, just like + is for adding. The cool thing is that & let’s you put more than just the contents of another cell into your destination cell. This means that as long as you use the double quotation marks “ ”around it you can add any string of letters or numbers you need, or just add a space if you are making a list of names for example.
We combined Cell C2 with an x and cell D2, added an x and cell E2. NOTE! You have to use all these & to make it work, and there is no technical limit to the CONCATENATE string, if needed it could be a mile long.
Now we have a column in the requested format, time to get it into the customer supplied Offer Form!
Step 2 – Vlookup the Pack Size to get it into the Offer Form.
What we tell Excel to do here is to search for
Value in column A for the row number of the current cell in sheet 1, Columns A through C, and then return the contents from the 3rd column and the cell in the row that matches the Column A value in the Offer Form. Building it this way will make the formulas perform their tasks for every row you need to submit. At times Excel tries to lock cells with this type of $A$2 behaviour – don’t accept that, your row numbers must be flexible, so remove the $ in front of the row number!
Step 3 – making the Offer Form portable.
COPY column C and PASTE VALUES
It is easy to forget, but absolutely necessary to do this when your source file for the VLOOKUP is not included in the send-out to the recipient. If you don’t, your “Pack Size” column will only display ######### or !ERROR!
Hope you enjoyed this simple tip, my inspiration for this was Chandoo at http://chandoo.org/ where I learned how to see the patterns that can be used with many of the functions available in Excel, like the &”x”&.