Excel VBA compare data to column

Question

I am new to excel VBA code. If someone could help me with it, I need it for an assignment.

I have this data in Excel:

excel data

What I want to do is: if the guardian is 'mother' than in the column of guardian edu it should display mother's education. If the guardian is 'father' than it should display the father's education as shown in the images below.

example

I need to do this for a large data set using VBA code.

Please post the code that you are having difficulties with and we will help you fix it. — YowE3K, Dec 03 '16 at 16:19

score 2 · Answer 1 · answered Dec 03 '16 at 16:26

2

Why vba? A Simple formula in the place of guardian education will do.

Use this formula assuming data is in second row.

=IF(C2="mother",A2,IF(C2="father",B2,"");

Then drag and drop to the bottom

answered Dec 03 '16 at 16:26

Rehban Khatri

924
1
7
19

score 0 · Answer 2 · answered Dec 03 '16 at 17:48

you could try this (commented) code:

Option Explicit

Sub main()        
    With Worksheets("Edu") '<--| change "Edu" to your actual worksheet with data name
        With .Range("A1", .Cells(.Rows.count, "C").End(xlUp)) '<--| reference its columns A to C range from row 1 down to column A last not empty cell one
            .Resize(, 1).Offset(, 4).Value = Application.Transpose(GetOrdinals(.Rows.count)) '<--| write initial order index in column "E" (it'll be deleted by the end of the macro)
            .Resize(, .Columns.count + 2).Sort key1:=.Columns(3), Header:=xlYes '<--| order columns A to E by column C ("guardian")
            SetGuardian .Columns(3), "mother", 1, -2 '<--| fill column "D" (1 offset column from column "C) with column "A" (2 column offset from column "C") values
            SetGuardian .Columns(3), "father", 1, -1 '<--| fill column "D" (1 offset column from column "C) with column "B" (2 column offset from column "C") values
            .Parent.AutoFilterMode = False '<--| remove autofilter mode and show all rows back
            .Resize(, .Columns.count + 2).Sort key1:=.Columns(5), Header:=xlYes '<--| sort columns A to E by initial order index column to get them back to their original position
            .Resize(, 1).Offset(, 4).ClearContents '<--| clear initial order index column
        End With
    End With
End Sub

Sub SetGuardian(data As Range, guardian As String, targetColOffset As Long, sourceColOffset As Long)
    With data
        .AutoFilter field:=1, Criteria1:=guardian '<--| filter referenced cells with passed 'guardian'
        If Application.WorksheetFunction.Subtotal(103, .Cells) > 1 Then
            With .Resize(.Rows.count - 1).Offset(1).SpecialCells(xlCellTypeVisible)
                .Offset(, targetColOffset).Value = .Offset(, sourceColOffset).Value
            End With
        End If
    End With
End Sub

Function GetOrdinals(max As Long) As Variant
    Dim i As Long
    ReDim arr(1 To max) As Long

    For i = 1 To max
        arr(i) = i
    Next i
    GetOrdinals = arr
End Function

Excel VBA compare data to column

2 Answers2