How can I automate lining up cells in excel or open office?

March 24, 2008 3:06 PM

Is there a way to automate this very tedious spreadsheet task?

I am currently aggregating the sales and inventory data for three stores that share inventory. Unfortunately, the software we are using won't allow us to export both sales and inventory in stock to the same spreadsheet.

To make matters worse, we can't delete unused inventory codes, so my inventory export is 18000 rows long. A sales export will typically only use between 500 and 2000 of these codes, and I have to go line by line, inserting cells so the sales data lines up with the inventory. Line by line, for 18000 rows. I have to do this three times, once for each store.

To make things even worse, I've been suffering from intense migraines lately, and this isn't making them better.

I was wondering if there was a way to automate the process of getting cells with a given code, and the associated cells with the sales data to line up with the cells of inventory data. The codes are a letter or series of letters followed by a dash and a 4 digit number, for example A-1134, ECO-0045. A few codes for various reasons don't adhere to this standard, like OM-4 or H-2334.5.

If this isn't clear, I'll try to provide a concrete example.

I use Open Office at work, but I have access to Excel if needed.

posted by [expletive deleted] to Computers & Internet (14 answers total)

So you've got a sheet of sales data. And for each item in that sheet, you want to pull information for each product sold from the inventory page (based on the "code" that is shared between both sheets)?

If so, then you want to look into the VLOOKUP formula in Excel, for which there are Open Office equivalents.
posted by Doofus Magoo at 3:19 PM on March 24, 2008

I'm not sure I understand exactly what you have and what you are trying to do, but it kind of sounds like a VLOOKUP query might allow you to match up the Inventory codes with the Sales data.
posted by gemmy at 3:20 PM on March 24, 2008

vlookup works best if it is a one to one relationship, meaning that there is only one EXAMPLEA record in each spreadsheet, and not one EXAMPLEA in spreadsheet1 and five EXAMPLEA in spreadsheet2.
posted by Pants! at 3:31 PM on March 24, 2008

Don't know about OO, but in Excel, you'd want to look into the LOOKUP, HLOOKUP and VLOOKUP functions. I'm imaging you've got two sheets in an Excel file. As long as you've got a uniform key for doing the lookup, you can get info out of one cell in one table and into a cell in the other.

So you're trying to get info out of the "export" sheet and into the "master," lets say. Let's assume A) they both use identical inventory codes, listed in column A, B) the info you need to copy is in column B of the "export" sheet, and you need to get it into column C of the "master" sheet. You'd fill all the cells in column C of the master sheet with something like this:

=VLOOKUP($An,export!A1:C500,2)

Here, $A is the "key" column the "n" is the current row number (and should increment when you do a "fill down", the export! refers to the sheet, A1:C500 refers to the range of cells on export, and 2 refers to the second column on that sheet from which you'd obtain the value to return.

You may need to play with this a bit, but I think it'll work.
posted by adamrice at 3:37 PM on March 24, 2008

VLOOKUP looks like a promising. The relationship is one to one, there will only be one record for each code in each spreadsheet.
posted by [expletive deleted] at 3:41 PM on March 24, 2008

I use VLOOKUP all the time for something similar to this. Then I use autofilter to filter out the N/A records (those that don't match).
posted by desjardins at 4:02 PM on March 24, 2008

So I'm trying this, but I don't know how to fill down with the first parameter incrementing while having the second static.
posted by [expletive deleted] at 4:24 PM on March 24, 2008

Desjardins, how do you use autofilter as you described?
posted by [expletive deleted] at 4:29 PM on March 24, 2008

Once you have the data sorted, you can also use something like =IF(ISERROR(Cell#)"X","") in an adjacent column. You'll get an X next to cells with an error, highlight, sort (alt+d+s) and you'll have all of your X cells with errors grouped together and your non error cells grouped together. Probably similar to autofilter, but I'm not in front of Excel to check.
posted by Frank Grimes at 4:42 PM on March 24, 2008

This works if I edit each line manually, but I seem incapable of getting the first parameter, the search criterion to increment while having the array parameter static, even if I fill down from two or more rows where the the search criterion increments but the array is static.
posted by [expletive deleted] at 4:51 PM on March 24, 2008

Desjardins, how do you use autofilter as you described?

Your last two posts lost me, so I'm going to explain my process in simple terms and I hope that helps. I'm not talking down to you, I'm just not sure I understand what you're asking now, and I hope my step-by-step helps. This concerns Excel 2003; I have no knowledge of Open Office.

I have OrdersToday, a small list with unique customer numbers, and MasterList, a large master list of customers. I want to find out which customers are on the master list because the master has more information about them (say, their phone number).

In MasterList, I insert a column to the right of the customer ID column. Let's say this is column F.

My formula in F2 (since line 1 is the header row) is something like =VLOOKUP(E2, [OrdersToday]Sheet1!$A:$A, 1, 0)

Then I copy that formula down the whole column, usually by double clicking on the bottom right corner of the cell containing the formula. Because my master list is freaking huge and my orderslist is small, most of the cells now contain N/A.

I go to Data > Autofilter and I get a drop down box at the top of each column. In column F, I choose "custom" from the drop down box and choose "does not equal N/A" in the dialog that comes up. Presto, I have a list of customers who are on both the orderstoday sheet and the master list.
posted by desjardins at 5:49 PM on March 24, 2008

To keep the lookup array static, interpolate dollar signs in the address depending on whether you want the row, column or both to not change on copying the formula. E.g., instead of

=VLOOKUP(Sales!A1,Inventory!A1:C500,2)

... you'd do this:

=VLOOKUP(Sales!A1,Inventory!$A$1:$C$500,2)

This will keep the "A1:C500" reference constant regardless of the cell(s) to which you copy the formula. (You're prefacing the column letter with a $, because you want it to remain static, and prefacing the row number because you also want it to remain static)
posted by Doofus Magoo at 6:20 PM on March 24, 2008

Ok, I'm an idiot who doesn't know how to use an absolute reference properly.

Thanks to everyone who helped me with this. This will save me hours, and the company literally hundreds of hours of labour over the course of a year.
posted by [expletive deleted] at 6:22 PM on March 24, 2008

By the way, with that amount of data, you/your company would be smart to think about moving to a database. Even MSAccess would be work fine and would make life much easier for you and everyone else. It would work like this: The data go into the database, and you get reports, which are set up to show exactly what data you want to see, in the format you want. No more fiddling with getting cells to match up! Of course, you'd want to find someone who knows how to set up a database and reports, but once that's done, you'd be sittin' pretty.
posted by exphysicist345 at 2:47 PM on March 25, 2008