• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help with filtering on data

G

game_federer

New Member
Hi All,
I have uploaded a file and I need some help regarding the application of filters on my data.
In the U column you can see the values over which my filter needs to work.For finding out summation of a column I used SUMIFS which is working just fine. Now I have 2 questions:

1. How can I use those filter values over a column which is not numerical, say reco_all. I want the reco_all value that occurs the max amount of times using the filters given. (Also, these filters are dynamic as those values will change as the user might choose to change them.)

2. Also, what if I have 1000000 rows of similar data and I want just the top 10 rows according to the filters? Is there a way to get just the top 10 or 20 rows?

Thanks in advance for your help. Cheers.
 

Attachments

  • Filter.xlsx
    10 KB · Views: 2
Hi,
Thanks for the reply. I dont really want to do it using pivots. Is there a way around it?
The reason why I dont want to do it that way is because pivots created in Excel 2007 are a row below those created in 2010 which causes issues when clients use different versions.

Thanks in advance.
 
Hi:

An alternative will be to use advanced filter or VBA. Though I am not sure on the first point you have mentioned on your OP
1. How can I use those filter values over a column which is not numerical, say reco_all. I want the reco_all value that occurs the max amount of times using the filters given. (Also, these filters are dynamic as those values will change as the user might choose to change them.)
Click to expand...
How do you want to calculate the max amount for "reco_all".
Thanks
 
Hi,
So as you can see the reco_all is a string value(say UPSCALE,BUSINESS,3)

Now, what I want is to use the filters provided by the users to filter out all the data and give me the recommendation which occurs the maximum number of times.(In SUMIFS I was able to calculate for numerical data as you can see)

I hope I'm clear this time.

Also, can you help me out with the VBA or advanced filter part? Ill be grateful.

Thanks.
 
Hi:

Is this what you are looking for?

Formula on cell AH3

Thanks
 

Attachments

  • Filter.xlsx
    11.8 KB · Views: 8
Would like to know also if we can somehow extract top 10 rows out of the main data on to somewhere else for similar operations as done already but just on the top 10.

Thanks.
 
You must log in or register to reply here.
Back
Top