Solved

Copy and Paste Matched data

Posted on 2011-02-22
24
192 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

708 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

16 Experts available now in Live!

Get 1:1 Help Now