I have a table that contains transactions made by each of my projects.
| Project Code | Project Name | Amount ($) |
|---|---|---|
| ABC | Project A | 400 |
| Transaction Code: … (11-01-2024) | 100 | |
| Transaction Code: … (16-02-2024) | 100 | |
| Transaction Code: … (04-03-2024) | 200 | |
| DEF | Project B | 640 |
| Transaction Code: … (14-02-2024) | 150 | |
| Transaction Code: … (21-05-2024) | 320 | |
| Transaction Code: … (30-05-2024) | 170 |
I want to get the monthly transactions made by each project so that I can convert them into this table:
| Project Code | Project Name | January | February | March | April | May |
|---|---|---|---|---|---|---|
| ABC | Project A | |||||
| DEF | Project B |
I’m thinking of first using VLOOKUP or INDEX/MATCH to find the project code (some projects have the same name), using that as a base, and then returning all the rows between “Project A” and “Project B” so I can use the date information at the end of the transaction code. I think I could use this to sort the transactions (for example, I need to find the cells ending with “01-2024)” for January). Note that some projects have multiple transactions per month, so the formula must take this into account.
What gets me stuck is the part where I have to find the transaction code between “Project A” and “Project B.” I’m thinking of using a formula to find all rows below Project A where Column B starts with “Transaction Code.” But how do I get the formula to stop when the criteria aren’t met?
I’m not sure if what I want is even possible with a formula, so I don’t mind using VBA if necessary. However, I’d like to know if there’s an easier way to do this.
Roach is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.