I want both rows for salesrep A, etc. In reality there are about 100
rows and 8 sales people.


Mark.

On Aug 27, 11:26*am, Mark767 wrote:
I want both rows for salesrep A, etc. *In reality there are about 100
rows and 8 sales people.

Mark.

I should also point out that I am not just copying to a new sheet but
a new workbook.


Mark.

Mark,

You can extract multiple lines/and columns of data using an array formula like this (enter using
Ctrl-Shift-Enter):

=IF(COUNTIF('Data Sheet'!$A:$A,$A$1)=ROWS($A$1:A1),INDEX('Data Sheet'!A:A,LARGE(('Data
Sheet'!$A$1:$A$999=$A$1)*ROW('Data Sheet'!$A$1:$A$999),COUNTIF('Data
Sheet'!$A:$A,$A$1)-(ROWS($B$1:B1)-1))),"")

copied down and over, where A1 has the rep name, and the rep names are found in column A of "Data
Sheet"

HTH,
Bernie
MS Excel MVP


"Mark767" wrote in message
...
On Aug 27, 11:26 am, Mark767 wrote:
I want both rows for salesrep A, etc. In reality there are about 100
rows and 8 sales people.

Mark.

I should also point out that I am not just copying to a new sheet but
a new workbook.


Mark.

One final thing, the number of rows for each sales person will change
each month so the individual salesman workbooks will somehow need to
be cleared first before the new data is copied over.



Mark.

=IF(COUNTIF('Data Sheet'!$A:$A,$A$1)=ROWS($A$1:A1),INDEX('Data Sheet'!
A:A,LARGE(('Data
Sheet'!$A$1:$A$999=$A$1)*ROW('Data Sheet'!$A$1:$A$999),COUNTIF('Data
Sheet'!$A:$A,$A$1)-(ROWS($B$1:B1)-1))),"")

Thanks for the help, unfortunately I can't get it to work. Here are
my steps.

In the new workbook for the individual salesrep, I entered this
formula and changed A1 to be the sales rep name "John".
In my actual case the sales rep names on in a workbook called "Sales
Data" on a sheet called "Master". They start below
the title information in column E6 so I changed 'Data Sheet' to be
'Master' and got a #VALUE error. Also not sure how to take
care of rep names starting in E6.


Mark.

Bernie,

I wonder if you take another look at this. I am currently unable to
move forward.

thanks in advance.



Mark.