Looping through a table to update values in another table or add new rows if not already existent

cemtas

New Member
Joined
Dec 29, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi,

I am trying to accomplish the following, any tips/pointers you can provide will be greatly appreciated.

I have a table in a worksheet. I have another table (with the same structure) in another worksheet. The 2nd table contains records which may or may not already exist in the 1st table. When I press a button, I want to run a VBA code which loops through table #2. For every record, it should check whether the same record exists in table #1. If so, it should update the fields of the table #1 with the values found in table #2. If no record exists, it should add a new record to table #1 using the values found in table #2.

To make this a bit more visual, this is how the tables look like:

TABLE_1 in Sheet1:
FY_PERIODPROJECTIDABCDEFGHI
2020-10P000003288
55​
Text 1100%
2020-10P000003293
84​
Text 20%
2020-10P000003837
666​
Text 350%
2020-10P000003838
777​
Text 425%
2020-10P000003839
555​
Text 50%

FY-PERIOD and PROJECTID together make up the unique key for this table.

TABLE_2 in Sheet2:
FY_PERIODPROJECTIDABCDEFGHI
2020-10P000003288
88​
Text 190%
2020-10P000003290
77​
Text B100%
2020-10P000003837
680​
Text C50%
2020-10P000003845
444​
Text D10%
2020-10P000003856
333​
Text E100%
2020-09P000003288
22​
Text F50%
2020-11P000003288
55​
Text G15%

After running the add/update VBA code, the TABLE_1 should look like as follows:

TABLE_1 in Sheet1 after the update (updates or additions marked in BOLD):
FY_PERIODPROJECTIDABCDEFGHI
2020-10P000003288
88
Text 190%
2020-10P000003293
84​
Text 20%
2020-10P000003837
680
Text C50%
2020-10P000003838
777​
Text 425%
2020-10P000003839
555​
Text 50%
2020-10P000003290
77
Text B100%
2020-10P000003845
444
Text D10%
2020-10P000003856
333
Text E100%
2020-09P000003288
22
Text F50%
2020-11P000003288
55
Text G15%

And finally, when I run another VBA sub, it should first erase TABLE_1 contents and the copy TABLE_1 to TABLE_2 in order to initialize it.

I realize that there are many ways via which this can be done in VBA. I am not asking for a ready made solution but for directions/guidance to follow on through. Such as how to loop through TABLE_2 and link the values read to TABLE_1. Thanks a lot for any help you can provide.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,791
*Since you just want general guidance:

Looping could take a while depending on the size of the table and the PC performance.

I'd copy table 1 and insert it above table 2, then remove duplicates on column B. That will delete any old data.

No need to erase 1 after, just copy columns A:E between the sheets as 2 will always be larger
 

cemtas

New Member
Joined
Dec 29, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
*Since you just want general guidance:

Looping could take a while depending on the size of the table and the PC performance.

I'd copy table 1 and insert it above table 2, then remove duplicates on column B. That will delete any old data.

No need to erase 1 after, just copy columns A:E between the sheets as 2 will always be larger
This is an interesting idea, thanks for it. My TABLE_2 lives next to a pivot table and the users take visual feedback from the pivot table in order to populate the columns table_2. That's why I cannot insert TABLE_1 on top of TABLE_2, it would shift all the data down and we would lose the visual link to the pivot table output. It looks like this:

Pivot tableTABLE_2
PROJECTIDACTUALDATA_AACTUALDATA_BFY_PERIODPROJECT_IDABCDEF
P12345612345234562020-12P123456123Text_XX
P2345673424352352020-12P234567334Text_YY
....
 

Watch MrExcel Video

Forum statistics

Threads
1,130,129
Messages
5,640,295
Members
417,135
Latest member
zeusmining

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top