filter help

[pete]

Hello,

I have columns representing company, company's product, and company
street address. all this information is in a row. i have about 800 rows
in my spreadsheet. i want to filter this spreadsheet so it contains
only products that are made by 10 companies or less. that is, i want to
be relatively selective in choosing products that are not made by a lot
of companies, just 10 or less.

how do you suggest i do this in Excel?

 

[Guest]

Probably your best bet would be to use the Data > Filter > AdvancedFilter......

Vaya con Dios,
Chuck, CABGx3

 

[pete]

hi,

i figured i would use Advanced Filter Criteria, but i'm not sure what
Excel syntax/functions are available for filtering a column of nominal
data based on the number of occurrences of a token.

for example, if i have a list of criteria (a column vector with
elements/tokens representing products)

Videogame
Joystick
PowerGlove

and a range of other products (800+), i want to get the row
representing the company, product, address only of those products that
are made by 10 companies or less. essentially, i want to study the
niche markets and products.

thanks,

pete

 

[CLR]

You could do a lot with just the Data > Filter > Autofilter........., but
the Advanced Filter should do everything you want. Check out the HELP file
and if you have questions, post back.

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

One thing you might try, it to CONCATENATE the Product and Company names into
a helper column, then Autofilter on that column for the "Botom
10".........maybe that would give you what you're after.......

Vaya con Dios,
Chuck, CABGx3

 

[pete]

hi CLR,

i don't understand your suggestion about CONCATENATE and then running
Auto Filter for bottom 10.

if i were doing this with a programming language, then i would write a
program that takes in a matrix as a parameter. this would be an n x 3
matrix. i would then get the second column vector (representing
products) and iterate through it. i would check whether the count of
the i-th element in the vector is greater or equal to 8.

i guess i can try to invest time for trying to do this in VB but i'd
rather not.

the Excel Help page for Advanced Filter is not too helpful with respect
to what i want to accomplish

many thanks,

pete

 

[Guest]

Sorry Pete......in re-looking at the problem, I kinda missed the point. Try
here for more-better Advanced Filter help.....

http://www.contextures.com/xladvfilter01.html

And if that don't get it for you, then post back here with some more
details..........

hth
Vaya con Dios,
Chuck, CABGx3

 

[pete]

hi CLR,

i don't understand your suggestion about CONCATENATE and then running
Auto Filter for bottom 10.

if i were doing this with a programming language, then i would write a
program that takes in a matrix as a parameter. this would be an n x 3
matrix. i would then get the second column vector (representing
products) and iterate through it. i would check whether the count of
the i-th element in the vector is greater or equal to 8.

i guess i can try to invest time for trying to do this in VB but i'd
rather not.

the Excel Help page for Advanced Filter is not too helpful with respect
to what i want to accomplish

many thanks,

pete

 

[pete]

hi CLR,

i don't understand your suggestion about CONCATENATE and then running
Auto Filter for bottom 10.

if i were doing this with a programming language, then i would write a
program that takes in a matrix as a parameter. this would be an n x 3
matrix. i would then get the second column vector (representing
products) and iterate through it. i would check whether the count of
the i-th element in the vector is greater or equal to 8.

i guess i can try to invest time for trying to do this in VB but i'd
rather not.

the Excel Help page for Advanced Filter is not too helpful with respect
to what i want to accomplish

many thanks,

pete