Finding Top Ten Values with Multiple Conditions

**DavidAndrew** · 01-27-2012, 10:01 PM

Hi
I have attached an example file including a "before" and "after" result

Looking to find what Salesperson sold what product in Top Ten Format

To find "Products" formula (with ctrl shift enter)
=IF(N(C6),INDEX(WORKINGS!$I$5:$I$18, SMALL(IF(WORKINGS!$J$5:$V$18=C6,ROW(WORKINGS!$J$5:$V$18)-ROW(WORKINGS!$I$5)+1),COUNTIF(C6:C$6,C6))),"")

To find "Amount of Sales" formula
=IF(ROWS(C6:C$6)>F$7,"",LARGE(WORKINGS!$J$5:$V$18,ROWS(C6:C$6)))

Finding which Salesperson sold how many formula
---This is where I am stuck, i would love a formula please!

I am new!

I need this format for work

I use excel 2007

I need to take into account "ties"

Thanks in advance!

Regards

**jpr73** · 01-28-2012, 04:24 AM

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

**DavidAndrew** · 02-23-2012, 07:39 PM

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?

**Pete_UK** · 02-24-2012, 07:50 AM

You can put this formula in D6:

=IF(C6="","",IF(AND(C6=C5,B6=B5),INDEX(WORKINGS!J$1:V$1,MATCH(C6,INDIRECT("WORKINGS!"&CHAR(MATCH(D5, WORKINGS!$J$1:$V$1,0)+1+9+64)&MATCH(B6,WORKINGS!I:I,0)&":W"&MATCH(B6,WORKINGS!I:I,0)),0)+MATCH(D5,WO RKINGS!$J$1:$V$1,0)),INDEX(WORKINGS!J$1:V$1,MATCH(C6,INDIRECT("WORKINGS!J"&MATCH(B6,WORKINGS!I:I,0)& ":W"&MATCH(B6,WORKINGS!I:I,0)),0))))

and then copy it down to D20. It takes account of ties and so gives you the correct names as in the attached example.

Hope this helps.

Pete

EDIT: It looks to me like a space has been inserted in the 3rd WORKINGS on the second line when viewing the post, although it is not there when I view the post in edit mode - something to watch out for if you copy/paste from here.

**DavidAndrew** · 02-27-2012, 07:04 PM

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

**Pete_UK** · 02-28-2012, 06:52 PM

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