Looking Up Data from Several Tables with Different Structures On-the-Fly

Quite frequently, we need to find some data in another Excel table. The basic case is quite easy to handle using the INDEX-MATCH approach, as is the case when we have to find data based on more than one criterion. However, we were talking about looking up data in a single table all the time. What if we have several data tables? Well, if these tables have the same structure (read: same columns) then we can put all those tables into one and reference it in the INDEX and MATCH functions. But if these tables have different structures, and it’s either impossible or impractical to create a common structure? We need a solution that’s adaptive and works on-the-fly, that is, if we change the structure of some table or add/remove tables, we don’t want to modify the formulae.┬áLet’s see how we can solve this case. … More Looking Up Data from Several Tables with Different Structures On-the-Fly

Advertisements

Aggregating Shipment Statistics Into Day Groups

We’re all frequently asked to compute statistics of micro-level data at group level. If you have to base statistics on micro-level data then there’s not much of a problem. But what if you have to base some statistics on micro-level data and some at subgroup-level data with too many subgroups? How to do that without creating additional tables? … More Aggregating Shipment Statistics Into Day Groups

Ranking Tender Offers

Many companies periodically run tenders to gather offers from several prospective B2B partners and choose the best ones. The problem is that the results of a tender come as a set of files, one per prospective partner. How can you easily build a single file that shows the best offers in every tender category? … More Ranking Tender Offers