#3: Assigning site names based on assigned site numbers

Feb 8, 2021

Clinical Research Data 101

Tools/Languages: Excel VBA Macro

This tutorial shows you how to automatically delete the unused column, loop through all rows to assign site names based on assigned site numbers.

It's super helpful if you have more than 100 participants from more than 1 site.
Or when you need to generate daily participant reporting.
You can use this template to assign column values based on column values (numbers) from another column.

First of all, here's the link to learn how to turn on the "Developer Mode" in your Excel.

What happened?

In clinical research, we often need to track participants' status. Usually we would have a spreadsheet of participants IDs with previously assigned site numbers. For example, UCLA is site 01 and UC Santa Cruz is site 02, etc. In the electronic data capture system (REDCap), the site numbers should be used as the best indicator for sites, since it's easy to mispell the site names and there're multiple ways to write the same site names. This tutorial is to simulate the situation when you export the report data to Excel spreadsheet as raw data.

What should I do now?

• Open the Excel spreadsheet and turn on the "Developer" mode as described at the beginning.
• Click "Visual Basic" on the top ribbon (first one on the left).
• Insert a new "Module" and copy/paste the codes below.

  
  Sub dag()
  
  'Run the missing site(DAG) report first
  
  Dim site As Double
  
  Dim group_name As String
  
  Dim wb As Workbook
  
  Dim today As String
  
  Dim counter As Integer
  
  'Display today's date first in a message box
  
  today = DateValue(Now)
  
  todaydate = Replace(today, "/", "_")
  
  MsgBox todaydate
  
  'Select the correct sheets and copy to a new tab
  
  Sheets("Sheet1").Select
  
  Sheets("Sheet1").Copy After:=Sheets(1)
  
  'Assign the last row variable for later loops
  
  lastrow = Cells(Rows.Count, 1).End(xlUp).Row
  
  'Delete unused columns'
  
  Columns("C:D").Select
  
  Selection.Delete Shift:=xlToLeft
  
  'Autofit the columns
  
  Columns("A:G").EntireColumn.AutoFit
  
  'Delete records that have been assigned DAG
  
  Range("C2:C" & lastrow).SpecialCells(xlCellTypeConstants).EntireRow.Delete
  
  'Delete records that have missing site number (empty cells)
  
  Range("D2:D" & lastrow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  
  'Loop through rows to assign DAG based on site number
  
  For myrow = 2 To lastrow
  
  intloc = Range("D" & myrow).Value
  
  If intloc = 2 Then
  
  	Range("C" & myrow).Value = "UC Santa Cruz"
  
  ElseIf intloc = 1 Then
  
  	Range("C" & myrow).Value = "Stanford"
  
  ElseIf intloc = 3 Then
  
  	Range("C" & myrow).Value = "UC Berkeley"
  
  ElseIf intloc = 4 Then
  
  	Range("C" & myrow).Value = "UCLA"
  
  ElseIf intloc = 5 Then
  
  	Range("C" & myrow).Value = "UC Irvine"
  
  ElseIf intloc = 6 Then
  
  	Range("C" & myrow).Value = "UC Merced"
  
  ElseIf intloc = 7 Then
  
  	Range("C" & myrow).Value = "UC Riverside"
  
  ElseIf intloc = 8 Then
  
  	Range("C" & myrow).Value = "UC Davis"
  
  ElseIf intloc = 9 Then
  
  	Range("C" & myrow).Value = "UC San Diego"
  
  ElseIf intloc = 10 Then
  
  	Range("C" & myrow).Value = "UC San Francisco"
  
  Else		
  	
  	End If
  
  Next myrow
  
  'Change the sheet name to today's date
  
  Sheets("Sheet1 (2)").Name = "dag_" & todaydate
  
  'Save the sheet with today's date as the file name (for macbook)
  
  ActiveSheet.SaveAs "/Users/aq/Desktop/StudyName_DAG_" & todaydate & "_.csv"
  
  
  End Sub
  							
  						

• Go back to the spreadsheet and click the "Macros" icon on the top ribbon. Then assign the macro to "dag" and click "OK".

Please leave me a message if it doesn't work for you.