krutobits.blogg.se

The Data Model works only with Table objects. You can't create a relationship between ordinary data sets. SEE: Tap into the power of data validation in Excel (free PDF) (TechRepublic) Convert the data to Table objects By relating the two data sets, you can combine the data in meaningful ways. If you're unfamiliar with the term, a relationship connects two sets of data by a common column (field) of values. If you've worked with databases, the term relationship is known to you. Figure A Two data sets related by the Shelf Code field. Displaying the description instead of the shelf code will improve the readability of the final product.

Using Excel's Data Model feature, we'll display the description field instead of the shelf code when grouping and analyzing the values without using VLOOKUP() or any other functions. We have two tables: the data table on the left and the lookup table on the right. In this case, the common field is Shelf Code, as shown in Figure A. Thanks to Excel's Data Model, you can bypass VLOOKUP() altogether and move straight on to the PivotTable.Įxcel's Data Model creates a relationship between two (or more) sets of data using a common field. Then, you'd most likely use a PivotTable to analyze the data set that now includes the description for each product. But, even if it slows things down, it still works. It's what we know, and it works well unless you have thousands of records to analyze. Most of us would use VLOOKUP() to add a column to the original data set.

So, you import a table of shelving codes that includes a helpful description, but how do you add the description with each record?

You've imported a table of products and each product has a shelving code, which is, meaningless to you. Now let's suppose you're working for a large grocery franchise and you want to analyze shelving data.