• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Code to Dynamically Expand Table if Another Table Expands

vletm

Excel Ninja
... and You just remember that minor thing ...
Those 19 seems to be in Monthly Budget-sheet.
You could use basic Excel's Find-procedure to find cells.
 

vletm

Excel Ninja
I didn't watch any formulas.
Your At this point I just need to find where these unique IDs are ...
Mine Those 19 seems to be in Monthly Budget-sheet ... as well as in Your Sheet1.
What's Your point now?
 

JenniferS

Member
My point now is there are 19 unique transactions between the Transactions sheet and the Notes sheet yet there are only 4 more rows.

So there seems to be something weird going on with the data.
 

vletm

Excel Ninja
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.
 

JenniferS

Member
Yes what happened was the banks changed IDs for the same transactions so please use my latest file. It has all of the transactions but the IDs are different than earlier workbooks.
 

vletm

Excel Ninja
Do not Refresh Transactions!
This file has same data.
I tested my way ... compared date, Merchant and Amount $.
Watch Transaction - and Notes-sheets A-columns,
those numbers shows other sheets rownumbers.
There are few duplicates for ... reason - marked with yellow.
 

Attachments

JenniferS

Member
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.
 

vletm

Excel Ninja
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
Screenshot 2021-10-24 at 09.50.49.png
... 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?
 
Last edited:

vletm

Excel Ninja
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
Screenshot 2021-10-24 at 12.06.42.png
 

Attachments

JenniferS

Member
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.
 

Attachments

vletm

Excel Ninja
Seems You do not use my modifications.
That my code, which You have, won't work well without those.
 

JenniferS

Member
I do not want drop down lists for Merchants. As far as what you say is new code I don't know what you mean. I am using your code.
 

JenniferS

Member
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?
 

Attachments

vletm

Excel Ninja
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,
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'.​
 

JenniferS

Member
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
 

JenniferS

Member
I think I have it now. What does the number "4" in cell C1 mean? Is that the number of differences that may occur? At this point there aren't any so I don't know what that is for.

What is the green highlight for in cell C2?

Thank you
 

Attachments

vletm

Excel Ninja
Seems You've skipped some of my writings eg below,
as well as there are those red right top corners (comments) ...
which You asked above:
Screenshot 2021-10-25 at 18.37.15.png
If Your datas are same with both sheets then there are nothing to test/verify.
 

JenniferS

Member
Yes they are the same but there will be future transactions that get imported when using the Plaid Sync code. I want to make sure that there will not be any differences as a cross check safety.

Thank you if you can help me with that.
 

JenniferS

Member
I was able to adapt your code to my workbook that has the correct transactions now.

I will continue to test. I do hope also that someone else can benefit from your valuable work.

Have a great day ahead!

Thank you
 

Attachments

Last edited:
Top