Solved

Copy and Paste Matched data

Posted on 2011-02-22
24
193 Views
Last Modified: 2012-05-11
Hi Experts,

I would like to request Experts help to write a macro to compare data from “DataA” sheet (Column A to Column F) with  data under “Type” category (Column B,D,F,H,J,L…)   at “DataB” sheet. If the data matched, copy the matched data from DataB sheet (Number and Type) at Matched sheet. All missing data copied at the Missing sheet. I have manually copied few sample data  at Matched and Missing sheet for Experts to get better view. Hope Experts will help me to create this feature.  



DataCheck.xls
0
Comment
Question by:Cartillo
  • 14
  • 10
24 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 34959665
Hi Cartillo,

You say:
Compare DataA with DataB (type) if found then put Type and number in Matched if not found put type and number in Missing.

I suppose you want to fill Matched and Missing from Col A to X then next row pls confirm.
Also pls confirm that basically you want to have DataB broken down between Match and Missing. ?
What about if an item Exist in DataA but does not exist in DataB what to do with this item as it has no Number to it.

Rgds/gowflow
0
 

Author Comment

by:Cartillo
ID: 34960480
Hi gowflow,

> suppose you want to fill Matched and Missing from Col A to X then next row pls confirm.
Yes, you're right
> Also pls confirm that basically you want to have DataB broken down between Match and Missing. ?
Indeed, you're right too
> What about if an item Exist in DataA but does not exist in DataB what to do with this item as it has no Number to it.
Just ignore this ones.

0
 
LVL 29

Expert Comment

by:gowflow
ID: 34960867
Hi Cartillo,

Pls try this version and see if it fits your need. Basically I reported in Matched the item found at the exact same location where it was originally and same for Missing this is why you see them dispersed. We can fix this if you want them to all follows. I thought maybe like this it is easier to chk if all ok.

Also if the sheet Matched and/or missing is already there it keeps it and create a new one with the HourMin this way you can have several tests in the same file. If the sheets does not exists obviously they will be created as Matched and Missing.

Pls chk it and let me know your feedback and pls be free to ask for changes modifications.
Rgds/gowflow
DataCheck-1-.xls
0
 
LVL 29

Expert Comment

by:gowflow
ID: 34960899
Oops !!!

Again forgot the instructions !
1) Make sure Macro settings are medium
2) Enable Macros when you open the file
3) Go to Sheet DataB and Activate the command "Create Match/Missing"
4) chk the resluts.

Rgds/gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 34961116
Hi Cartillo,

Sorry a small typo in my routine while rechecking. Terribly sorry pls use this version of the file and advise results.

Rgds/gowflow
DataCheck-1-.xls
0
 

Author Comment

by:Cartillo
ID: 34961353
Hi gowflow,

>Basically I reported in Matched the item found at the exact same location where it was originally and same for Missing this is why you see them dispersed. We can fix this if you want them to all follows.

Please make the whole data follows (do not copied at the same location).
0
 
LVL 29

Expert Comment

by:gowflow
ID: 34961450
ok pls allow me 1 hour I hv to leave office now will do upon arrival
rgds/gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 34963407
Hi Cartillo,

Sorry for delay, pls chk this version and let me know.
Rgds/gowflow
DataCheck-1-.xls
0
 

Author Comment

by:Cartillo
ID: 34965720
Hi,

I have tested the file with my actual data and its not giving the right result. My actual data consist of 2,285,016 data which is up to 45702 rows (DataB) sheet, and DataA sheet has 22953 data rows. Hope you can help me to fix this and make it more flexible when we have more data.  
0
 

Author Comment

by:Cartillo
ID: 34966836
Hi,

Sorry for the wrong update, the hiccup happened because of empty cells at DataB (first 3 rows' numbers have been missing). It works after removing these empty rows. However, due to data rows (45702 rows (DataB) and  22953 rows (DataA)), the process become so slow. It takes hours to process these data. Is that possible to speedup the process? like deploying “scripting.dictionary” method.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 34968543
Sorry for the wrong update, the hiccup happened because of empty cells at DataB
>>> I noticed you have empty cells this is why I chk if empty cell simply move on. Don't understand why you need to delete the first 3 rows ?

However, due to data rows (45702 rows (DataB) and  22953 rows (DataA)), the process become so slow
>>> I do not understand your comment are you reaching the whole data or it is stopping in between ???

Before I modify the code I need your answers to above. For scripting sorry but only use VBA and we can reach better perfomrance

Rgds/gowflow
0
 

Author Comment

by:Cartillo
ID: 34968674
Hi,

The 3 rows doesn't have numbers, only consist of "type" data. Therefore I simply removed the whole row since its doesn't give any meaning without numbers.

Its running without stopped more than 3 hours until I need to force stop.  
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 29

Expert Comment

by:gowflow
ID: 34969192
Hi Cartillo,

Pls understand you have humangous data. I am building a trace and simulated the figures you gave me and in this light modifying the routine. I will shortly send you something pls be patient.

By the way you run this type of matching regularly ? Every day what is the frequency ?
Rgds/gowflow
0
 

Author Comment

by:Cartillo
ID: 34969341
Hi Gowflow,

I need to run this type of query every week.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 34969421
ok did you ever ran this and if yes how much time it took ??
Rgds/gowflow
0
 

Author Comment

by:Cartillo
ID: 34969885
Hi,

Its ran > 3hours without any result, thus I force the excel to stop.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 34970241
ok fine pls be patient and if you get a solution that run say for 4 to 6 hours to process all your data would it be ok ?

Rgds/gowflow
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 34973114
Hi Cartillo,

Sorry not to be able to go way fast Scripting is not my thing. I have worked out themodule to make it user freindly with options to pause so you can see the data and even to stop the whole process before it totally complets.

Pls chk this version and feel free to let me know your comments.

NB the time remaining may fliquer but overall should be good indication.
Rgds/gowflow
DataCheck-2-.xls
0
 

Author Comment

by:Cartillo
ID: 34976248
Hi Goflow,

Shows an error when I run macro as “Object not found” (.ProgressBar1.). How to fix this?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 34977713
Oh yes sorry forgot to mention it you need to do the following:

1) Open the file and enable macros
2) Goto the Visual Basic enviroment
3) Select Menu Tools/Refrences
4) Scroll down the list till Microsoft Windows Common Controls 6.0 (SP6) and select it
5) Press OK save the file exit and restart it it should be ok

Pls advise.
Rgds/gowflow
0
 

Author Comment

by:Cartillo
ID: 34991778
Hi Gowflow,

I'm using Office 2007, not seeing such object exist at reference.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 34991843
I just tired it with my Excel 2007 all you need to do is change the extension of the file from .xls to .xlt
Too bad you did not mention this at the first place. The file you posted was .xls which is Excel 2003 extension.

Anyway when I changed .xls to .xlt it worked perfectly.
Pls advise
Rgds/gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 34991873
In any case if it does not work by changing the extention do the following

1) open the file in Excel 2007
2) Choose Developper Tab
3) Click on Visual Basic Icon (the first one)
4) Under VBAProject you have a folder Form Expand it
5) doubleclick on UserForm1
6) In the menu Choose tools/Additional controls
7) look for Microsoft ProgressBar Control, Version 6.0 and select it.
8) Press OK and save to file
9) Open the file and run it

Rgds/gowflow
0
 

Author Closing Comment

by:Cartillo
ID: 35042194
Hi Gowflow,

Thanks a lot for the help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now