complex reporting

[sherry]

Hi,

I get a report in the following format:

A B C
1 Product Units sold Rate
2 richard 1 3
3 item1 a 3 4
4 item2 c 2 2
5 item1 c 3 2
6 item 3 8 1.5
7 item2 b 4 1
8 item1 d 7 0.5
9 richard 28
10 frank
11 item2 a 4 6
12 item1 b 2 5
13 item2 b 1 1
14 richard 6
15

The problem begins when I have to calculate the total
sales/salesman of a category of items (eg item1 a,b,c...
all fall under one category)

The items can appear randomly (but just once) under a
salesman

eg: "item1 a" can come after "item2 c" but i'll have to
show/agents sales of items1 x, 2x , 3x etc....

please help..

 

[Dave Peterson]

I'd modify the data slightly. I'd put the name of the salesman on each row.

I'm guessing that Richard's top row should have had blanks for Units and Rate.
And I'm not sure where the bottom Richard came from--and why it has a 6 there.

I'd insert a new column A. Then in A2, put this formula:

=IF(D2="",B2,A1)

And drag down.

I got this:

Name Product Units sold Rate
richard richard
richard item1 a 3 4
richard item2 c 2 2
richard item1 c 3 2
richard item 3 8 1.5
richard item2 b 4 1
richard item1 d 7 0.5
richard richard 27
frank frank
frank item2 a 4 6
frank item1 b 2 5
frank item2 b 1 1
frank frank 7

Now I'd convert column A to values (copy|paste special values).

Then apply data|filter|autofilter to column D.

Show only the blanks in that column and delete those visible rows.

Then you could manipulate it further--data|filter|autofilter, data|pivottable or
even data|subtotals.

 

[Peo Sjoblom]

Don't forget Richard III <g>

 

[Dave Peterson]

You are one strange Swede! <vvbg>

Don't forget Richard III <g>

--

Regards,

Peo Sjoblom

<<snipped>>