I have a large amount of data I want summarized without having any blank
rows. The data looks like the following (only thousands of rows of different
numbers):

A B
3 123
3 567
3 578
4 746
5 645

Column A represents a sales person # and Column B represents a part number.
I want a summay on a different sheet that shows a range of part numbers by
sales person. One section will show part numbers starting with the number 5,
another section starting with 6, etc. There is a lot of formatting on the
other worksheet so I can't use a filter and I don't want any blank rows. For
example I would like sales person #3 to display as follows for part number
starting with 5 only. Is there a formula I could use to pull in only part
numbers starting with 5 for sales person #3 that does not show any blanks?

A B
3 567
3 578

I would use filterautofilterfilter for 3 and filter for starts with 5copy
to other sheetrecord a macroclean it up.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"spaanstb" wrote in message
...
I have a large amount of data I want summarized without having any blank
rows. The data looks like the following (only thousands of rows of
different
numbers):

A B
3 123
3 567
3 578
4 746
5 645

Column A represents a sales person # and Column B represents a part
number.
I want a summay on a different sheet that shows a range of part numbers by
sales person. One section will show part numbers starting with the number
5,
another section starting with 6, etc. There is a lot of formatting on the
other worksheet so I can't use a filter and I don't want any blank rows.
For
example I would like sales person #3 to display as follows for part number
starting with 5 only. Is there a formula I could use to pull in only part
numbers starting with 5 for sales person #3 that does not show any blanks?

A B
3 567
3 578

I'm trying to avoid the filter. The table I am pulling the data into has a
table for part number 1, 2, 3, etc... I want to keep the table that the data
pulls into the same so that each month I only have to replace the raw data
and the formulas automatically pull in the data I want. That is why I want a
formula that automatically pulls in the different part numbers for different
sales persons without having to take the time to filter and all that extra
work. I am trying to avoid macros since I don't have much experience with
them, and a simple formula would really do the trick. Any suggestions?

"Don Guillett" wrote:

I would use filterautofilterfilter for 3 and filter for starts with 5copy
to other sheetrecord a macroclean it up.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"spaanstb" wrote in message
...
I have a large amount of data I want summarized without having any blank
rows. The data looks like the following (only thousands of rows of
different
numbers):

A B
3 123
3 567
3 578
4 746
5 645

Column A represents a sales person # and Column B represents a part
number.
I want a summay on a different sheet that shows a range of part numbers by
sales person. One section will show part numbers starting with the number
5,
another section starting with 6, etc. There is a lot of formatting on the
other worksheet so I can't use a filter and I don't want any blank rows.
For
example I would like sales person #3 to display as follows for part number
starting with 5 only. Is there a formula I could use to pull in only part
numbers starting with 5 for sales person #3 that does not show any blanks?

A B
3 567
3 578



.

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"spaanstb" wrote in message
...
I'm trying to avoid the filter. The table I am pulling the data into has
a
table for part number 1, 2, 3, etc... I want to keep the table that the
data
pulls into the same so that each month I only have to replace the raw data
and the formulas automatically pull in the data I want. That is why I
want a
formula that automatically pulls in the different part numbers for
different
sales persons without having to take the time to filter and all that extra
work. I am trying to avoid macros since I don't have much experience with
them, and a simple formula would really do the trick. Any suggestions?

"Don Guillett" wrote:

I would use filterautofilterfilter for 3 and filter for starts with
5copy
to other sheetrecord a macroclean it up.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"spaanstb" wrote in message
...
I have a large amount of data I want summarized without having any blank
rows. The data looks like the following (only thousands of rows of
different
numbers):

A B
3 123
3 567
3 578
4 746
5 645

Column A represents a sales person # and Column B represents a part
number.
I want a summay on a different sheet that shows a range of part numbers
by
sales person. One section will show part numbers starting with the
number
5,
another section starting with 6, etc. There is a lot of formatting on
the
other worksheet so I can't use a filter and I don't want any blank
rows.
For
example I would like sales person #3 to display as follows for part
number
starting with 5 only. Is there a formula I could use to pull in only
part
numbers starting with 5 for sales person #3 that does not show any
blanks?

A B
3 567
3 578



.