EXCELlant
New Member
- Joined
- May 28, 2009
- Messages
- 29
</SPAN>I understand that a Macro is good for completing tasks which will be repeated but I have no idea how to even start with something like this.</SPAN>
I sometimes use the record macro option but this time I need to include a search function.</SPAN>
I downloaded some data at work labeled sheet 1 (see below) which shows both changes to a vendor and newly created vendors, and I would like to split them into two new sheets.</SPAN>
Here is an example of two vendors I would like the macro to look at column E and if grouped together has more than 2 “ *** Created *** </SPAN>” in column J it removes the data and puts it into a new sheet.</SPAN>
If there is 2 or less “*** Created *** “ then it will remain in the current sheet.</SPAN></SPAN>
[TABLE="width: 607"]
<TBODY>[TR]
[TD]E</SPAN></SPAN>
[/TD]
[TD]F</SPAN></SPAN>
[/TD]
[TD]G</SPAN></SPAN>
[/TD]
[TD]H</SPAN></SPAN>
[/TD]
[TD]I</SPAN></SPAN>
[/TD]
[TD]J</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]Vendor</SPAN></SPAN>
[/TD]
[TD]Changed By</SPAN></SPAN>
[/TD]
[TD]Field Name</SPAN></SPAN>
[/TD]
[TD]Company Code</SPAN></SPAN>
[/TD]
[TD]Purch. Organization</SPAN></SPAN>
[/TD]
[TD]New value</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]300874</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Confirm.status</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300874</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Name</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300874</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Bank Details</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]*** Created ***</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]300874</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Chk double inv.</SPAN></SPAN>
[/TD]
[TD]2000</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300874</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Confirm.status</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300874</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Name</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300874</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]IBAN</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]*** Created ***</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]303901</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Confirm.status</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]303901</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]ConfirmSt (CCd)</SPAN></SPAN>
[/TD]
[TD]1000</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]303901</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Bank Details</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]*** Created ***</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]303901</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Company code data</SPAN></SPAN>
[/TD]
[TD]1000</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]*** Created ***</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]303901</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]General data</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]*** Created ***</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]303901</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Purchasing Data</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]1000</SPAN></SPAN>
[/TD]
[TD]*** Created ***</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]303901</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]IBAN</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]*** Created ***</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]303901</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Cntrl Addr.Admn</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]*** Created ***</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]303901</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Cntrl Addr.Admn</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]*** Created ***</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]303901</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Cntrl Addr.Admn</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]*** Created ***</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]303901</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Cntrl Addr.Admn</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]*** Created ***</SPAN></SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
The aim at the end of the task would be to have two sheets.</SPAN>
The one sheet displaying all of the vendors which have had modifications in the month (here highlighted in blue).</SPAN>
With another sheet with all of the new vendors for the period displayed (in the above picture highlighted in green).</SPAN>
Obviously the original data can have hundreds of lines with many new vendors or vendors which have had changes.</SPAN>
Any help, suggestions would be greatly appreciated.</SPAN>
Thank you.</SPAN>
I sometimes use the record macro option but this time I need to include a search function.</SPAN>
I downloaded some data at work labeled sheet 1 (see below) which shows both changes to a vendor and newly created vendors, and I would like to split them into two new sheets.</SPAN>
Here is an example of two vendors I would like the macro to look at column E and if grouped together has more than 2 “ *** Created *** </SPAN>” in column J it removes the data and puts it into a new sheet.</SPAN>
If there is 2 or less “*** Created *** “ then it will remain in the current sheet.</SPAN></SPAN>
[TABLE="width: 607"]
<TBODY>[TR]
[TD]E</SPAN></SPAN>
[/TD]
[TD]F</SPAN></SPAN>
[/TD]
[TD]G</SPAN></SPAN>
[/TD]
[TD]H</SPAN></SPAN>
[/TD]
[TD]I</SPAN></SPAN>
[/TD]
[TD]J</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]Vendor</SPAN></SPAN>
[/TD]
[TD]Changed By</SPAN></SPAN>
[/TD]
[TD]Field Name</SPAN></SPAN>
[/TD]
[TD]Company Code</SPAN></SPAN>
[/TD]
[TD]Purch. Organization</SPAN></SPAN>
[/TD]
[TD]New value</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]300874</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Confirm.status</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300874</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Name</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300874</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Bank Details</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]*** Created ***</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]300874</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Chk double inv.</SPAN></SPAN>
[/TD]
[TD]2000</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300874</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Confirm.status</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300874</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Name</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300874</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]IBAN</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]*** Created ***</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]303901</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Confirm.status</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]303901</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]ConfirmSt (CCd)</SPAN></SPAN>
[/TD]
[TD]1000</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]303901</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Bank Details</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]*** Created ***</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]303901</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Company code data</SPAN></SPAN>
[/TD]
[TD]1000</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]*** Created ***</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]303901</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]General data</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]*** Created ***</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]303901</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Purchasing Data</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]1000</SPAN></SPAN>
[/TD]
[TD]*** Created ***</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]303901</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]IBAN</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]*** Created ***</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]303901</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Cntrl Addr.Admn</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]*** Created ***</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]303901</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Cntrl Addr.Admn</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]*** Created ***</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]303901</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Cntrl Addr.Admn</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]*** Created ***</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]303901</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Cntrl Addr.Admn</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]*** Created ***</SPAN></SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
The aim at the end of the task would be to have two sheets.</SPAN>
The one sheet displaying all of the vendors which have had modifications in the month (here highlighted in blue).</SPAN>
With another sheet with all of the new vendors for the period displayed (in the above picture highlighted in green).</SPAN>
Obviously the original data can have hundreds of lines with many new vendors or vendors which have had changes.</SPAN>
Any help, suggestions would be greatly appreciated.</SPAN>
Thank you.</SPAN>