Find differences in two lists
June 9, 2010 at 5:26 PM | Posted in General | Leave a commentTags: aggregated, compare
Dick describes a situation where he has two lists of invoices. In each list, the invoices are separated into rows according to some other criteria.
In general, however, the various numbers for each invoice should sum up to the same number in both lists, but he knows there’s a problem and wishes to find out where.
This would indicate to him where the problem is, in the code that created those lists in the first place, or indicate a human error in the data entry process.
He goes on to tell the tale of how he tried pivot tables, SUMIFs, and custom-built dialogs.
Many commenters suggested to use SQL in one way or another. I’ve covered that topic before, when Dick himself brought up the subject. I think it’s a great approach, using aggregate functions to do most of the work for you.
However, he’s saying that the column headers are unpredictable, and that he wishes to find a more generic solution to situations like this.
I find it hard to believe a generic solution exist, because this is a very specific problem, but the suggested approach should be OK for most people, with some tweaks.
Filling the gaps
May 7, 2010 at 8:59 AM | Posted in General | Leave a commentTags: aggregated, gaps, header, subtotals
I used to get tabular reports in Excel from various sources: From my phone bill invoices, from the sales department, from customers who needed help, and so on.
Every so often, I found myself working with an Excel sheet that was designed to be printed. That is, some of the columns had values only in the first row in every contiguous batch of rows.
This technique is useful for printing. It’s easier on the eyes of the reader, but it causes havoc if you try to sort the table…
I tend to find a relation between this kind of column and a row of sub-totals. Both have information that is related to a number of rows. Sub-totals are aggregated data, but these are a description or a header for the following set of data.
Luckily there are easy ways to convert these kinds of columns back into full columns. Well, at least not too complicated ways.
Susan Harkins describes such a process, which starts out with adding formulas in all the empty cells (at once), and ends with converting the formulas to literal values.
It doesn’t cover the case when you need to split the cells first, but that’s pretty straight-forward anyway.
Blog at WordPress.com. | Theme: Pool by Borja Fernandez.
Entries and comments feeds.
