Vlookup and Sumif Formulas to assist summarizing queried data
Vlookup and Sumif Formulas to assist summarizing queried data
When accessing data from Foundation through the MS Query tool, at times it is necessary to join multiple tables together to retrieve the required information. At times this is very easy, other instances are rather frustrating. The following demonstration shows examples of how data may be joined via formulas in Microsoft Excel. If we want to create a simple list of job records with their associated project managers, we would start by querying the JOBS table. From the Jobs table, we would pull the job_id, description and project_manager_id fields.
Everything looks great so far, but I would also like to include the description of the project manager field in this list as well.
Add the PROJECT_MANAGERS table to the query.
- MS Query makes some "assumptions" and creates joins between the two tables.
You will notice that the MS Query tool "joins" the two tables based on a common field or fields. The company_no field will always be joined between existing tables. You will notice that one or more fields within a table will be highlighted. These fields are known as a "key identifier". These are the fields that cannot be changed once entered in Foundation. Basically any maintenance ID (or number) once entered in Foundation is now "known" to all the other tables in the database by that key identifier. These key identifiers could potentially be referenced on hundreds of tables throughout the database, which is why ? once they are entered, they cannot be altered. Once the join is created, you must review your data for integrity.
This is the Query that contained ONLY the JOB table. Notice, there are MANY more jobs returned in this query as opposed to the query that contains two tables. On the right is the query with both tables present. Notice that Jobs 160 ? 170 are not on the list on the right.
?
The "joins" in the MS query tool are rather particular. By default they want to satisfy a simple condition. If there is a project_manager_no on the JOBS table, then match it up with the project_manager_no on the PROJECT_MANAGERS table. When there is no project_manager_no entered on the JOBS table, the query tool looks at the fields as a "blank". Since there is no such animal as a "blank" project_manager_no, the link is not satisfied, and the record is dropped from the queried data. NOTE: There are some advanced programmatic / coding solutions to these issues, but for the use of simple queries, we offer these solutions. First, start by removing the project_managers table from the query. Single click on the table and press the delete key on your keyboard. You will notice that all of the data has returned.
Return the data to Excel.
Access Sheet2 in the Excel workbook and start a new query. In this query, add the project_managers table. From this table, choose project_manager_id and description.
Return this data to Excel ? Sheet2
The Vlookup function will allow us to "join" the information from these two sheets together.
VLOOKUP function in Excel.
We have two worksheets with data. We have the JOB table information on Sheet1 and the PROJECT_MANAGERS information on Sheet2. We will use the VLOOKUP (vertical lookup) function in Excel to "find" the match between the value(s) on Sheet1 and Sheet2.
Sheet 1
Sheet 2
When I "find" a value in Column C of Sheet1, I want to return the value from Column B in Sheet2 when the project_manager_id values are the same. The formula is written as follows:
=VLOOKUP(C2,Sheet2!A:B,2,FALSE)
The Vlookup function is broken down into 4 parts.
1. What Value do you want to find ? (lookup_value) 2. Where do you want to look for the value ? (table_array) 3. How many rows from the leftmost column in the table_array does the requested data
reside ? (col_index_num) 4. Do you want an exact match, or a "close" match (range_lookup)
These 4 Items are broken down as follows: The Lookup_value is the cell on the spreadsheet that you want to pull information from. Although the help dialog state that it must be a value from the "leftmost column of a table", this is not true, you may create a vlookup anywhere in a spreadsheet. In this example, we are starting the lookup in column "C". I like to refer to this as "what are we looking FOR" ?
The table_array is the selected columns from which to look for the associated value defined in the first step. In this example, our array is on Sheet2, Columns A:B. Even though we are only looking to an array with 2 columns, these arrays can be much larger.
The Col_index_num is the number of columns from the first column in the array to locate the required data. In this case, the array is A:B. We are looking to return the data from column B. Column B is the SECOND column from column A, so we enter a 2 in the Col_index_num field.
The Range_Lookup is the most illogical "logical"' value Excel offers. To find an exact match between the fields on the two sheets, you will select "false". To find the first, or closest match, enter "true". 99.99% of the time, you will want to enter "false".
When the formula is written properly, you will see that Sheet1 has the associated Project Manager description pulled from the table on Sheet2.
Everything looks great, except for the #N/A errors in the rows where employees have no project_manager_id identified. Excel 2007 has a nice formula to address any and all errors in calculated cells. This new
formula is called =IfError
If we append the original equation with the following:
=IfError(VLOOKUP(C2,Sheet2!A:B,2,FALSE),"")
This formula states that if there is an error with the specified formula, return "" (two sets of double quotes = blank). For calculated numbers, such as a #DIV/0 error, you may want to use a 0 in place of the "".
Resulting data:
Resulting data:
Use the filters, and this is a nice list to show who is the PM on a particular project. (Use filters and criteria in the MS query to show Active jobs only).
This theory may be applied with multiple fields within a worksheet.
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- rental property formulas to know
- formulas to solve triangle dimensions
- formulas to solve triangles
- excel change formulas to numbers
- recursive and explicit formulas calculator
- area and perimeter formulas pdf
- area and volume formulas pdf
- i m happy to assist you
- excel formulas to add rows
- convert formulas to value
- formulas to convert decimal to percentage
- happy to assist synonyms