A not so advanced, but useful, Excel tip.

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
Prod1 Funny Stuff 158 210 242
Prod2 Funnier Stuff 281 264 277
Prod3 Funniest Stuff 256 224 252

 

look like this in the customer supplied Offer Form

SKU SKU Description Pack Size
Prod1 Funny Stuff 158x210x210x242
Prod2 Funnier Stuff 281x264x264x277
Prod3 Funniest Stuff 256x224x224x252

 

I suggested the following solution based on the KISS-principle.

Step 1 – Concatenate to create a column that matches the customer “Pack Size” format.

=CONCATENATE(C2&”x”&D2&”x”&E2)

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.

=VLOOKUP(A:A;SHEET1!A:C;3)

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”&.

                                       

Advertisements

About powerpivotseth

As long as I learn, I live, and to that end I am now immersing myself in the new Microsoft Power BI applications available through Excel. I was quite good at plain vanilla Excel, but even so, I ended up in "No, that is not possible" dead ends at times. Then I discovered Power Pivot and all my previous dead ends opened up into veritable eight-lane interstate highways!
This entry was posted in Power BI. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s