Advanced Filter? Can I?

[sherry]

i am in a mess. my client send me a report in the
following format:

A B C D
1 SALESMAN_1
2 PRODUCT1 DATA DATA DATA
3 PRODUCT3 DATA DATA DATA
4 PRODUCT5 DATA DATA DATA
4 SALESMAN_1 TOTAL TOTAL TOTAL
5 SALESMAN_2
..
..
9 SALESMAN_2
10PRODUCT3 DATA DATA DATA
..
..

this sheet is very very long and i manage just 5 salesmen.
Lets say 5 9 and 50 etc. What is the fastest way to get
just the data I want:

I need:

SALESMAN_5
PRODUCT1 DATA DATA DATA
PRODUCT3 DATA DATA DATA
PRODUCT5 DATA DATA DATA
SALESMAN_5 TOTAL TOTAL TOTAL
SALESMAN_9
PRODUCT2 DATA DATA DATA
PRODUCT5 DATA DATA DATA
SALESMAN_9 TOTAL TOTAL TOTAL


Please help!

 

[dcronje]

I would use auto outline, Data:Group and Outline:auto outline

then press the 2 to the left of "A1" which will supress the data and
show only the saleman and the salesmen total. This may be what you are
looking for.

 

[Debra Dalgleish]

Add a row of headings at the top of the table
A B C D
e.g. Product Jan Feb Mar

To calculate the salesman for each row --
Insert a blank column to the left of column A
Add a heading to this column -- Salesman
In cell As, enter the formula:
=IF(LEFT(B2,8)<>"Salesman",A1,B2)
Copy the formula down to the last row of data

In a blank column, or on a different sheet, list the salesmen
you need in your report
Select the cells in this list
Click in the Name Box, to the left of the formula bar
Type a name for the list, e.g. MySalesmen
Press the Enter key

In the main table, add a new column, with the heading 'Report'
In row 2 of the new column, enter the following formula:
=COUNTIF(MySalesmen,A2)
Copy the formula down to the last row of data

To report on your salesmen --
Select a cell in the table, and choose Data>Filter>Autofilter
From the dropdown in the Report column, choose '1'