Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Copy and Paste if Data Matched

Posted on 2011-03-10
15
Medium Priority
?
278 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:Cartillo
  • 8
  • 7
15 Comments
 
LVL 3

Expert Comment

by:longtruong
ID: 35104922
Hello Cartillo,

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
0
 

Author Comment

by:Cartillo
ID: 35106011
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.

0
 
LVL 3

Expert Comment

by:longtruong
ID: 35106106
Hello Cartillo,

Alright, I have adjusted the code to cover all duplications.  Could you please try it again?

Thanks,

Long
DataCopy.xls
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Cartillo
ID: 35106315
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.
0
 
LVL 3

Expert Comment

by:longtruong
ID: 35106407
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
0
 

Author Comment

by:Cartillo
ID: 35106552
Hi Long,

>Yes, we can  fixed the column, let say from A:P
>You’re right.
0
 

Author Comment

by:Cartillo
ID: 35107041
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.
0
 
LVL 3

Expert Comment

by:longtruong
ID: 35107143
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
0
 
LVL 3

Expert Comment

by:longtruong
ID: 35107158
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
0
 

Author Comment

by:Cartillo
ID: 35107297
Hi,

Here’s the data that I’ve exported into sheet2.

Sdata.zip
0
 
LVL 3

Expert Comment

by:longtruong
ID: 35107343
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

Error descriptionDataCopy---Large-Data.xls.zip
0
 

Author Comment

by:Cartillo
ID: 35107458
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
0
 
LVL 3

Accepted Solution

by:
longtruong earned 2000 total points
ID: 35121713
Hello Cartillo,

I have added one more validation to the code and it now runs pretty well.  Could you please check the attached file and let me know if it is good?

Regards,

Long
DataCopy-2-.xls
0
 

Author Closing Comment

by:Cartillo
ID: 35144014
Hi Long,

Thanks a lot for helping me to solve this problem.
0
 

Author Comment

by:Cartillo
ID: 35144364
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.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26889721.html

Hope you’ll consider this request.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question