The dbf export time period in the video above, finishes at 2:34 just in case you want to manually drag the video forward

Formula used in demonstration

• =VLOOKUP(A2,patient!A:N,4,FALSE)

This has got to be one of the most popular formulas in Excel, I use it all the time as it enables me to combine data from multiple spreadsheets.

As you know Vision extracts the data in multiple TAB separated txt files, so use VLOOKUP to combine the data.

I normally move spreadsheets into the same workbook as its easier to manage:

1. Right mouse click on the sheet tab at the bottom of one spreadsheet and select Move or Copy…

2. Select an open book from the top drop down list

3. Press OK

4. Repeat this if you have multiple open workbooks by moving all of them into the same workbook.

A nice feature about all the Vision extracted searches is that the PAT_ID is always in the first column, which means that the first part of the following formula will always be A2 as this is the first patient ID in the spreadsheet that you will use as the lookup reference.

The formula is written like this and as mentioned presumes that the PAT_ID is in the first column and you have inserted a new column and are entering the formula in row 2 of the new column

=VLOOKUP(A2,Sheet1!A:D,2,FALSE)

Each coma in the formula is a different question

1. “A2” is the LOOKUP value (i.e. the PAT_ID is the unique value in all the spreadsheets that reference a patient.)

2. Sheet1!A:D is the whole table (spreadsheet) I’m looking in to find the data I want displayed.

   •  I use columns as in A:D but this could also be  an exact range of cells like A2:D5432

   • Note always make sure the table includes the PAT_ID column and the data you want to be displayed, as it will count from the PAT_ID across to the relevant column.

3. What column number is the data in you want displayed from the table you just selected above, in the example above it will show me everything in the 2nd column that matches the PAT_ID in both spreadsheets

4. This is normally optional, but in our case this should always be FALSE.

   If you use FALSE, VLOOKUP returns an exact match. If Excel cannot find an exact match, it displays the #N/A error message.

   If you set the value to TRUE or leave it blank, VLOOKUP returns the closest match to your search term. If you set the value to TRUE, you must sort the values in the first column of your table array in ascending order.

   Excel Help calls this part of the function the range_lookup value.

That's it.

Additionally, if no matching data is found you may see #N/A. Just add the following to the formula to remove it.

• =IF(ISNA(VLOOKUP(A2,patient!A:D,2,FALSE)),"",(VLOOKUP(A2,patient!A:D ,2,FALSE)))

Also another handy tip is to be able to freeze columns and rows using the F4 keyboard function key when you drag cells either up or down. All you need to do is use a dollar symbol in front of either the row or column or both cell references, i.e.

• $A$2 would freeze both the column and row, so that when the cell was dragged either up or down it would remain the same.

• A$2 would just freeze the row, but not the column, i.e. if the cell was dragged across it would be B2, C2, D2, etc... but if dragged down it would remain as A2.

• $A2 would just freeze the column and not the row, i.e. when dragged across it would remain as A2, but if dragged down it would be A3, A4, A5, etc...