Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

Copy and Paste Matched data

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
Cartillo
Asked:
Cartillo
  • 14
  • 10
1 Solution
 
gowflowCommented:
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
 
CartilloAuthor Commented:
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
 
gowflowCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
gowflowCommented:
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
 
gowflowCommented:
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
 
CartilloAuthor Commented:
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
 
gowflowCommented:
ok pls allow me 1 hour I hv to leave office now will do upon arrival
rgds/gowflow
0
 
gowflowCommented:
Hi Cartillo,

Sorry for delay, pls chk this version and let me know.
Rgds/gowflow
DataCheck-1-.xls
0
 
CartilloAuthor Commented:
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
 
CartilloAuthor Commented:
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
 
gowflowCommented:
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
 
CartilloAuthor Commented:
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
 
gowflowCommented:
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
 
CartilloAuthor Commented:
Hi Gowflow,

I need to run this type of query every week.
0
 
gowflowCommented:
ok did you ever ran this and if yes how much time it took ??
Rgds/gowflow
0
 
CartilloAuthor Commented:
Hi,

Its ran > 3hours without any result, thus I force the excel to stop.
0
 
gowflowCommented:
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
 
gowflowCommented:
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
 
CartilloAuthor Commented:
Hi Goflow,

Shows an error when I run macro as “Object not found” (.ProgressBar1.). How to fix this?
0
 
gowflowCommented:
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
 
CartilloAuthor Commented:
Hi Gowflow,

I'm using Office 2007, not seeing such object exist at reference.
0
 
gowflowCommented:
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
 
gowflowCommented:
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
 
CartilloAuthor Commented:
Hi Gowflow,

Thanks a lot for the help.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 14
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now