I have uploaded a comprehensive real version of my project to show you what I want the macro to do exactly.
http://dl.dropbox.com/u/14436395/Book1.zip
Here are some rules for the macro (explained thoroughly):
- Go through each cell in sheet1 column C and match it with the value in sheet2 column A. if a match is found then copy the data from the declared columns in sheet1 and paste them to the declared columns in sheet2.
- if a value in sheet1 column C is not present in sheet2 col A, then insert that row with that value in sheet2 col A, and import the data from the respective columns (as above).
- If a certain value in sheet1 col C occurs X times, and that same value in sheet2 col A occurs Y times, then it means X nr. of times must the data be overwritten to sheet2 (according to the rules below).
X(1) = first row of a value occurring in sheet 1 col C . X(2), X(3).... etc
Y(1) = first row the same value occurs in sheet2 col A . Y(2), Y(3)...... etc
Data flow path:
columns of X(1) -> columns of Y(1)
columns of X(2) -> columns of Y(2)
... and so on
Cases:
X>Y : overwrite data X times to the respective row position of same value in sheet2. X-Y nr. of new blank rows created (below existing row(s) of that value with that value then entered in sheet2 col A) to accommodate new data coming from columns of sheet1
X=Y: no new rows needed. overwrite data X times in sheet2
X<Y : no new rows needed. overwrite data X times in sheet2. the remaining rows (Y-X) remain the same.
- Overwriting rules: if before pasting the data in sheet2, the respective cell contains no data then simply paste the value in that cell. if that cell contains the same data to be pasted then just paste it into that cell (or do nothing). if the data in that cell is different to the data to be pasted then save the old data as a comment in that cell and paste the new data.
How I approached this:
- I started off with a macro to create new rows depending on the multiple occurring same value i compared between sheet1 col C and sheet2 col A
http://www.ozgrid.com/forum/showthread.php?t=147485
- After the new rows were inserted at the bottom of the worksheet , i used the auto filter to sort the values in sheet2 col A by ascending order. now the new rows were below the original rows for every value. you may just want to create the new rows (depending on the cases for X and Y) below the existing row(s) when needed.
- Now I ran the macro to import data from the declared columns in sheet1 and put it in the declared columns in sheet2 .
http://www.ozgrid.com/forum/showthread.php?t=147461
Here the code only works for the first time entry of the same value i.e data from the columns in the row of X(1) goes to the columns of the row of Y(1). Y(1), (Y2) ,etc.. remain the same , or in the case of the new rows inserted , they stay blank. (so you need to modify the code as i described above).
I hope this helps. Please let me know if you need any more information.
Cheers,
JN
http://dl.dropbox.com/u/14436395/Book1.zip
Here are some rules for the macro (explained thoroughly):
- Go through each cell in sheet1 column C and match it with the value in sheet2 column A. if a match is found then copy the data from the declared columns in sheet1 and paste them to the declared columns in sheet2.
- if a value in sheet1 column C is not present in sheet2 col A, then insert that row with that value in sheet2 col A, and import the data from the respective columns (as above).
- If a certain value in sheet1 col C occurs X times, and that same value in sheet2 col A occurs Y times, then it means X nr. of times must the data be overwritten to sheet2 (according to the rules below).
X(1) = first row of a value occurring in sheet 1 col C . X(2), X(3).... etc
Y(1) = first row the same value occurs in sheet2 col A . Y(2), Y(3)...... etc
Data flow path:
columns of X(1) -> columns of Y(1)
columns of X(2) -> columns of Y(2)
... and so on
Cases:
X>Y : overwrite data X times to the respective row position of same value in sheet2. X-Y nr. of new blank rows created (below existing row(s) of that value with that value then entered in sheet2 col A) to accommodate new data coming from columns of sheet1
X=Y: no new rows needed. overwrite data X times in sheet2
X<Y : no new rows needed. overwrite data X times in sheet2. the remaining rows (Y-X) remain the same.
- Overwriting rules: if before pasting the data in sheet2, the respective cell contains no data then simply paste the value in that cell. if that cell contains the same data to be pasted then just paste it into that cell (or do nothing). if the data in that cell is different to the data to be pasted then save the old data as a comment in that cell and paste the new data.
How I approached this:
- I started off with a macro to create new rows depending on the multiple occurring same value i compared between sheet1 col C and sheet2 col A
http://www.ozgrid.com/forum/showthread.php?t=147485
- After the new rows were inserted at the bottom of the worksheet , i used the auto filter to sort the values in sheet2 col A by ascending order. now the new rows were below the original rows for every value. you may just want to create the new rows (depending on the cases for X and Y) below the existing row(s) when needed.
- Now I ran the macro to import data from the declared columns in sheet1 and put it in the declared columns in sheet2 .
http://www.ozgrid.com/forum/showthread.php?t=147461
Here the code only works for the first time entry of the same value i.e data from the columns in the row of X(1) goes to the columns of the row of Y(1). Y(1), (Y2) ,etc.. remain the same , or in the case of the new rows inserted , they stay blank. (so you need to modify the code as i described above).
I hope this helps. Please let me know if you need any more information.
Cheers,
JN
