This Post Will Save You Two Hours Per Scale Out • 8 May 2007 • The SnowBlog

This Post Will Save You Two Hours Per Scale Out

          
...if you happen to be a publisher who receives scale out orders from large retailers and need to send them to LBS. Obviously this is a select population, so more details below should you require them. Seriously, I can't believe what I've just heard. I was on the phone to LBS, our distributor, who casually mentioned in passing that most publishers receive an excel sheet from the retailer containing store-level orders, and then manually type that data into LBS' template. 

MADNESS. 

Ok, here we go. Create a new sheet on LBS's template. Paste the retailer's order into it. Go back to LBS' template and go to the first cell where you need to type in data. Type instead 
=VLOOKUP(F42,Sheet1!F:L,5,FALSE) 
where:
F42 is the cell with the first store code in
Sheet1!F:L is the data with the retailer scale out, and F is the column with store codes in
5 is the number of columns to the right of the column with the store code in. 

Then copy that formula down through all the stores. Delete any that come up #N/A. Then select all the cells you've done a calculation in, right-click and select 'copy', right click and select 'paste special / values'. Then delete Sheet 1 where the original data is. 

And it takes 2 minutes, not two hours. Honestly, if there are publishers out there not using VLOOKUPS for this they should feel very foolish. 
Emma