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
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
ASKER
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.
> 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.
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
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
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
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
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
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
ASKER
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).
>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).
ok pls allow me 1 hour I hv to leave office now will do upon arrival
rgds/gowflow
rgds/gowflow
ASKER
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.
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.
ASKER
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.
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.
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
>>> 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
ASKER
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.
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.
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
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
ASKER
Hi Gowflow,
I need to run this type of query every week.
I need to run this type of query every week.
ok did you ever ran this and if yes how much time it took ??
Rgds/gowflow
Rgds/gowflow
ASKER
Hi,
Its ran > 3hours without any result, thus I force the excel to stop.
Its ran > 3hours without any result, thus I force the excel to stop.
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
Rgds/gowflow
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 Goflow,
Shows an error when I run macro as “Object not found” (.ProgressBar1.). How to fix this?
Shows an error when I run macro as “Object not found” (.ProgressBar1.). How to fix this?
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
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
ASKER
Hi Gowflow,
I'm using Office 2007, not seeing such object exist at reference.
I'm using Office 2007, not seeing such object exist at reference.
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
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
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
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
ASKER
Hi Gowflow,
Thanks a lot for the help.
Thanks a lot for the help.
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