Copy and Paste if Data Matched
Hi Experts,
I would like to request Experts help to write a macro for copying the data if the data is matched. Need to crosscheck data from Sheet 1 (Table 1 to 6) with data at “Type” columns at the sheet2. If the first 5 alphabets at Sheet2 is matched with the data sheet1, thus copy all the matched data (Number and Type) of the sheet2 at Matched sheet. I have attached the sample data at “Matched” sheet for Experts to get better view. Hope Experts will help me to create this feature.
DataCopy.xls
I would like to request Experts help to write a macro for copying the data if the data is matched. Need to crosscheck data from Sheet 1 (Table 1 to 6) with data at “Type” columns at the sheet2. If the first 5 alphabets at Sheet2 is matched with the data sheet1, thus copy all the matched data (Number and Type) of the sheet2 at Matched sheet. I have attached the sample data at “Matched” sheet for Experts to get better view. Hope Experts will help me to create this feature.
DataCopy.xls
ASKER
Hi Long,
Thanks for the help. We need to copy the whole data from Type column (Sheet2). For e.g. ATZDI is duped at Sheet 2 4X:
571479 ATZDI01HS11A
571479 ATZDI01HS11B
572599 ATZDI01HS11B
573150 ATZDI01HS11A
All these numbers need to copied Matched sheet. Hope the example gives you better idea.
Thanks for the help. We need to copy the whole data from Type column (Sheet2). For e.g. ATZDI is duped at Sheet 2 4X:
571479 ATZDI01HS11A
571479 ATZDI01HS11B
572599 ATZDI01HS11B
573150 ATZDI01HS11A
All these numbers need to copied Matched sheet. Hope the example gives you better idea.
Hello Cartillo,
Alright, I have adjusted the code to cover all duplications. Could you please try it again?
Thanks,
Long
DataCopy.xls
Alright, I have adjusted the code to cover all duplications. Could you please try it again?
Thanks,
Long
DataCopy.xls
ASKER
Hi Long,
Thanks for the solution. Is that possible for me to populate a new data at Matched sheet without overridden the old data? This is allow me to compile all data by running the script multiple times. Hope you will consider this.
Thanks for the solution. Is that possible for me to populate a new data at Matched sheet without overridden the old data? This is allow me to compile all data by running the script multiple times. Hope you will consider this.
Hello Cartillo,
I need some inputs:
(1) Can the number of column in Matched sheet be fixed? User is never asked for his/her input again when running the code.
(2) Every time you change data of "Sheet 2", you need all previous data in Matched sheet remains and new matched data is appended at the end of Matched sheet?
Regards,
Long
I need some inputs:
(1) Can the number of column in Matched sheet be fixed? User is never asked for his/her input again when running the code.
(2) Every time you change data of "Sheet 2", you need all previous data in Matched sheet remains and new matched data is appended at the end of Matched sheet?
Regards,
Long
ASKER
Hi Long,
>Yes, we can fixed the column, let say from A:P
>You’re right.
>Yes, we can fixed the column, let say from A:P
>You’re right.
ASKER
Hi Long,
I’m having big data at sheet 2, same data rows that I have used before. When I run using the revised version, it shows error at this line:
strTmp = Trim(Str(vIn(i, j))) & vIn(i, j + 1)
Hope this can be fixed.
I’m having big data at sheet 2, same data rows that I have used before. When I run using the revised version, it shows error at this line:
strTmp = Trim(Str(vIn(i, j))) & vIn(i, j + 1)
Hope this can be fixed.
Hello Cartillo,
I have completed your request and would like to send it to you to review. Please kindly review the attached file and let me know if it is good.
Regards,
Long
DataCopy.xls
I have completed your request and would like to send it to you to review. Please kindly review the attached file and let me know if it is good.
Regards,
Long
DataCopy.xls
Hello Cartillo,
In regards to the big data set, please send it to me to review. I guess it may relate to the limitation in rows in Excel 2003.
Regards,
Long
In regards to the big data set, please send it to me to review. I guess it may relate to the limitation in rows in Excel 2003.
Regards,
Long
Hello Cartillo,
Like I guessed, the error happens because the result data contains over 65536 rows. I have attached the spreadsheet with large data as well as the error produced when running the code for your reference.
Please try to reduce the data and try again. OR you might need to switch to Excel 2007 to get rid of the limitation in rows.
I hope it helps.
Long
DataCopy---Large-Data.xls.zip
Like I guessed, the error happens because the result data contains over 65536 rows. I have attached the spreadsheet with large data as well as the error produced when running the code for your reference.
Please try to reduce the data and try again. OR you might need to switch to Excel 2007 to get rid of the limitation in rows.
I hope it helps.
Long
DataCopy---Large-Data.xls.zip
ASKER
Hi Long,
OK, I will control the data rows since the data at "Matched" sheet can be accumulated. I've noticed the data not exist in Sheet1 but it was copied at "Matched" sheet. By right only the matched data need to be copied. I have attached the sample data "marked in red" for your reference.
DataCopy-2-.xls
OK, I will control the data rows since the data at "Matched" sheet can be accumulated. I've noticed the data not exist in Sheet1 but it was copied at "Matched" sheet. By right only the matched data need to be copied. I have attached the sample data "marked in red" for your reference.
DataCopy-2-.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Long,
Thanks a lot for helping me to solve this problem.
Thanks a lot for helping me to solve this problem.
ASKER
Hi Long,
I need your help. I’m using the same solution that you’ve provided me earlier but now need to look at different data.
https://www.experts-exchange.com/questions/26889721/Rewrite-script-for-copy-and-paste-data.html
Hope you’ll consider this request.
I need your help. I’m using the same solution that you’ve provided me earlier but now need to look at different data.
https://www.experts-exchange.com/questions/26889721/Rewrite-script-for-copy-and-paste-data.html
Hope you’ll consider this request.
I have created a code and would like to attach it here for you to review. Could you please review the attached file to see if it meets your expectation?
Regards,
Long
DataCopy.xls