none
Macro to Clean Up Data RRS feed

  • Question

  • Hi, I need some help to create a macro to clean up data.  I want to show that, for each Entity, Criterion 1, 2 or both that say "Yes"  are denoted as Yes. If neither, I want a "No" in the output.  I need the macro to loop until all entities and currencies are exhausted (as the file will change periodically).  

    Please note that the x's in the below table are blank, i just put x's to paste clearly for this example.

    Entity Name Criteria Currency1 Currency2 Currency3
    Entity 1 Criterion 1 Yes x x
    Entity 1 Criterion 2 x x Yes
    Entity 2 Criterion 1 Yes Yes x
    Entity 2 Criterion 2 Yes x Yes
    Entity 3 Criterion 1 x x Yes
    Entity 3 Criterion 2 x Yes Yes

    if Criterion 1, 2 or both are Yes: YES

    If both are blank: NO

    The Output would look like this:

    Entity Name Currency1 Currency2 Currency3
    Entity 1
    Yes No Yes
    Entity 2 Yes Yes Yes
    Entity 3 No Yes Yes

    Many thanks in advance!

    Melissa

    Monday, November 19, 2018 8:05 PM

All replies

  • paste your matrix in range("a1") of a sheet and use this code:

    Sub cleandataa()

    number_of_lines = Range("A1").End(xlDown).Row
    For j = 3 To 5
    x = 1
    For i = 1 To number_of_lines Step 2
    criterion1 = Cells(i, j)
    criterion2 = Cells(i + 1, j)
    entity = Cells(i, 1)
    Cells(x, 7) = entity

    If criterion1 = "Yes" Or criterion2 = "Yes" Then
        Cells(x, j + 5) = "Yes"
        
    Else
        Cells(x, j + 5) = "no"
    End If
    x = x + 1
    Next i
    Next j

    End Sub
    Thursday, November 22, 2018 3:54 PM