Updating sheet with data from another sheet
Hello there,
I'm still quite new to VBA and I'm facing optimization problem with quite simple macro working with a lot of data.
To describe what I need to acheive:
I have two sheets of data. I need to take one sheet, check for data in another sheet and update it if needed. Visual usually works best so bellow you can find very simplified example.
As you might see, I have some data in sheet 1 and 2. I need to take ID from sheet 2, check for it in sheet 1 and see if it is there.
If yes, I need to update all cells on corresponding row in sheet 1 with data from 2. 2 does not have to contain all the data for the row, so I need to be sure I'm not removing anything from 1 by copying empty cells from 2 (as shown with ID 555).
If not, I need to add new ID and all it's data from 2 to 1 (as shown with ID 666, 777 and 888).
As shown with ID 111, sheet 2 does not have to contain all the IDs listed in sheet 1 and vice versa.
I have a brute-force macro simply comparing ID from 2 to IDs from 1 as strings deciding what to do in double loop, but this is terrible inefficient, since I have to work with for example 10 000 rows on sheet 1 and another 18 000 rows on sheet 2 (both have about 50 columns), which can end up as sheet 1 having 25 000 rows in total after the update (I don't care for sheet 2 when it is done).
So, any tips how to smoothen the code and reach best working speed would be much appreciated.
And here is the example I promised
sheet 1 (original data) |
name |
surname |
ID |
status |
note |
John |
Doe |
111 |
pending |
|
Jane |
Dee |
222 |
ok |
married |
Adam |
|
333 |
ok |
|
Default |
Name |
444 |
pending |
|
Peter |
Pan |
555 |
|
check |
|
|
|
|
|
sheet 2 (updated data) |
name |
surname |
ID |
status |
note |
Jane |
Dee |
222 |
valid |
still married |
Adam |
Brown |
333 |
valid |
|
Simon |
Whatisname |
444 |
ok |
check |
Peter |
Pan |
555 |
ok |
|
Arthur |
Novak |
666 |
valid |
|
Anna |
Marie |
777 |
ok |
single |
Lili |
White |
888 |
ok |
check |
|
|
|
|
|
sheet 1 (updated) |
name |
surname |
ID |
status |
note |
John |
Doe |
111 |
pending |
|
Jane |
Dee |
222 |
valid |
|
Adam |
Brown |
333 |
valid |
still married |
Simon |
Whatisname |
444 |
ok |
|
Peter |
Pan |
555 |
ok |
check |
Arthur |
Novak |
666 |
valid |
check |
Anna |
Marie |
777 |
ok |
single |
Lili |
White |
888 |
ok |
check |