VBA Code to Dynamically Expand Table if Another Table Expands
- Thread starter JenniferS
- Start date
The basic rule was that Transaction IDs are fixed - otherways there will be challenges.
... as well as there could be duplicates with 'new' data.
If fixed Transaction IDs then there could update needed columns in Notes-sheet .. otherways ... something else.
I tested to 'update' Notes ... and there were over 4k rows of data.
... as well as there could be duplicates with 'new' data.
If fixed Transaction IDs then there could update needed columns in Notes-sheet .. otherways ... something else.
I tested to 'update' Notes ... and there were over 4k rows of data.
Okay maybe I am not being clear.
The raw data comes from the Transactions sheet table (Table1) which will always be adding more transactions by an external code that I cannot control. It is done with what is called Plaid which is a security that gets transactions from banks and credit cards.
The Category and Subcategory from them is not always correct is why I need another sheet to have so I can properly identify the Category and Subcategory. Also this sheet does not allow notes is why there is a Notes column in Table4.
So the Notes sheet table (Table4) needs to have the same number of rows and the Date, Merchant, Amount, and Account needs to be the same for the transactions. Because of this there needs to not be unique Transaction IDs between the 2 tables and there needs to be the same number of rows in each table.
I hope I have better explained the situation.
Thank you again for your help.
The raw data comes from the Transactions sheet table (Table1) which will always be adding more transactions by an external code that I cannot control. It is done with what is called Plaid which is a security that gets transactions from banks and credit cards.
The Category and Subcategory from them is not always correct is why I need another sheet to have so I can properly identify the Category and Subcategory. Also this sheet does not allow notes is why there is a Notes column in Table4.
So the Notes sheet table (Table4) needs to have the same number of rows and the Date, Merchant, Amount, and Account needs to be the same for the transactions. Because of this there needs to not be unique Transaction IDs between the 2 tables and there needs to be the same number of rows in each table.
I hope I have better explained the situation.
Thank you again for your help.
The raw data comes ...
You've written that.
The Category and Subcategory from...
You've written that.
the Notes sheet table (Table4) needs to have the same number of rows and the Date, Merchant, Amount, and Account needs to be the same for the transactions.
My previous file checks that 75% - Account missed, because ...
I can add it there, and after that it's possible to know which row match with which row.
... after few tests - below rows cannot find from Transactions-sheet --- the last one is some kind of 'mystery' row
... after deleting those ... four - there will be same number of rows.
... Because of this there needs to not be unique Transaction ...
and Your original The data on the Notes sheet rows must stay with the Transaction IDs.
... to keep notes with correct 'action' there have to be something unique with both sheets.
... of course it could be combination of those four cells per row - slow!
>>> while testing, Transaction IDs were unique ... hmm? <<<
For Refreshing -code,
there should add feature, which shows new rows in Notes-sheet.
... as well as to add new unique ID for each row. Seems that no need!
Can You send two files... one before Plaid and other after Plaid?
You've written that.
The Category and Subcategory from...
You've written that.
the Notes sheet table (Table4) needs to have the same number of rows and the Date, Merchant, Amount, and Account needs to be the same for the transactions.
My previous file checks that 75% - Account missed, because ...
I can add it there, and after that it's possible to know which row match with which row.
... after few tests - below rows cannot find from Transactions-sheet --- the last one is some kind of 'mystery' row
... after deleting those ... four - there will be same number of rows.
... Because of this there needs to not be unique Transaction ...
and Your original The data on the Notes sheet rows must stay with the Transaction IDs.
... to keep notes with correct 'action' there have to be something unique with both sheets.
... of course it could be combination of those four cells per row - slow!
>>> while testing, Transaction IDs were unique ... hmm? <<<
For Refreshing -code,
there should add feature, which shows new rows in Notes-sheet.
Can You send two files... one before Plaid and other after Plaid?
Last edited:
If those have same data ... then have to wait until something will happen
... and after that could do more tests.
I continue testing ...
> with that data - now, there are four 'mysteries'
--- after You've solved ... what are those? ... maybe those four could delete from Notes-sheet?
> I've there code to compare those two sheets and it will show 'mysteries'
> I added three 'filtering'-cells to left to corner.
[ ALL ] shows all rows > select to filter
[ yellow ] shows number of those 'mysteries' > select to filter
[ green ] shows number of new rows from Transactions > select to filter
... and after that could do more tests.
I continue testing ...
> with that data - now, there are four 'mysteries'
--- after You've solved ... what are those? ... maybe those four could delete from Notes-sheet?
> I've there code to compare those two sheets and it will show 'mysteries'
> I added three 'filtering'-cells to left to corner.
[ ALL ] shows all rows > select to filter
[ yellow ] shows number of those 'mysteries' > select to filter
[ green ] shows number of new rows from Transactions > select to filter
Hello,
I really appreciate all of your help. What I have done now is find the Transaction IDs in both tables for unique values. I updated so that all are matching now. The table row counts are also the same.
I will continue to use the Plaid Sync to see if there will be new transactions and verify that the row counts remain the same and there are no different Transaction IDs.
I really appreciate all of your help. What I have done now is find the Transaction IDs in both tables for unique values. I updated so that all are matching now. The table row counts are also the same.
I will continue to use the Plaid Sync to see if there will be new transactions and verify that the row counts remain the same and there are no different Transaction IDs.
Okay what I discovered in your latest version was the Refresh method for applying Category and Subcategory are not what I want.
In my other version I would copy a line including Merchant, Category, and Subcategory to the table on the right in the Notes sheet. Then I would ApplyRules code. I do this when that particular Merchant will always have the same Category and Subcategory.
I have a request as a cross checking to make sure that all Transaction_IDs in both sheets are the same.
I put a table in the Transactions sheet that would list the differences if they occur which will need code.
Can you please derive a code to do this?
In my other version I would copy a line including Merchant, Category, and Subcategory to the table on the right in the Notes sheet. Then I would ApplyRules code. I do this when that particular Merchant will always have the same Category and Subcategory.
I have a request as a cross checking to make sure that all Transaction_IDs in both sheets are the same.
I put a table in the Transactions sheet that would list the differences if they occur which will need code.
Can you please derive a code to do this?
As written,
Of course, it's Your choice - which code do You use.
I won't guess - what do You need or use?
I surprised the You do not want to see - which Transactions are new? ... okay.
You asked more code,
but I have there already something same - compare,
Of course, it's Your choice - which code do You use.
I won't guess - what do You need or use?
I surprised the You do not want to see - which Transactions are new? ... okay.
You asked more code,
but I have there already something same - compare,
which shows those rows - instead that You've to find those later.
... and You had an idea to have it in sheet, which You cannot 'control'.
I put it in the Transactions sheet because of the limited view in the Notes sheet. But I can see your point that sheet is not in my control and it make be affected by Plaid code.
However if you can make it so the 2nd table in the Notes sheet is the way I had it before with the ApplyRules method it will be the best way.
Then have it so the Notes sheet main table has the option of showing differences if any.
The Transactions from my latest version are all accurate and correct for both sheets so I will have to copy these over if you have a new version again.
Thank you
However if you can make it so the 2nd table in the Notes sheet is the way I had it before with the ApplyRules method it will be the best way.
Then have it so the Notes sheet main table has the option of showing differences if any.
The Transactions from my latest version are all accurate and correct for both sheets so I will have to copy these over if you have a new version again.
Thank you
