CODEWITHSUNDEEP
excelvba
Vigmo10
Vigmo10

Reputation: 49

Matching two values for multiple columns. i cant figure how to approach it

i have 6 columns

Grade,Date, ID number, Grade Old, date old and ID number

I need the grade to look for value in grade old and also date old of the grade needs to be within 2 weeks of the date then the ID number is same.

for example:

if grade = A then it needs to look for A in Grade Old and also compare the dates, if the date old is 2 weeks of date then ID number stays the same. (There can be multiple Grades i.e. Grade old can have multiple entries as A, just need to pick the right one by comparing dates.)

I have no idea how do i achieve this. I tried a few codes but in vain.

Thank you for your time. I appreciate your help.

Upvotes: 0

Views: 53

Answers (2)

Sun
Sun

Reputation: 762

Give this code a try. This should do what you want, based on your comments above.

Note on data structure:

  • Grade is in column A, Row 1 are the headers.
  • My sheet is called "Grades"

Change the code according to your sheet structure.

Sub grades()

Dim wb As Workbook
Dim ws As Worksheet

Dim firstrow, lastrow As Integer

Dim firstAddress As String
Dim tmpFind As Range
Dim grade As String
Dim dateLeft As Double
Dim dateOld As Double

Dim found As Boolean

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Grades")    'change "Grades" to the name of your sheet


    With ws

        firstrow = 2

        lastrow = .Range("A1000000").End(xlUp).Row

        For r = firstrow To lastrow

            found = False

            grade = .Range("A" & r).Value
            dateLeft = .Range("B" & r).Value

            Set tmpFind = .Range("D" & firstrow & ":D" & lastrow).Find(grade, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=False)

            If Not tmpFind Is Nothing Then

                firstAddress = tmpFind.Address

                Do

                    dateOld = .Range("E" & tmpFind.Row).Value

                    'check if dateOld is within + - two weeks. if + 2 weeks not required, remove 'And dateOld <= dateLeft + 14' from below
                    If dateOld >= dateLeft - 14 And dateOld <= dateLeft + 14 Then

                        .Range("F" & tmpFind.Row).Value = .Range("C" & r).Value     'writes ID-left to ID-right
                        found = True

                    End If

                    Set tmpFind = .Range("D" & firstrow & ":D" & lastrow).FindNext(tmpFind)

                Loop While Not tmpFind Is Nothing And tmpFind.Address <> firstAddress And found = False

            End If

        Next r

    End With

End Sub

Upvotes: 1

CodeKavi
CodeKavi

Reputation: 45

Are you looking for a Excel Macro to do this? You may use the below formulae =IF(ISERROR(MATCH(,A:A, 0)), "No Match", "Match")

=IF(G2="Match",IF(B2-E2<15,C2,F2),)

this is how it looks

If you are looking for an excel macro then you may use try using the same sort of validation in your logic. Hope this helps.. thanks.

-Codekavi.

Upvotes: 0

Related Questions