macro to delete lines based on a value - Repost

[MarkT]

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

 

[Jim Thomlinson]

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...

 

[MarkT]

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.