0

I was hoping someone might be able to figure out some formulas (or VBA scripts) for me to help me calculate qualitative analyses. Specifically, I have two columns that look like this:

Participant IDs and Responses over Time

enter image description here

Just to be clear, the Participant ID column is NOT important in this context, I have only included it to contextualize that each T1 and T2 cell is tied to a participant we are comparing across. Those columns represent participants' responses (codes) to questions over two different times: immediately (T1) and after a delay (T2). So I need to compare the T1 to T2 columns to assess what participants added (committed) or deleted (omitted) from T1 to T2. What I am trying to have some formulas print out for me is something that looks like this:

Column 1: Comissions Column (Checking what was added from T1 to T2 column)

  • 67
  • 39

  • 39, 60
  • 47, 54

Column 2: Number of Comissions (Counting number of codes from Comissions Column)

  • 1
  • 1

  • 2
  • 2

Column 3: Omissions Column (Checking what was deleted from T1 to T2 column)

  • 47, 52
  • 69

  • 40, 48
  • 42, 64

Column 4: Number of Omissions (Counting number of codes from Omissions Column)

  • 2
  • 1

  • 2
  • 2

Here are some examples of formulas we have tried and that (somewhat) work:

{=TEXTJOIN(",",TRUE,IF(ISNUMBER(SEARCH("," &TRIM(MID(SUBSTITUTE(N3,",",REPT(" ",999)),(ROW(INDIRECT("1:" & LEN(N3)-LEN(SUBSTITUTE(N3,",",""))+1))-1)*999+1,999))&",",","&B3&",")),"",TRIM(MID(SUBSTITUTE(N3,",",REPT(" ",999)),(ROW(INDIRECT("1:" & LEN(N3)-LEN(SUBSTITUTE(N3,",",""))+1))-1)*999+1,999))))}

The code above counts some codes properly, but takes out the space between, for instance: "39, 60" and makes it "39,60" which is an issue. Additionally, it does not count some comissions properly, counting some numbers as commissions even if they were mentioned at T1 (which makes it a non-comission).

{=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(","&TRIM(FILTERXML("<z><y>" & SUBSTITUTE(C3,",","</y><y>")&"</y></z>","//y"))&",",","&SUBSTITUTE(B3," ","")&",")),TRIM(FILTERXML("<z><y>" & SUBSTITUTE(C3,",","</y><y>")&"</y></z>","//y")),""))}

The code above does EXACTLY what we wanted for one column and that was tracking Consistency across T1 and T2, that is codes that are reported at both times. We still have not figured out how to reverse engineer this code to work for Comissions and Omissions though.

=LEN(TRIM(H3))-LEN(SUBSTITUTE(TRIM(H3),",",""))+1

The code above counts the codes, but for blank cells it always adds 1 which is problematic because it should really count those cells as 0 or blank preferably.

Hopefully that makes sense, and thank you all for your help in advance!

  • 1
    This would be a lot easier if you had two tables and data in separate cells. Would that be an option? – cybernetic.nomad Feb 11 at 20:05
  • @cybernetic.nomad Hi there, unfortunately our data cannot be formatted in that way. Ideally we are seeking for some formulas using TEXTJOIN, ISNUMBER, FILTERXML, SUBSTITUTE, and TRIM to potentially accomplish what we are trying to do within one sheet. Our efforts have not been completely successful, but we have gotten close using those functions, which is why we have come here for some extra help! – ACPL Researcher Feb 11 at 20:55
  • 1
    Please edit your post and show what formulas you've tried. Also, is the data exactly like this, or are there other cases (like column T2 has only one number). Handling the edge cases is really where using formulas can get complicated. – gns100 Feb 11 at 21:01
  • @gns100 Hi there, we just added all the formulas we have been trying to work with/reverse engineer with some explanations for what is right and wrong with each code at the moment. Hopefully that helps! And yes, both column T1 and T2 can end up having one number in them at times, but neither column is ever blank or 0. – ACPL Researcher Feb 11 at 21:10
  • try changing =LEN(TRIM(H3))-LEN(SUBSTITUTE(TRIM(H3),",",""))+1 to =LEN(TRIM(H3))-LEN(SUBSTITUTE(TRIM(H3),",",""))+NOT(ISBLANK(H3)) – cybernetic.nomad Feb 11 at 22:32
1

We figured out exactly all the formulas we needed for Comissions (Additions), Omissions (Deletions), and Consistency calculations! We will be sharing them as an answer to this post for anyone that needs them in the future.

IMPORTANT NOTE: The codes below are SPECIFICALLY working for comma-separated values (CSV) coding schemes, we cannot guarantee they work for other cases.

To calculate Comissions (Additions), you will need to insert a VBA script/module. To do this, press Alt + F11 in Excel which opens the VBA window then click Insert -> Module and paste the following code:

(Source: https://www.ozgrid.com/VBA/array-differences.htm)

Public Function Comissions(Cell1 As Range, Cell2 As Range) As String
Dim Array1, Array2, lLoop As Long
Dim strDiff As String, strDiffs As String
Dim lCheck As Long


Array1 = Split(Replace(Cell1, " ", ""), ",")
Array2 = Split(Replace(Cell2, " ", ""), ",")
On Error Resume Next
With WorksheetFunction
    For lLoop = 0 To UBound(Array2)
        strDiff = vbNullString
        strDiff = .Index(Array1, 1, .Match(Array2(lLoop), Array1, 0))
        If strDiff = vbNullString Then
            lCheck = 0
            lCheck = .Match(Array2(lLoop), Array1, 0)

            If lCheck = 0 Then
                strDiffs = strDiffs & ", " & Array2(lLoop)
            End If
        End If

    Next lLoop
End With

Comissions = Trim(Right(strDiffs, Len(strDiffs) - 1))
End Function

The usage of this function would be something like: "Comissions(B3,C3)" which will compare those cells and print the Comissions (Additions) in a third cell where the formula is.

For Omissions (Deletions), you will need to insert another VBA script/module with the following code:

Public Function Omissions(Cell1 As Range, Cell2 As Range) As String
Dim Array1, Array2, lLoop As Long
Dim strDiff As String, strDiffs As String
Dim lCheck As Long


Array1 = Split(Replace(Cell1, " ", ""), ",")
Array2 = Split(Replace(Cell2, " ", ""), ",")
On Error Resume Next
With WorksheetFunction
    For lLoop = 0 To UBound(Array1)
        strDiff = vbNullString
        strDiff = .Index(Array2, 1, .Match(Array1(lLoop), Array2, 0))
        If strDiff = vbNullString Then
            lCheck = 0
            lCheck = .Match(Array1(lLoop), Array2, 0)

            If lCheck = 0 Then
                strDiffs = strDiffs & ", " & Array1(lLoop)
            End If
        End If

    Next lLoop
End With

Omissions = Trim(Right(strDiffs, Len(strDiffs) - 1))
End Function

Similarly to the Comissions formula, writing "Omissions(B3,C3)" will compare those cells and find Omissions (Deletions) in a third cell where the formula is written.

To find Consistency, which is codes/numbers/values that are the same in each cell, use the following code as an array function (hit Ctrl + Shift + Enter after pasting this code to make it an array function, thank you to @Scott Craner for the code):

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(","&TRIM(FILTERXML("<z><y>" & SUBSTITUTE(C3,",","</y><y>")&"</y></z>","//y"))&",",","&SUBSTITUTE(B3," ","")&",")),TRIM(FILTERXML("<z><y>" & SUBSTITUTE(C3,",","</y><y>")&"</y></z>","//y")),""))

This code will print any consistent (same) values that exist in two cells and print them in the third cell where this formula is written.

To then count all these codes for calculating Proportion/Percentages Committed, Omitted, and Consistent, use this code (thank you to @cybernetic.nomad for the code):

=LEN(TRIM(D3))-LEN(SUBSTITUTE(TRIM(D3),",",""))+NOT(--(D3=""))

You would use this code to count up the codes in the Comission/Omission/Consistency columns and then divide them by the TOTAL number of values that occurred at/in, for instance, the T1 column in our case. So for example, proportion/percentage committed from T1 -> T2 for Participant 1 would be 0.33 (1/3), proportion/percentage omitted would be 0.66 (2/3), proportion/percentage consistent would be 0.33 (1/3).

Hopefully this helps any other qualitative researchers out there who work with Excel!

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.