Finding Top Ten Values with Multiple Conditions

Hi,
May be a solution with pivot table
The field Model Line That Was Sold is filtered top 10 elements by sales
Note : Excel 2007 does not have the functionality to repeat all items label
Hope this helps
Best regards

Thanks for the response Jpr73

Is there any other options we could use here to get that format required or very similar besides Pivot Tables?

HI Pete_Uk

Thanks for the formula. Still having a few issues trying to get it to work. Can you please clarify the following points

Please dont forget I am very new!

1.) D5, WORKINGS!$J$1:$V$1,0)+1+9+64)&
Where do you get these numbers from 1+9+64?


2.) (B6,WORKINGS!I:I,0)&":W"&
Is the ":w" cell w in workings or is this something else?


3.) ("WORKINGS!J"&MATCH
Is the "J" in this above cell j or something else?


4.) Obviously the original example was a snippet of a much larger file. I have tried to work it out in my real file. This is what i come up with, maybe i have done something wrong and you could tell?
=IF(K225="","",IF(AND(K225=K224,J225=J224),INDEX('FD JAN-DEC RETAIL'!CE$1:CQ$1,MATCH(K225,INDIRECT("FD JAN-DEC RETAIL!"&CHAR(MATCH(L224,'FD JAN-DEC RETAIL'!$CE$1:$CQ$1,0)+1+9+64)&MATCH(J225,'FD JAN-DEC RETAIL'!CD:CD,0)&":CR"&MATCH(J225,'FD JAN-DEC RETAIL'!CD:CD,0)),0)+MATCH(L224,'FD JAN-DEC RETAIL'!$CE$1:$CQ$1,0)),INDEX('FD JAN-DEC RETAIL'!$CE$1:$CP$1,MATCH(K225,INDIRECT("FD JAN-DEC RETAIL!CE"&MATCH(J225,'FD JAN-DEC RETAIL'!CD:CD,0)&":CR"&MATCH(J225,'FD JAN-DEC RETAIL'!CD:CD,0)),0))))


Again anymore help would be much appreciated. This is driving me crazy!


I use 2007!


Regards

Hi Dave,

I've taken the points as you have asked them:

Originally Posted by DavidAndrew

1.) D5, WORKINGS!$J$1:$V$1,0)+1+9+64)&
Where do you get these numbers from 1+9+64?

Well, you have to consider a slightly wider view of that part of the formula, which is:

CHAR(MATCH(D5, WORKINGS!$J$1:$V$1,0)+1+9+64)

This is trying to find where D5 matches the range J1:V1 of the WORKINGS sheet, and we want the letter of the next column beyond the match. Suppose it matches with what is in J1 (i.e. returns the value 1) - we want to add 1 to this to get the next column, but we also need to add 9 because column J is actually the 10th column (MATCH returns the relative position in the range). To convert this result to a letter, we need to use the CHAR function and to realise that the letter A (column 1) has an ASCII code of 65, so we need to add 64 on. If I had just put + 74 then you would have been just as confused !!

Originally Posted by DavidAndrew

2.) (B6,WORKINGS!I:I,0)&":W"&
Is the ":w" cell w in workings or is this something else?

Yes, that is correct, as it will always be column W that we will want to consider.

Originally Posted by DavidAndrew

3.) ("WORKINGS!J"&MATCH
Is the "J" in this above cell j or something else?

Again, a more complete picture can be had by looking at the wider expression, i.e.:

"WORKINGS!J"&MATCH(B6,WORKINGS!I:I,0)

which forms part of a larger INDIRECT expression. We are trying to build up a range which will be from J-something to W-something, the something being given by the MATCH expression, which will find the row in column I of the WORKINGS sheet where B6 can be found.

Originally Posted by DavidAndrew

4.) Obviously the original example was a snippet of a much larger file. I have tried to work it out in my real file. This is what i come up with, maybe i have done something wrong and you could tell?

=IF(K225="","",IF(AND(K225=K224,J225=J224),INDEX('FD JAN-DEC RETAIL'!CE$1:CQ$1,MATCH(K225,INDIRECT("FD JAN-DEC RETAIL!"&CHAR(MATCH(L224,'FD JAN-DEC RETAIL'!$CE$1:$CQ$1,0)+1+9+64)&MATCH(J225,'FD JAN-DEC RETAIL'!CD:CD,0)&":CR"&MATCH(J225,'FD JAN-DEC RETAIL'!CD:CD,0)),0)+MATCH(L224,'FD JAN-DEC RETAIL'!$CE$1:$CQ$1,0)),INDEX('FD JAN-DEC RETAIL'!$CE$1:$CP$1,MATCH(K225,INDIRECT("FD JAN-DEC RETAIL!CE"&MATCH(J225,'FD JAN-DEC RETAIL'!CD:CD,0)&":CR"&MATCH(J225,'FD JAN-DEC RETAIL'!CD:CD,0)),0))))

If your larger file has the data in different columns then it is likely that the formula cannot be applied. For one thing, the use of the CHAR function to return a single character will restrict this to columns less than AA as the start column, but I see that in your formula you are wanting to go up to column CP. Without seeing your larger file, I can only suggest that you try to split it onto other sheets so that it follows a layout similar to what you have posted earlier - then it will be easier to apply that formula to the revised layout. Some solutions are not easily scalable up to much larger actual layouts.

Hope this helps.

Pete