Good afternoon!
If I should be posting this to the Formulas thread, kindly let me know please


So, I'm completely new to this forum and I have tried searching it for my answer, but I'm not exactly familiar with the terminology I need to use in this case. So, I'll explain what I'm trying to do and hopefully somebody can give me some insight as to what I should be searching for!

Basically, I'm in charge of multiple different ledger accounts, (extremely old and outdated accounting software) I have my raw data imported from the software and organized like this in an excel workbook:

C130.0004.0001 CLOSED PROJECT
C130.0004.0987 T/F Invest in Ontario Res
C130.0004.5351 Computer Software Service
C130.0008.0001 CLOSED PROJECT
C130.0008.0905 Tsf from Gen Bldng Res
C130.0008.8902 Construction Costs
C130.0008.8915 Furniture Fixtures Equip
C130.0012.0991 Tsf From Revenue Fund
C130.0012.5351 Computer Software Service
C130.0013.0987 T/F Invest in Ontario Res
C130.0013.8901 Cap Expend-Other
C130.9618.0900 Tsf From Gen Capital Res
C130.9618.8904 Equipment Purchases
C140.0004.0001 CLOSED PROJECT
C140.0004.0900 Tsf From Gen Capital Res................


This goes on for about 7000 rows.

The first 8 digits identify the specific capital project, and the individual accounts in each project are identified by the last 4 digits.
Any accounts ending in ".0001" are only there to show if the project has been closed or not. (simply an "identifying" account, in essence)

Long story short - I need a macro that will essentially lookup wherever "Closed Project" or "Cancelled Project" is in Column B, and then use the first 8 digits of the Account # in Column A to lookup and output (to a new worksheet) all the associated individual accounts that have the same first 8 digits.


Which functions or macros should I be searching for/using to do this in the most effective way?
Hopefully I'm communicating my objective clearly.
Any help is greatly appreciated!