View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.misc
MarkT MarkT is offline
external usenet poster
 
Posts: 67
Default 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