Help with something more advanced than VLOOKUP

mneghassi

New Member
Joined
Jul 17, 2018
Messages
18
I would like to create a worksheet that includes pulls data for each fund, but one fund (i.e. AA Fund) at a time and for Net Purchases for fund total ($6,678.00 as of Oct 1, 2018). I would like to create a table that shows the time series.

For example, column A should be the date, 10/01/2018, 10/02/2018 and so on. Column B should be Net Purchase under fund total. I have 5 years of data for 16 funds. I tried INDEX/MATCH, but I couldn't figure it out. What formula could I put to pull the 6678 value under net purchase/fund total for AA fund as of Oct 1, and the next row in column B will pull 8678 value as of Oct 2? I am dire need of help.

10/01/18
Gross PurchasesExchanges-InGross RedemptionExchanges-OutNet Purchases
AA Fund
Retail111222155-23355
Institutional5197050-33307236
Fund Total8427272-1203-2336678
BB Fund
Retail20003000-200-4000800
Institutional3000045000-2000073000
Fund Total3200048000-31000-4000-75000
Oct 2, 2018
AA Fund
Retail111222-155-233-55
Institutional5197050-3330-503
Fund Total8427272-1203-2338678
BB Fund
Retail20003000-200-4000800
Institutional3000045000-200001500
Fund Total34,100XXXYYYZZZ80,935

<tbody>
</tbody>
 

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,706
Either use an OFFSET formula or transform your data into a proper data Table that can be used.
Transform data using PowerQuery
 

Nine Zero

Well-known Member
Joined
Mar 10, 2016
Messages
622
Alright so this is a super workaround if your data is EXACTLY LIKE YOU MENTIONED ABOVE the only change i had to make to allow it to function properly was switch your first and second row to put the header on top

Sheet1


Sheet2 the result i believe



This is the code for this.. not sure if its what you need..... I didnt wanna continue to far in case this was not the desired result

Code:
Sub Mneghassi()
Dim q, r As Range


For Each q In Sheets("Sheet2").Range("A2:A12")
    For Each r In Sheets("Sheet1").Range("B1:B20")
        If q.Value = r.Value Then
            Sheets("Sheet2").Cells(q.Row, 2).Value = r.Offset(4, 4).Value
        End If
    Next r
Next q


End Sub
 
Last edited:

mneghassi

New Member
Joined
Jul 17, 2018
Messages
18
Alright so this is a super workaround if your data is EXACTLY LIKE YOU MENTIONED ABOVE the only change i had to make to allow it to function properly was switch your first and second row to put the header on top

Sheet1


Sheet2 the result i believe



This is the code for this.. not sure if its what you need..... I didnt wanna continue to far in case this was not the desired result

Code:
Sub Mneghassi()
Dim q, r As Range


For Each q In Sheets("Sheet2").Range("A2:A12")
    For Each r In Sheets("Sheet1").Range("B1:B20")
        If q.Value = r.Value Then
            Sheets("Sheet2").Cells(q.Row, 2).Value = r.Offset(4, 4).Value
        End If
    Next r
Next q


End Sub
That looks good. I'm getting a slighted reformatted version in an attempt to make life slightly easier

smh
 
Last edited:

Forum statistics

Threads
1,085,537
Messages
5,384,299
Members
401,887
Latest member
Somesh

Some videos you may like

This Week's Hot Topics

Top