CODEWITHSUNDEEP
vbaexcel
spaindc
spaindc

Reputation: 361

vba: Identify row using 2 criteria and return other data from row

I am trying to find code that looks at two criteria in spreadsheet1 and finds a row that corresponds in spreadsheet2 and returns a third piece of data in spreadsheet2 to spreadsheet1. I need to do this in vba because it loops, because I will be done it again and again, and because the data from spreadsheet2 imports from another database and will change over time. If possible it would be nice if the code also allowed for identifying a 3rd criteria on spreadsheet2.

Example is: Spreadsheeet 1

 Product ID  ActCode: A0003
 11111
 12345
 22222
 ...

Spreadheet 2

ProductID  ActivityCode   DateDue
 11111     A0001          7/15/15
 11111     P7530          7/30/15
 11111     A0003          8/1/15
 12345     A0003          12/15/15
 12345     A0007          1/1/15
 22222     A0001          2/1/15 
 ...

Where I want Spreadsheet1 to end up:

Spreadsheeet 1

 Product ID  ActCode: A0003
 11111       8/1/15
 12345       12/15/15
 22222         - 
 ...

I have tried a ton of things over the past few days. 1) vlookup/index/match combos that have never really worked, 2) filtering spreadsheet2 by productID and activitycode and then copying to spreadsheet1 the visible cells - this works but is very slow. I will be doing this for many activity codes, so I need something faster (I can post the code if you want to see it). I am currently trying a loop within a loop. Not sure if this is the best way but here is the code I have so far. It does copy some dates over, but not the right ones - its also a bit slow.

Sub test()

Application.ScreenUpdating = False
  Sheets("Spreadsheet1").Select

  Range("A2").Select ' Select A = the column with the product ID in it
  ' Set Do loop to stop when an empty cell is reached.
  Do Until IsEmpty(ActiveCell)

    Dim ConceptAct  As String
    ConceptAct = "A0003"

    Dim ProductID
    ProductID = ActiveCell.Value
    Dim ConcDue

        Sheets("Spreadsheet2").Select
        Range("A2").Select 'The column with the ProductID in it

        Do Until IsEmpty(ActiveCell)

        If ActiveCell.Value = ProductID And ActiveCell.Offset(0, 1).Value = ConceptAct Then

            ConcDue = ActiveCell.Offset(0, 2).Value

            Exit Do
        End If
           ActiveCell.Offset(1, 0).Select
        Loop

     Sheets("Spreadsheet1").Select

     ActiveCell.Offset(0, 1) = ConcDue

     ' Step down 1 row from present location.
     ActiveCell.Offset(1, 0).Select

  Loop

Application.ScreenUpdating = True

End Sub

Upvotes: 1

Views: 1194

Answers (2)

user4039065
user4039065

Reputation:

I'm unclear on why a two-column criteria formula using native worksheet functions is not apprpriate but a User Defined Function (aka UDF) would be one avenue to pursue from a VBA point of view.

Function udf_Get_Two(sCode As Range, rCodes As Range, _
                     sProd As Range, rProds As Range, _
                     rDates As Range)
    Dim vCode As Variant, rw As Long, m As Long

    'quick check to see if there is anything to find
    If CBool(Application.CountIfs(rCodes.Columns(1), sCode.Value, _
      rProds.Resize(rCodes.Rows.Count, 1), sProd.Value)) Then
        rw = 0
        For m = 1 To Application.CountIf(rCodes.Columns(1), sCode.Value)
            rw = rw + Application.Match(sCode.Value, rCodes.Columns(1).Resize(rCodes.Rows.Count - rw, 1).Offset(rw, 0), 0)
            If rProds(rw, 1) = sProd Then
                udf_Get_Two = rDates.Cells(rw, 1).Value
                Exit Function
            End If
        Next m
    End If

End Function

Use like any other worksheet formula. Example:

=udf_Get_Two(A2, Sheet2!$A$2:$A$7, $C$1, Sheet2!$B$2:$B$7, Sheet2!$C$2:$C$7)

    UDF for two column lookup

Note that the returned values are raw. The cells should be formatted as m/d/yy or as you prefer.

Upvotes: 0

BruceWayne
BruceWayne

Reputation: 23283

Why won't Index/Match work? I was able to get, I think, your solution with an Index/Match formula entered as an array. Here's a screenshot of everything:

enter image description here

Index/Match can use multiple criteria for looking things up, just connect these with &, both in the first and second parts of the Match(), and hit CTRL+SHIFT+ENTER to enter as an array. This formua will look at the product ID, then that ActCode, return the date.

Is this what you were looking for?

Upvotes: 1

Related Questions