Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to delete lines based on a value - Repost
Greetings all:
This is a repost from an earlier one to include more specific info as requested. I export from Peachtree a report that has the following columns: Account ID (A1), Account Name (B1), Amount (C1), Customer (D1) & Sales Rep ID (E1) (these are the column header names on line 1, then the specific data starts in cell A2 on down to ??? Each sale that our sales reps make will create three or more lines on this report - depending on the number of items sold to our customers. It is in order by date created in Peachtree. One line is always titled "Accounts Receivable" and one line is always titled "sales tax payable", then there are multiple lines for the different "Sales" accounts. What I do now is I sort it by account ID, I then delete all of the "Accounts Receivable" and "Sales Tax Payable" lines, and then I sort that result by "Sales Rep ID". The result of these two sorts is the total number of sales lines sorted by each rep's name. - the same header appears, but only the sales data is left, all A/R and Sales Tax lines have been deleted. This is my question: Can I create a macro that will delete all lines that contain the account names of "Accounts Receivable and Sales Tax Payable" just leaving the lines that have the sales account names? I would then also like to sort the results by sales rep name. Is this possible? The icing on this puzzle would be to have the macro also total up the sales by sales rep - but now I think I am asking too much..... Any help on this would be greatly appreciated. I run this program a number of times during the week, and the sort routine is getting quite old - there has to be a better way to perfom this routine. I would create a macro to record my keystrokes, but again, the number of lines of each sale account varies during the month. Thanks again! Mark |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to delete lines based on a value - Repost
Based on the info that you have given a pivot table just might do the trick
with no need for code... Try the following. Place you cursor anywhere in the middle of the data. Select Data - Pivot Table. Now just select finish (You can follow all of the step in the wizard but that is probably not necessary). A new sheet will be created along with a dialog with all of the column heading (fields). Drag the sales reps to the left hand column and the Account ID next to it. Put the amounts in the middle. Select the drop down for Account ID and uncheck the two items AR and Tax... That should do it. You can also add in any more fields that you want and you are done... if you want to get fancy then add an auto format and you will have a report that is ready to print... -- HTH... Jim Thomlinson "MarkT" wrote: Greetings all: This is a repost from an earlier one to include more specific info as requested. I export from Peachtree a report that has the following columns: Account ID (A1), Account Name (B1), Amount (C1), Customer (D1) & Sales Rep ID (E1) (these are the column header names on line 1, then the specific data starts in cell A2 on down to ??? Each sale that our sales reps make will create three or more lines on this report - depending on the number of items sold to our customers. It is in order by date created in Peachtree. One line is always titled "Accounts Receivable" and one line is always titled "sales tax payable", then there are multiple lines for the different "Sales" accounts. What I do now is I sort it by account ID, I then delete all of the "Accounts Receivable" and "Sales Tax Payable" lines, and then I sort that result by "Sales Rep ID". The result of these two sorts is the total number of sales lines sorted by each rep's name. - the same header appears, but only the sales data is left, all A/R and Sales Tax lines have been deleted. This is my question: Can I create a macro that will delete all lines that contain the account names of "Accounts Receivable and Sales Tax Payable" just leaving the lines that have the sales account names? I would then also like to sort the results by sales rep name. Is this possible? The icing on this puzzle would be to have the macro also total up the sales by sales rep - but now I think I am asking too much..... Any help on this would be greatly appreciated. I run this program a number of times during the week, and the sort routine is getting quite old - there has to be a better way to perfom this routine. I would create a macro to record my keystrokes, but again, the number of lines of each sale account varies during the month. Thanks again! Mark |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to delete lines based on a value - Repost
Hi Jim,
We are getting close, but the data that is coming into the pivot table is just the number of sales, not the actual dollar sales. It looks like the table is just bringing in the count, not the actual sales amounts. Hope this makes sense. "Jim Thomlinson" wrote: Based on the info that you have given a pivot table just might do the trick with no need for code... Try the following. Place you cursor anywhere in the middle of the data. Select Data - Pivot Table. Now just select finish (You can follow all of the step in the wizard but that is probably not necessary). A new sheet will be created along with a dialog with all of the column heading (fields). Drag the sales reps to the left hand column and the Account ID next to it. Put the amounts in the middle. Select the drop down for Account ID and uncheck the two items AR and Tax... That should do it. You can also add in any more fields that you want and you are done... if you want to get fancy then add an auto format and you will have a report that is ready to print... -- HTH... Jim Thomlinson "MarkT" wrote: Greetings all: This is a repost from an earlier one to include more specific info as requested. I export from Peachtree a report that has the following columns: Account ID (A1), Account Name (B1), Amount (C1), Customer (D1) & Sales Rep ID (E1) (these are the column header names on line 1, then the specific data starts in cell A2 on down to ??? Each sale that our sales reps make will create three or more lines on this report - depending on the number of items sold to our customers. It is in order by date created in Peachtree. One line is always titled "Accounts Receivable" and one line is always titled "sales tax payable", then there are multiple lines for the different "Sales" accounts. What I do now is I sort it by account ID, I then delete all of the "Accounts Receivable" and "Sales Tax Payable" lines, and then I sort that result by "Sales Rep ID". The result of these two sorts is the total number of sales lines sorted by each rep's name. - the same header appears, but only the sales data is left, all A/R and Sales Tax lines have been deleted. This is my question: Can I create a macro that will delete all lines that contain the account names of "Accounts Receivable and Sales Tax Payable" just leaving the lines that have the sales account names? I would then also like to sort the results by sales rep name. Is this possible? The icing on this puzzle would be to have the macro also total up the sales by sales rep - but now I think I am asking too much..... Any help on this would be greatly appreciated. I run this program a number of times during the week, and the sort routine is getting quite old - there has to be a better way to perfom this routine. I would create a macro to record my keystrokes, but again, the number of lines of each sale account varies during the month. Thanks again! Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I delete a macro in Excel 2003? Button is greyed out. | Excel Discussion (Misc queries) | |||
Cannot delete a macro | Excel Discussion (Misc queries) | |||
How do I delete filtered lines in Excel 2000? | Excel Worksheet Functions | |||
How to delete a macro that has been assigned to run when workshee. | Excel Worksheet Functions | |||
Macro to delete a macro | Excel Worksheet Functions |