Combining data of two different reports (i.e. Accounts and Contacts)

Sometimes you need to combine data from two different reports in Severa for example Accounts and Contacts. This cannot be done directly in Severa but if you are using Microsoft Excel (or similar), you can combine the two reports by using VLOOKUP –function.

1. First you need to create the two reports in Severa and export them to Excel. NOTE: Both reports need to have one column which has the same information: i.e. Account name.

• Contacts
• Accounts
• Select one of the reports to be your “primary” worksheet where you import the data from the secondary worksheet. In this case I select Contacts to be the primary worksheet and Accounts as second.
• We will use the “Account name” to lookup the “Industry” and “Account type” into our primary worksheet (Contacts).
• Please notice regarding the secondary worksheet (Accounts): The left column must contain the values being referenced, in this example left column A = “Account name”. Also you can’t have duplicate values in the leftmost column of the lookup range, so each account name can only be there once, duplicates will cause an issue.
• Add new columns on your primary worksheet (Contacts) that will display the info pulled from the Lookup table. In my example, I added a column called Industry in Column E and Account type in Column F.
• Place your cursor in the first blank cell in the column. In my example, this is cell E2.
• From the Formulas menu, select Insert Function. Or click the fx –icon in the formula bar. The Insert Function dialog will appear.
• In the Search for a function: text box, type “vlookup” and click OK.
• After you click OK, Excel’s Function Arguments dialog appears and allows you to define the four values.
• Lookup_value – Select the cell of the first account name in to this field. We are requesting Excel to take the value of A2, which displays as the Account name of “Account Ab”, and find the matching industry on my lookup table on the Accounts worksheet.
• Table_array – This is the range for your lookup table. The range can be on your existing worksheet or another worksheet such as our Accounts report. Click your cursor to Table_array, and then open the Accounts report. Select the whole area with your mouse from A2 onwards (don’t include the headlines!). In my example the area is from A2 to C4. When you have selected the area and the cursor is back in Table_array, press F4 which will cycle through absolute and relative references. If you don’t do this, you won’t be able to copy the function to the rest of the cells properly.
• Col_index_num – This is the number of the column on your lookup table that has the information you need. In our example, we want column 2 from the Accounts worksheet which has the name of the “Industry”. Just type number “2” in the field. 
• Range-lookup – this field defines how close a match should exist between your Lookup_value (A2) and the value in the leftmost column on our lookup table. In our case, we want an exact match so we’ll type “FALSE”.
• At the bottom of the window Formula result should now show the correct industry for the first account.
• Finish the formula by clicking OK
• Copy the function to rest of the column E:
• Click the cell containing the VLOOKUP arguments (in our example, this would be E2)
• Grab the cell handle that displays in the lower right corner.
• Left-click and drag down the cell handle to cover your column range.
• Copy the function to column F to get the Account type:
• Click the cell containing the VLOOKUP arguments. In our example, this would be E2.
• Copy this cell with CTRL+C or right click mouse and select copy
• Select the first empty cell in column F -> F2 and paste the function with CTRL+V or right click mouse and select paste
• You will notice that the function gives error so we need to correct the formula. Click your mouse into the formula bar and change the lookup_value back to A2. Also change the Col_index_num 2 (which we gave to point to Industry) to number 3 to point to Account type.
• Once the formula has been corrected drag and copy the cell F2 to the rest of the column F as instructed for column E.
• And now we're finished having the information from the Accounts excel in our Contacts Excel.