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

[cemtas]

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_PERIOD	PROJECTID	ABC	DEF	GHI
2020-10	P000003288	55​	Text 1	100%
2020-10	P000003293	84​	Text 2	0%
2020-10	P000003837	666​	Text 3	50%
2020-10	P000003838	777​	Text 4	25%
2020-10	P000003839	555​	Text 5	0%

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

TABLE_2 in Sheet2:

FY_PERIOD	PROJECTID	ABC	DEF	GHI
2020-10	P000003288	88​	Text 1	90%
2020-10	P000003290	77​	Text B	100%
2020-10	P000003837	680​	Text C	50%
2020-10	P000003845	444​	Text D	10%
2020-10	P000003856	333​	Text E	100%
2020-09	P000003288	22​	Text F	50%
2020-11	P000003288	55​	Text G	15%

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_PERIOD	PROJECTID	ABC	DEF	GHI
2020-10	P000003288	88​	Text 1	90%
2020-10	P000003293	84​	Text 2	0%
2020-10	P000003837	680​	Text C	50%
2020-10	P000003838	777​	Text 4	25%
2020-10	P000003839	555​	Text 5	0%
2020-10	P000003290	77​	Text B	100%
2020-10	P000003845	444​	Text D	10%
2020-10	P000003856	333​	Text E	100%
2020-09	P000003288	22​	Text F	50%
2020-11	P000003288	55​	Text G	15%

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.

 

[mrshl9898]

*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]

*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 table			TABLE_2
PROJECTID	ACTUALDATA_A	ACTUALDATA_B	FY_PERIOD	PROJECT_ID	ABC	DEF
P123456	12345	23456	2020-12	P123456	123	Text_XX
P234567	34243	5235	2020-12	P234567	334	Text_YY
....