TMITS

TailorMade Info Technology Solutions Ltd

Tel/Fax: +44 (0)1359 242146 ~ Email: Solutions@tailormadeit.co.uk


Excel Hints & Tips

Here are a few popular Excel formulas and processes I have used with Vision data.

Please note that all names and demographic details shown on this page and throughout this website are fictitious, any resemblance to real persons, living or deceased are purely coincidental.


Export as Tab Separated or DBF?

Vision exports data in multiple files, which depends on each entity you add to your search. There will always be a PATIENT file and then additional data files depending on your search.

You can join these data files using either tab separated or DBF...

DBF Export

Exporting as DBF creates very large files which takes a while to compile after the search has run, doesn't include free text and must include all data items to show in Excel. For example if your exported data includes BP's and Smoking and addresses; if there are some patients that only have a smoking and no BP, or No BP and only a smoking record etc... they will not show in the Excel spreadsheet when you merge the data.

However DBF files are easy to join up in Excel.

So if your exported files are going to be small amounts of data and you can guarantee all items will be present, then DBF is the way to go.

Importing Vision DBF data into Microsoft Excel requires two elements to of been performed per workstation; If you are on the Vision remote server (VES/WES) the following is automatically installed and you may skip these steps:

Tab Separated Export

This by far the quickest way to export data as it creates small txt files, so you can export very large amounts of data, its around 10 times quicker in exporting the data out of Vision and exports everything including free text.

If you are only interested in the data and don't want to disclose patient demographic data, this is perfect as you simply open the txt file in Excel.

If you want to join data, you will have to use the VLOOKUP formula explained below. The beauty of VLOOKUP is that it won't remove any patients that do not have data, it will simply show #N/A

I personally use Tab Separated in the majority of exports from Vision.

Use VLOOKUP to show data from one spreadsheet in another

This is probably the most popular formula I use to link up tables of data. Click here to view a video tutorial

99% of the exported Vision data will always show the PAT_ID in the first column. This is the link that joins all the data together.

The formula would look something like this:

=VLOOKUP(A2,patient!A:S,19,FALSE)

Notice that the majority of the formula will remain the same because the forename and surname columns are within A:S, which means I only need to change the column number in the formula.

Find Title

Find Forename

Find Surname

Table of data

How to add Y or N to the VLOOKUP result

Formula’s:
=IF(ISERROR(VLOOKUP(A2,Sheet2!A:F,2,FALSE)),"N","Y")

=IF(COUNTIF(Sheet2!A:F,A2),"N","Y")

Or if you just want a TRUE or FALSE reference you could use:

=COUNTIF(Owners!A:F,C4)>0

How to import DBF data into Excel and join it together

  1. After exporting the DBF files from your Vision search
    DBF Export
  2. Open Microsoft Excel 2010
  3. Data tab > From Other Sources > From Microsoft Query
    MS Query
  4. Select Visual FoxPro Database > click OK
    FP Driver
    Make sure "Use the Query Wizard..." is selected
  5. Click the Browse button and locate the Vision DBF files folder, (i.e. The folder you chose when you ran your Vision search) then click OK
    Path
  6. You should now see the DBF databases in this folder.
    DBF Files
  7. Expand the tables to see the columns of data you can choose to show in Excel
  8. Double-click on the column names from any of the tables, so that they show in the right window area or use the arrows located on the middle bar to add/remove these column names
    Add Columns
    Note: The Options... button allows you to sort the column names
  9. Click Next when you have all you need
  10. Click OK when the Query Wizard shows the following prompt screen
    Join Warning
    This message is basically saying it doesn't know what column of data to use, to join the tables together.
  11. Locate pat_id in both tables as shown below
    Locate pat_id
  12. Then drag one pat_id onto the other to create a join line between them
    Join line
  13. Now click the File menu and Return the data to Microsoft Excel

Join separate columns of data and show in one cell

You can either use the Concatenate formula or join cells using the & symbol.

If you want to include some text elements between your joins use " "

The formula would look like this:

=CONCATENATE(D2&" "&E2&" "&F2&" "&G2&" "&H2&" "&I2&" "&J2)

OR

=D2&" "&E2&" "&F2&" "&G2&" "&H2&" "&I2&" "&J2

Concatenate formula

Separate data held in one cell

If you have a string of text in one cell that you need to separate, for example and address or the whole patient name, you can separate each block of text into separate columns.

You just need to insert the correct amount of columns before you start. For example if you want to separate Title, Forename and Surname you will need 2 additional columns inserted to the right side of you initial column of text, while an address may need 5 additional columns and so on...

Use "Text to Columns" to divide up the data.

  1. Highlight the column you want to divide up
  2. Select Text to Columns located on the Data tab
  3. In the example below commas are used in the column of text to divide it up, so we will use the delimited option
    Text to Columns
  4. Click Next
  5. Make sure you select Comma as the delimited text character
    Comma Delimiter
  6. Click Finish as the next screen doesn't provide anything that is needed.
  7. Press OK to move the text from the original column into the new ones
    Replace Contents

Divided Address 

Get Age from Date of Birth

Formula:
=IF(MONTH(TODAY())>MONTH(A1),YEAR(TODAY())-YEAR(A1),IF(AND(MONTH(TODAY())=MONTH(A1),DAY(TODAY())>=DAY(A1)),YEAR(TODAY())-YEAR(A1),(YEAR(TODAY())-YEAR(A1))-1))

Age Formula

To work out the age when data was recorded, in the same formula above, just replace TODAY() with the event date cell reference.

Formula:
=IF(MONTH(B2)>MONTH(A2),YEAR(B2)-YEAR(A2),IF(AND(MONTH(B2)=MONTH(A2),DAY(B2)>=DAY(A2)),YEAR(B2)-YEAR(A2),(YEAR(B2)-YEAR(A2))-1))

Age at Event Date

Remove SPACES from cell

Formula:
=SUBSTITUTE(A2," ","")

Substitute

Using the formula above means creating a new column, whereas using REPLACE would remove spaces in the actual cells.

Excel 2003:
Highlight the column (or cell) > Edit menu > Replace > Find What: (just type a space) and leave Replace with: empty > Click Replace All > OK

Excel 2010:
Highlight the column (or cell) > Home tab > Find & Select > Replace > Find What: (just type a space) and leave Replace with: empty > Click Replace All > OK

Replace Space

How to extract specific text from a cell full of text

Formula:
=MID(A1,FIND("[",A1,1)+1,LEN(A1)-2)
=MID(A1,SEARCH("[",A1)+1,SEARCH("]",A1)-(SEARCH("[",A1)+1))
=MID(A1,FIND("[",A1)+1,FIND("]",A1)-FIND("[",A1)-1)

Extract Text

If you just want to remove all the text above and just leave the contents  within the brackets (inverse delete) you could use the REPLACE feature as follows:

This will now look like this:

Inverse delete

Remove zero values from cells

There are a few methods to achieve this, but the simplest way to hide all zero values in a sheet is to switch them off for the whole worksheet. Just remember, you must set it for each sheet in the workbook, as follows:

  1. Click the File menu and then choose Options (under Help).
    In Excel 2007, click the Office button and then click Excel Options.
    In Excel 2003, choose Options from the Tools menu.
  2. Choose Advanced in the left pane. In Excel 2003, click the View tab.
  3. In the Display Options For This Worksheet section, uncheck the Show A Zero In Cells That Have Zero Value.
  4. Click OK.

Numeric format

The sheet setting will hide all zero values in the sheet. If you want to hide specific zero values, but not all, you can use a numeric format instead:

  1. Select the cells that contain the zero values that you want to hide.
  2. Click the Home tab and click the Number group's dialog launcher (the small arrow in the bottom-right corner.
    In Excel 2003, choose Cells from the Format menu.
  3. Click the Number tab (if necessary).
  4. Choose Custom from the Category list.
  5. Enter 0;-0;;@ in the Type field.
  6. Click OK

If you edit in the cell hidden 0s will still be visible in the Formula bar or in the cell. To undo this format, simply choose an alternate numeric format for the cells.

Conditional format

This format is easy to forget and may cause problems later on, so choose this method as a last resort.

The numeric format shown above will hide literal zeroes and most returned by a formula. When you run into an exception, you can use a conditional format as follows:

  1. Select the cells that contain the 0s you want to hide.
  2. Click the Home tab and then click the Conditional Formatting option in the Styles group. Choose New Rule.
    In Excel 2003, choose Conditional Formatting from the Format menu, and skip to step 4.
  3. In the top pane, select the Format Only Cells That Contain option.
  4. From the second dropdown, choose Equal To.
  5. Enter 0 in the third control.
  6. Click Format.
  7. From the Color dropdown, choose White (or the color that matches the sheet's background).
  8. Click OK twice.

Convert Numbers into Time

Convert number to time

Formula: =TEXT(A2,"00\:00")+0

You will also need to Format Cells using either Time or a custom Category like: hh:mm

Convert Text Number into Actual Number

When importing data from an external database into Excel you may find that some numbers are actually defined as text and therefore your formulas won't work.

To resolve this;

  1. Copy a 1 (actual number) from a cell
  2. Highlight your column of numbers
  3. Use Paste Special, then select Multiply.
  4. This will multiply all the values in the cells by 1

Thus converting them to actual numbers Excel can recognize.

Date Formula's

Below are a set of formula's that gather information from the today date value in cell B2.

These are useful when using Pivot tables to count numbers in a given month or year, etc...

Excel Date Formula's

With regards to the WEEKDAY formula (serial_number, return_type)

Serial_num is any valid date

Return_type is a number (between 1 and 3) that determines the type of return value.

With regards to the WEEKNUM formula (serial_num, return_type)

Serial_num is any valid date

The default for Return_type is 1, or it can be omitted and the week begins on a Sunday.

If Return_type is 2, the week begins on a Monday.

Keep 1st Word in Cell

This formula will keep the left most word before the first space in a cell:

Left Formula

To keep just the first few characters from the left use =LEFT(A2, 3)

Left formula

Back to Top