If then Formula

[spaanstb]

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

 

[Don Guillett]

I would use filter>autofilter>filter for 3 and filter for starts with 5>copy
to other sheet>record a macro>clean it up.

 

[spaanstb]

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]

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.