I have a large file that has all of the drugs that our patients have taken
for a period of time. I need to get a subtotal by the drug number (average
cost) and the number of clients that have received the drugs by drug number.

Is there a way to get both the subtotals on a single line. I need to
transfer the data from this worksheet to another sheet provided by
contractor.

The file looks similar to this.

NDC# (drug ID)
Client#
Unit Cost
Date
(asst' other columns)

You want to treat the drugid and client# as one field (essentially)?

How about using a helper column:

=a2&"!"&b2
and drag down.

Then subtotal using that column.

Then you could hide the details
select the range to copy
edit|goto|special|check visible
copy and paste.

Then insert another column to the right of that combined field.
select that column and do data|text to columns|delimited by the !.

You may want to learn a bit about data|Pivottable. It may do what you want a
little easier (after investing a little learning time).





Steve D wrote:

I have a large file that has all of the drugs that our patients have taken
for a period of time. I need to get a subtotal by the drug number (average
cost) and the number of clients that have received the drugs by drug number.

Is there a way to get both the subtotals on a single line. I need to
transfer the data from this worksheet to another sheet provided by
contractor.

The file looks similar to this.

NDC# (drug ID)
Client#
Unit Cost
Date
(asst' other columns)

--

Dave Peterson

Dave:

The problem I have here is that I need a total of the average cost of the
drug and the number of clients that took the drug. I can get both of these
totals, but not on a single sub-total line. I can alway drag subtotal into
other columns but there are nearly 200 drugs 700 clients and over 8000
records.

"Dave Peterson" wrote:

You want to treat the drugid and client# as one field (essentially)?

How about using a helper column:

=a2&"!"&b2
and drag down.

Then subtotal using that column.

Then you could hide the details
select the range to copy
edit|goto|special|check visible
copy and paste.

Then insert another column to the right of that combined field.
select that column and do data|text to columns|delimited by the !.

You may want to learn a bit about data|Pivottable. It may do what you want a
little easier (after investing a little learning time).





Steve D wrote:

I have a large file that has all of the drugs that our patients have taken
for a period of time. I need to get a subtotal by the drug number (average
cost) and the number of clients that have received the drugs by drug number.

Is there a way to get both the subtotals on a single line. I need to
transfer the data from this worksheet to another sheet provided by
contractor.

The file looks similar to this.

NDC# (drug ID)
Client#
Unit Cost
Date
(asst' other columns)

--

Dave Peterson

So you're subtotalling by DrugId?

If yes, then just use Average for each field.

But then select the column with the client name
edit|replace
what: =subtotal(1,
with: =subtotal(3,
replace all

=subtotal(1,xxx) will do the average
=subtotal(3,xxx) will do the count (counta, actually).

Is that what you meant?



Steve D wrote:

Dave:

The problem I have here is that I need a total of the average cost of the
drug and the number of clients that took the drug. I can get both of these
totals, but not on a single sub-total line. I can alway drag subtotal into
other columns but there are nearly 200 drugs 700 clients and over 8000
records.

"Dave Peterson" wrote:

You want to treat the drugid and client# as one field (essentially)?

How about using a helper column:

=a2&"!"&b2
and drag down.

Then subtotal using that column.

Then you could hide the details
select the range to copy
edit|goto|special|check visible
copy and paste.

Then insert another column to the right of that combined field.
select that column and do data|text to columns|delimited by the !.

You may want to learn a bit about data|Pivottable. It may do what you want a
little easier (after investing a little learning time).





Steve D wrote:

I have a large file that has all of the drugs that our patients have taken
for a period of time. I need to get a subtotal by the drug number (average
cost) and the number of clients that have received the drugs by drug number.

Is there a way to get both the subtotals on a single line. I need to
transfer the data from this worksheet to another sheet provided by
contractor.

The file looks similar to this.

NDC# (drug ID)
Client#
Unit Cost
Date
(asst' other columns)

--

Dave Peterson


--

Dave Peterson