VLOOKUP with multiple parameters?-CodePudding

Novice user here, so please bear with me. I am pretty decent with VLOOKUP, but that is about where my expertise ends.

Sheet1 is my active sheet which I am working on.

Sheet2 is my source data.

This sheet contains thousands of lines of client data over the span of the last 6 months.
Each client will currently have anywhere between 1 to 6 unique lines of data within the list (and growing), with the main difference for each client's line being a "date" (1 date per client each month), a "type" (i.e. Trail, Arrears) and a "$ amount".

There are a lot more column than I've mentioned above, so in my active sheet I have already used VLOOKUP to transfer the 3 main columns of data I am using, which are;

"Loan No", "Client Name", "Lender"

Duplicate rows, based on "Loan No" have also been removed. So, now each client just as 1 row.

What I am now trying to do, within each clients unique row, using the original source data, is populate the "$ amount" into a corresponding column, with a month heading, also based on the "Type" value

So basically

Within "Sheet1", look up Loan Number (cell A1) within "Sheet2"
Within "Sheet2", find the row that has both the matching "Loan Number" and matching "Date" (keeping in mind "Sheet1" only has the Month, whereas "Sheet2" has a full DD/MM/YYY)
If this row has a "Type" of "Trail" or "Arrears" (there are other Type fields I want to omit), then
Return the "Amount" to the cell the formula is entered into.

I know there may be multiple ways to do this, but I don't want to mess around with Pivots or anything like that, I also don't want to change the format of the source data. Basic formula is preferred as I am integrating some VBA functions using written formulas etc...

Any help would be greatly appreciated!

Simon

CodePudding user response：

You'll need to adapt this across your worksheets but a SUMIFS should do it for you ...

This is the formula in cell K2 ...

=SUMIFS($F$2:$F$9,$A$2:$A$9,$H2,$D$2:$D$9,">=" & K$1,$D$2:$D$9,"<=" & EOMONTH(K$1,0))

Essentially, it's summing off your data with multiple criteria. You can add additional criteria as need be. The important thing to note here is the dates ...

K1 = 1/07/2021, formatted as ... mmmm ... it must be a date.

L1 = =EDATE(K1,1), formatted as ... mmmm

Make sure you fill across from K1 to N1.

To filter on the dates (i.e. using >= and <=) all fields need to be valid dates.

The dates are important, if they're not dates, then comparing dates (i.e. 1/01/2021) to the words (i.e. January) will not work.

CodePudding user response：

Please, try the next code. It should be fast, using arrays and most of processing is done in memory. It returns in another sheet (shDest), I tested it to return in the next one:

Sub TransposeDataByLenderMonth()
  Dim sh As Worksheet, shDest As Worksheet, lastR As Long, minD As Date, maxD As Date, arrD
  Dim dict As Object, El, arr, arrFin, i As Long, k As Long, mtch, strD As String
  
  Set sh = ActiveSheet 'use here the sheet you need
  Set shDest = sh.Next 'use here the destination sheet (where the processing result to be returned)
                       'if next one is empty, the code can be used as it is
  lastR = sh.Range("A" & sh.rows.Count).End(xlUp).row
  minD = WorksheetFunction.min(sh.Range("D2:D" & lastR)) 'find first Date
  maxD = WorksheetFunction.Max(sh.Range("D2:D" & lastR)) 'find last date

  'create months arrays (one for months header and second to match the date):
  arrD = GetMonthsIntArraysl(minD, maxD)
  
  arr = sh.Range("A2:F" & lastR).Value  'place the whole range in an array, for faster iteration
  'Extract unique keys by LoanNo:
  Set dict = CreateObject("Scripting.Dictionary")
  For i = 1 To UBound(arr)
        dict(arr(i, 1)) = Empty
  Next i

  ReDim arrFin(1 To dict.Count   1, 1 To UBound(arrD(1))   4): k = 1
  'Place the header in the final array:
  arrFin(1, 1) = "LoanNo": arrFin(1, 2) = "Client Name": arrFin(1, 3) = "Lender"
  For i = 1 To UBound(arrD(0))
        arrFin(1, 4   i) = CStr(arrD(0)(i, 1))
  Next i
  k = 2  'reinitialize k to load values after the header
  For Each El In dict.Keys     'iterate between the unique elements:
    For i = 1 To UBound(arr)   'iterate between the main array rows:
        If arr(i, 1) = El Then 'when a match is found:
            arrFin(k, 1) = arr(i, 1): arrFin(k, 2) = arr(i, 2): arrFin(k, 3) = arr(i, 3)
            strD = CStr(Format(DateSerial(Year(arr(i, 4)), month(arr(i, 4)), 1), "dd/mm/yyyy"))
            mtch = Application.match(Replace(strD, ".", "/"), arrD(1), True)
            arrFin(k, 4   mtch) = arr(i, 6)   arrFin(k, 4   mtch) 'add the value of the appropriate month
        End If
    Next i
    k = k   1
  Next El
  'Format a little and drop the processed array content:
  With sh.Range("J1").Resize(1, UBound(arrFin, 2))
        .NumberFormat = "@"
        .BorderAround 1, xlMedium
  End With
  With sh.Range("J1").Resize(UBound(arrFin), UBound(arrFin, 2))
        .Value = arrFin
        .EntireColumn.AutoFit
        .BorderAround 1, xlMedium
        .Borders(xlInsideVertical).Weight = xlThin
  End With
  MsgBox "Job done..."
End Sub

'The following function returns an array of two arrays. 
'One for header and the other for matching the date columns (in the final array)
Private Function GetMonthsIntArraysl(startDate As Date, endDate As Date) As Variant
   Dim monthsNo As Long, rows As String, monthsInt, dd As Long, arrStr, arrD
    monthsNo = DateDiff("m", startDate, endDate, vbMonday)
    rows = month(startDate) & ":" & monthsNo   month(startDate)

    arrStr = Evaluate("Text(Date(" & Year(startDate) & ",row(" & rows & "),1),""mmmm YYYY"")")
    arrD = Evaluate("Text(Date(" & Year(startDate) & ",row(" & rows & "),1),""dd/mm/yyyy"")")
    GetMonthsIntArraysl = Array(arrStr, arrD)
End Function

The above code adds all values for dates belonging to the same months/Client, if the case...

I took your question like a challenge, but you must learn that, in order to receive a solution, you must make some research by your own and show us a piece of code. Please, take it as a favor and next time place a better question, complying to the community rules, from the above mentioned point of view.