Tr# ID# (Excel)(AI)

Utilizing AI for Excel is amazing (even with prompting alone as we’re not using Premium version). At this rate, many more excel heavy based users would no longer be needed as it can be done with AI.

In our Excel spreadsheet for our transaction activity, we initially have # to record our transaction as it’s needed for us when we reconcile with AI. When testing out our workflow from transaction table to Fund Table, we needed to separate the data for that specific fund, in addition to their transaction #. We’ve created ID# as our identifier. First test, we used COUNTIF formula, but after reconciling our data, this formula picked up hidden rows. Excel is notorious when utilizing hidden rows, hence, we use vlookups, sumifs, and pivot tables for these reasons. Instead, we asked AI to update their formula to reflect hidden rows and gave us a formula that we weren’t able to think of ourselves. Therefore, we’ve created this table for reference.

It’s crazy what AI is capable of. We most likely won’t be using AI Agents for our trading or investments, but we will be utilizing AI when building the workflow of our investment portfolio.

// Source //

Fund-Tr-ID (Test) [PDF]

Formula: =A2 & "-" & TEXT(SUMPRODUCT(SUBTOTAL(103, OFFSET($A$2, ROW($A$2:$A2)-ROW($A$2), 0, 1)),--($A$2:$A2=A2)),"000")

Notes: We’ve updated the formula to reflect the table input. We were also able to reconcile our results by Fund when checking our data. We won’t be uploading any Excel documents due to its privacy and security settings. You can download the PDF and have AI extract the data for you in a structured table, then copy and paste the data directly into Excel.