I’ve got a file that has ‘open item’ invoices instead of account totals. That is, there are duplicate account numbers with different amounts. I’d like to add the amounts together and out put a single record with a single account number.
Obviously I can accomplish this with an Easytrieve. Read a record and write it to working storage. Read the next record. If the account number is the same, add the amounts. When a record doesn’t have the same account number, write the stored record to output.
But that’s tedious. Is there a way to accomplish this in Excel?
A PivotTable will do it. I don’t have Excel anymore so I can’t give you exact instructions, but you should just be able to set the rows to be the account number, and the values to be the sum of the amounts. It’ll group all the identical account numbers together and give you the totals in the value column.
I have no clue how to use a pivot table. On the rare occasion I come across one, I copy everything and paste it as text into a new document so I can work with it.
I highly recommend learning how. Excel without PivotTables is missing a huge portion of the functionality.
Select your dataset, ensuring that it has named column headers. Click the “insert PivotTable” button, wherever it might be. Find the “row” section and point it to the column containing the account numbers. Find the “values” section and point it to the column containing the amounts, and set summarize/group by to SUM.
Sorry, I can’t be more specific than that. I use Google Sheets, which has the same feature but with slightly different names and such. Maybe someone else with Excel can be more helpful.
Pivot tables are simple and powerful once you have enough experience to use them properly.
Before that they are confusing, complex and quirky. What you want to do never matches what you think you are doing.
But this sounds like the best example to work with, you have one column with repeats and you want to sum all rows of the same invoice number. Pivot tables don’t get easier than that.
I’m the computer expert, and my wife knows far more about them than I do - because she has to use them.
A pivot table is the most appropriate solution for this problem. It’s not hard.
I recommend an Excel board excelforum.com (where I am a moderator). It also allows you to attach files to your post and post inline images in your post.
Using Excel 365 or other recent version:
Click on a destination cell where you would like the pivot table to appear
Go to Insert > Pivot Table
Select “Select table or range” and then highlight the columns with your data. They must have headers at the top of each column.
The destination cell you selected should be in the Location box below that. If not, update the box.
Click OK
A placeholder for your pivot table will appear at the destination cell. To the right is a panel that allows you to build the table.
Find the name of your “account number” column and drag it down to the Rows box.
Find the name of the “open item amounts” and drag it into the Values box.
The default should say “Sum of Amount” or "Sum of " whatever your heading is.
Close the panel
Your pivot table is now complete
If your source data changes, right click anywhere in the pivot table and click Refresh.
I think it is easier to create the pivot table by clicking anywhere in the data and then selecting Insert > Pivot Table.
The create pivot table box that pops up has the range populated and asks whether you want it on a new sheet or an existing sheet. The data selected for the pivot is outlined so that you can see if you are missing columns due to absent headers.
Thanks for the detailed answers. I’ll see about learning pivot tables. But for now, I’m going to have to write an Easytrieve. I get data from a company who used to send open invoices (they don’t anymore), but I think I still have the ‘dedup/combine’ program I can adapt. I’m still in the first four weeks of the month (he said sardonically), and this will be the fastest way to get the file.
Your Easytrieve solution won’t work unless the duplicate account numbers are all adjacent (which would be the case if they’re sorted). Maybe not a problem, but something to watch out for.
To be honest, it seems that you’re using Easytrieve as a crutch. I realize it’s familiar to you, but if you are expected to use Excel in your job, you should be learning its basic functionality. PivotTables can get complicated, but they aren’t inherently an advanced feature.
I was hired because I knew Easytrieve. Good program. Also, a lot of data needs to be put into specific fixed formats or aging and customer files need to be combined into a flat file. No worries about sorting. I sort the Excel file and I run a sort.
In fact PivotTables are designed to be a way of simplifying a sequence of sorts and conditional filters into a single integrated function. @CookingWithGas has laid out the detailed sequence but it is actually even easier than this, because the real process is really three steps: selecting the data set, and applying a PivotTable, and the selecting the column you want to index, and selecting the value to sum over. I don’t know what is involved in writing an ‘Easytrieve’ script but I guarantee it is easier and more flexible to create a PivotTable. Also, note that while “PivotTable” is a Microsoft trademark, pivot tables as a general function predate Excel and exist in pretty much any spreadsheet-type applications including Google Sheets, MacOS Numbers, and Pandas (for Python), so learning the functionality in one tool is generally applicable to all of them with minor modifications. It is worth spending the half an hour or so to just play around with PivotTables because they require no programming whatsoever can can perform pretty much any kind of sort/filter/group operation that you can reasonably do in a spreadsheet.
As for merging files and putting variable length data in fixed formats, that is what Perl or Python is for and will do with ease. If you don’t want to learn them yourself you should be able to pay a local community college student beer money to gin up a script and some kind of input definition to automate that.
Easytrieve is a sequential programming language developed in the '60s. Think BASIC (as I learned '79-'80) and maybe some COBOL. First, convert the alpha-numeric amounts (spaces plus ####.## to numeric ############). Then add everything up and divide by 100 for whole dollars. Then, basically, IF ARNUM = WS-ARNUM, add the amounts. ELSE, write the record.
The company isn’t going to hire anyone to do that as long as I’m there. They will have to when I retire, which is uncomfortably closer for me than I imagined. (Who am I kidding? I’ll die at my desk!)
I put PivotTables in the same category as regular expressions or Reverse Polish Notation. They require a bit of a mental jump to get started. But once you achieve that, you realize that they’re a really good abstraction for the process they are designed to solve. For the limited domain they cover, it would be hard to think of a better tool to summarize and collate tabular data. You pretty much have to go to SQL or the like if PivotTables prove insufficient.
As noted, the steps to configuring the PivotTable are trivial (because it’s a powerful tool). But Johnny_L.A has to start thinking of the operation in terms of grouping/classification/reduction operations as opposed to the iterative approach that Easytrieve apparently encourages. It’s more like a mathematical approach with set theory and equivalence classes.
I’m making it sound harder than it is, of course–one can get the basic idea by just playing around. But it does require a change in how you think about problems.
If you want to be a Excel PivotTable “power user” (ugh!) then sure, you need to understand the concepts behind how pivot tables work in general, and how Excel implements their PivotTable function. But for what @Johnny_L.A is doing (summing all entries relating to a key value), this is literally the most trivial application, and in fact if you select Insert>Recommended PivotTables this is the default option, so it is essentially a one step action. There may be other reasons to use Easytrieve if there is some other data formatting to be done (something Excel is notoriously weak at) but for summarizing tabular data it is a trivially easy solution.
I wrote the Easytrieve this morning, and it looks like the numbers are correct. Almost 50,000 records were reduced to fewer than 8,000. I’ve sent the output to the account executive so she can check my work. (It’s always good to have another pair of eyes.)
I think our Excel guru in the office tried a pivot table yesterday. Apparently she was able to de-dup the file, but did not capture the names and addresses. (I didn’t see the file – or indeed, her – yesterday.) I’ve emailed her asking if she can show me what she did and show me how to do the pivot tables.
It’s not our accounting system. We receive commercial accounts receivable from many companies. I’m trying to get away from Easytrieve, because I’m not going to last forever. Also, since I became my boss I don’t have a lot of time. And Big Multinational Corp has plenty of programmers to put raw data into whatever (fixed-position text) format they want. They have no problem with open-invoice files. It’s a different (very big) company that can’t handle it. So the de-duping is only for this one company that wants its data reported to this other company.
