.png)
![‘Companion’ Ending Breakdown: Director Drew Hancock Tells All About the Film’s Showdown and Potential Sequel: ‘That’s the Future I Want for [Spoiler]’](https://variety.com/wp-content/uploads/2025/02/MCDCOMP_WB028.jpg?#)
Data Matching In VBA - Best way to deal with dynamic data and user entry?
Background I am currently building this project with VBA, just to keep in the back of your mind when thinking about my question. Imagine 2 adjacent blocks, in Excel. The first block is made up of columns A, B, and C. The second block is made of columns D, E, and F. The first block is filled with data from a file published online as a text file. I have written a number of subroutines that will import the data I want into columns A, B, and C based on an active status. The most important data in block 1 would be in column A, it is an ID number that will never change. The subsequent data in columns B and C may change at some point in the future. When the imported data is refreshed, all of the cells in columns A, B, and C are deleted and repopulated. This data can include more or less entries on refreshing, than the previous refresh. In the second, adjacent block, the data in columns D, E, and F will be filled out by a user with information about the object in the first block. I say object referring to the ID in column A, as B and C can change. Please follow along with my imaginary time frame as it will help me ask my question later. Week 1 - See Image 1 User opens workbook: Block 1 is imported with "Update button" and Block 2 filled out by user for first time. Week 2 - See Image 2 User opens workbook: Block 1 is refreshed with "Update button" and now differs from week one. Before the user changes anything in block 2, there is an issue with the data. As the Week 2 data is brought in, the week 1 data is deleted. In week 2 only 3 id's are imported rather than the initial 5 id's. Question My question revolves around how to handle the issue presented in week 2. Block 1 now holds values in 3 rows, even though the data initially held 5 rows. Block 2's data is still in the order of week 1, so the data is now misaligned and there are two extra rows of data that do not correspond to the ID's currently imported. What are my options to deal with the excess data in block 2 and then realign blocks 1 and 2 correctly to look like this? Also to potentially store the data for what was removed... My thoughts I am not a developer of any sort, just a young engineer and I thought this would be a good tool for my company so I am trying to work my way through it. My first thought would be to ID the block 2 data with the ID from block 1 in Week 1. as this piece of data will never change (even if name, status, favorite items change). On the refreshing of the data there would be some sort of subroutine to then match the block 2 data to the new block 1 data and remove the excess and possibly store it somewhere, so the block 2 data could be retrieved in an ID was reintroduced into block 1. This seems convoluted to me, I am sure there is a better way to do this (maybe even in something outside of excel. The only reason I chose excel was because the users will need to be able to work on block 2 in excel. If you have any ideas regarding a better potential software, a different language with an interface I would have to build, or how to continue building it in Excel which would be preferred at this point, even it would require rearranging the worksheet such as holding block 1 in a separate worksheet from block 2 and then merging the data later. I would like to hear what you have to say! Thank you.
Background
I am currently building this project with VBA, just to keep in the back of your mind when thinking about my question.
Imagine 2 adjacent blocks, in Excel. The first block is made up of columns A, B, and C. The second block is made of columns D, E, and F.
The first block is filled with data from a file published online as a text file. I have written a number of subroutines that will import the data I want into columns A, B, and C based on an active status. The most important data in block 1 would be in column A, it is an ID number that will never change. The subsequent data in columns B and C may change at some point in the future. When the imported data is refreshed, all of the cells in columns A, B, and C are deleted and repopulated. This data can include more or less entries on refreshing, than the previous refresh.
In the second, adjacent block, the data in columns D, E, and F will be filled out by a user with information about the object in the first block. I say object referring to the ID in column A, as B and C can change.
Please follow along with my imaginary time frame as it will help me ask my question later.
Week 1 - See Image 1 User opens workbook: Block 1 is imported with "Update button" and Block 2 filled out by user for first time.
Week 2 - See Image 2 User opens workbook: Block 1 is refreshed with "Update button" and now differs from week one. Before the user changes anything in block 2, there is an issue with the data.
As the Week 2 data is brought in, the week 1 data is deleted. In week 2 only 3 id's are imported rather than the initial 5 id's.
Question
My question revolves around how to handle the issue presented in week 2. Block 1 now holds values in 3 rows, even though the data initially held 5 rows. Block 2's data is still in the order of week 1, so the data is now misaligned and there are two extra rows of data that do not correspond to the ID's currently imported.
What are my options to deal with the excess data in block 2 and then realign blocks 1 and 2 correctly to look like this? Also to potentially store the data for what was removed...
My thoughts
I am not a developer of any sort, just a young engineer and I thought this would be a good tool for my company so I am trying to work my way through it.
My first thought would be to ID the block 2 data with the ID from block 1 in Week 1. as this piece of data will never change (even if name, status, favorite items change). On the refreshing of the data there would be some sort of subroutine to then match the block 2 data to the new block 1 data and remove the excess and possibly store it somewhere, so the block 2 data could be retrieved in an ID was reintroduced into block 1.
This seems convoluted to me, I am sure there is a better way to do this (maybe even in something outside of excel. The only reason I chose excel was because the users will need to be able to work on block 2 in excel.
If you have any ideas regarding a better potential software, a different language with an interface I would have to build, or how to continue building it in Excel which would be preferred at this point, even it would require rearranging the worksheet such as holding block 1 in a separate worksheet from block 2 and then merging the data later. I would like to hear what you have to say!
