Import a dataset through multiples IFs and Types

Hello Excel fellows,



Quick edit: since "DealDate" and "Date" can be confusing, here is an explanation: DealDate (41000 format) is supposed to be the real date (so column A in Sheet2), and the "Date" (in year) column in Sheet1 corresponds to each year in 3rd in Sheet2. Hope that helps!


I am pretty new to VBA, and I'd like to improve my skills in VBA.
First, thank you in advance for your help! The purpose of my demand is really to improve my skills for my job, so the following demand is just a representation of my real task. The goal is to reproduce it with more difficulty, but it will help me a lot if this exemple gets an answer. So again, thanks in advance!


Goal: the goal of this code would be to input a certain value in the right cell, after the code identifies if the date from the dataset to import corresponds to an existing date in the target worksheets. After it recognizes the date, it has to detect the type of product, and the DealDate of the product. Once it has done it, paste the value in the right cell (row is determined by the date, and column is determined by both DealDate and ProductType). I have included the same explanation in the Excel File, but if you have understood you can avoid reading it


Exemple of the goal: In Sheet1, we have the value in red "0.7". The code should loop to find the DealDate "41772" in Sheet2 (in rows), and detect it's the "Type A" + "2021" column. Then the code should check the corresponding DealDate (2021) and ProductType (Type A), and then copy the corresponding value (0.7) and paste it into the cell it has defined by identifying the right row (where 41772 is) and the right column (Type A + 2021).


I've tried to put the architecture on paper to have a clear idea of how it would work, and my guess is there is only a small part of code which is difficult, the rest will be copy and paste through different arguments (detect Type B instead of A ...). Am I right ?


I hope I was clear in my explanations, though, if you have any question, feel free to ask!

Best,
Casperinoo

I *think* this does what you want to do:

Public Sub PopulateData()

Dim oneSheet As Worksheet
Dim twoSheet As Worksheet
Dim lastRow As Long
Dim thisRow As Long
Dim thisCol As Long
Dim foundRow

Application.ScreenUpdating = False
Set oneSheet = Worksheets("Sheet1")
Set twoSheet = Worksheets("Sheet2")
lastRow = oneSheet.Cells(oneSheet.Rows.Count, "A").End(xlUp).Row
For thisRow = 2 To lastRow
    foundRow = Application.Match(oneSheet.Cells(thisRow, "A").Value, twoSheet.Range("A:A"), 0)
    If Not IsError(foundRow) Then
        thisCol = oneSheet.Cells(thisRow, "B").Value - 2012
        If oneSheet.Cells(thisRow, "C").Value = "B" Then thisCol = thisCol + 8
        twoSheet.Cells(foundRow, thisCol).Value = oneSheet.Cells(thisRow, "D").Value
    End If
Next thisRow
Application.ScreenUpdating = True

End Sub

WBD

Hello,

First of all, thanks a lot for the help and for replying this fast!

So I took a little time to understand the code and to adapt it to my task.
So basically what I did is replicate the code to fit my original file. Thus, I only changed product types by the real categories I had, and multipled If statements with ElseIf all along. The result of the part of the code I changed is the following:

quick edit: this part of code is from the original file. I didn't change the code of the example, so you already know the architecture of it. I've paste the changed code so you can have a clear idea on what I've done

HTML Code: 

Indeed there are a lot of categories and columns, right?
In the original file, I changed the format of the target array to properly fit to my task, the source database doesn't change much. So what you should see in terms of change of code is only multiple If statements for each category, ElseIf added for each If statement.

The problem is, what if multiple values correspond to our criteria? Right now, our code will input only the first found corresponding value. The thing is, in my area of work, there can sometimes be outliers; meaning that the value inputed in the final array can be "wrong". Thus it is acceptable to take the mean of all the values found by the algorithm, or to simply "store" multiple values in one cell.

I imagine that there must be 2 options then :
-1) Either we design an array through VBA that stores the different values in buffer memory, and then take the mean out of them;
-2) Or we could tell the algorithm that for each of those values found, we shall take the mean of it (like a waterfall architecture I guess).

I don't really know what could be the easiest way to be honest. Is there any way we could say the algorithm to put one value after the other in the same cell? In any case, what could be code solutions for this? I'll knowledge myself on dynamic arrays so I can understand your answer properly and bounce on it if needed.
Meanwhile, you will find on the example file an update of the explanations (same as before, if you read all of this message, you can skip it ! ).


Thanks again in advance and for the help done, have a great day!

Your code changes are not very efficient. I'd need to see a better example of what you're working with to help you amend what you have.

WBD

Hello guys,

So sorry for not being clear enough.

Okay here is the (better) explanation: The algorithm that WBD created takes the first value corresponding to the matching criteria (DealDate for row, and Date/ProductType for columns). I currently work in the trading commodities area, and there are multiple products I manipulate (for example, wheat, hops ... Those are called "Type A", "Type B" and so on on my document). The final values to inject correspond to prices, and the general goal of it is to track the previous market evolution with a curve of price. The thing is, sometimes we traded multiple times the same product for the same DealDate (date of deal), and sometimes there are outliers.

The final goal is to eliminate outliers, and to integrate the others values that weren't taken by the algorithm (the second one, the third one ...). The change in the code would be to take into account not only the first value found but all of them, and make the mean out of them, and inject this mean in the corresponding cell.

As an exemple, in the Excel file attached, in Sheet1 we have the same DealDate (41900) for 3 different trades of "Type A" product with the same maturity (2021). All of these values correspond to 1 cell in Sheet2. The result would be to take the mean of these 3 values and inject it in the corresponding cell.

As for the optimization, how could I condensate all the ElseIf I've added into a more compact block of code that would be optimized?

Is it clearer ? I'm not quite good at explanations I realise

OK. Try this macro:

Public Sub PopulateData()

Dim oneSheet As Worksheet
Dim twoSheet As Worksheet
Dim lastRow As Long
Dim thisRow As Long
Dim firstRow As Long
Dim thisCol As Long
Dim foundRow
Dim foundCol

Application.ScreenUpdating = False
Set oneSheet = Worksheets("Sheet1")
Set twoSheet = Worksheets("Sheet2")
firstRow = 0
lastRow = oneSheet.Cells(oneSheet.Rows.Count, "A").End(xlUp).Row
For thisRow = 2 To lastRow + 1
    If oneSheet.Cells(thisRow, "A").Value <> oneSheet.Cells(thisRow - 1, "A").Value _
    Or oneSheet.Cells(thisRow, "B").Value <> oneSheet.Cells(thisRow - 1, "B").Value _
    Or oneSheet.Cells(thisRow, "C").Value <> oneSheet.Cells(thisRow - 1, "C").Value Then
        If firstRow <> 0 Then
            foundRow = Application.Match(oneSheet.Cells(firstRow, "A").Value, twoSheet.Range("A:A"), 0)
            foundCol = Application.Match(oneSheet.Cells(firstRow, "C").Value, twoSheet.Range("1:1"), 0)
            If Not (IsError(foundRow) Or IsError(foundCol)) Then
                thisCol = oneSheet.Cells(thisRow, "B").Value - 2012
                If oneSheet.Cells(thisRow, "C").Value = "B" Then thisCol = thisCol + 8
                twoSheet.Cells(foundRow, foundCol + oneSheet.Cells(firstRow, "B").Value - 2014).Value = Application.WorksheetFunction.Average(oneSheet.Cells(firstRow, "D").Resize(thisRow - firstRow))
            End If
        End If
        firstRow = thisRow
    End If
Next thisRow
Application.ScreenUpdating = True

End Sub

WBD

Arrays and/or dictionary would also work but they wouldn't necessarily be much faster due to the nature of the input data and the required output.

WBD