Combining Large with If function

[msharma864512]

I want to pull the top list on the basis of certain conditions

I am trying to return the top values on the basis of condition, In this case, "I2" should match with Column B
For E.g. if I select, Manufacturing, then it should return the top 20 values from column C wherever in Column B, its Manufacturing
Similarly if I change the filter in cell I2, it should return the top value of that selection only

 

[bosco_yip]

Try..............

1] Criteria in I1, add heading in I2 and J2

2] In I3 formula, copied across to J3 and all down for 20 rows :

=IFERROR(INDEX($A$1:$C$155,AGGREGATE(15,6,ROW($C$1:$C$155)/($B$1:$B$155=$I$1),ROWS($1:1)),MATCH(I$2,$A$1:$C$1,0)),"")

Regards
Bosco

 

[msharma864512]

Perfect Bosco, it was very helpful, it really helped me a lot and made my work a lot easier.

Thank you Once Again

 

[msharma864512]

B

Try..............

1] Criteria in I1, add heading in I2 and J2

2] In I3 formula, copied across to J3 and all down for 20 rows :

=IFERROR(INDEX($A$1:$C$155,AGGREGATE(15,6,ROW($C$1:$C$155)/($B$1:$B$155=$I$1),ROWS($1:1)),MATCH(I$2,$A$1:$C$1,0)),"")

Regards
Bosco

Bosco, Thank you very much for all your help on this.

I was hoping you could also help me in adding more criteria to the list to pull top data
I have added the details in spreadsheet.

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[msharma864512]

Thank You Very Narayan, You really are a saviour.

There are couple of more things I was looking at

Is it possible to add a text as "All" in I2 to K2,
The reason I am looking for is:
Score type will be consistent for all:
For e.g: I have selected filer as "Preserving", then it should automatically bring the top score from the entire list of C3 to E156, then as and when I select any other drop down from the list it applies the conditions accordingly.

In other words, If I apply filters on Score Type and Sector as Preserving and Manufacturing respectively, then it should bring the top preserving scores of all the manufacturing accounts, now if select any other drop down either from channel or segment, it should apply consitions accordingly.

 

[NARAYANK991]

Hi ,

It can be done , but the formulae will become quite lengthy.

Give me some time.

Narayan

 

[msharma864512]

Great Narayan,

You can take your time, there is no hurry

 

[Peter Bartholomew]

The attached uses named formulae to break the nested calculation into comprehensible parts. Thus 'filtered_scores'

= IF(
(Table1[Sector]=Sector) *
(Table1[Channel]=Channel) *
(Table1[Segment]=Segment),
CHOOSE( MATCH( Score_Type, ScoreTypeHeading, 0 ),
Table1[Score1], Table1[Score2], Table1[Score3]) )

and, based upon that, the 'top_scores' are

= LARGE(filtered_score,{1;2;3;4;5;6;7;8;9;10})

From there, the companies with the top scores are simply

= INDEX( Table1[Company Name], MATCH( top_scores, filtered_score, 0 ) )

 

[Peter Bartholomew]

As for the rider to you OP, the condition for a match becomes

IF( Sector="ALL", 1, IF(Table1[Sector]=Sector) )
or, less disruptive in terms of validation, go for blank
IF( Sector="", 1, IF(Table1[Sector]=Sector) )

 

[msharma864512]

Thank You Very much Peter for you tremendous help!

The formula to pull the desired list is really very complicated, however I still tried my hand to pull the desired result, but there seems to be some issue with that,

In many cases, it is pulling duplicate values too, my primary moto here is to pull the scores only, because if i have the scores, then i can just use the look up to pull the names.

There are a few things I am not able to understand:
1> Its only pulling 10 scores, however I have tried changing the large function in named range, and tried adding more numbers to it, but its only pulling duplicate names. To look make the list more comprehensive, lets assume that i want to pull all the scores which matches the desired conditions, not just top 10 or top 20 or so.

2> I am not able to understand how to apply the "All" to the drop down, which allows me to pull all the data set

Please provide your thoughts on the same.

 

[Peter Bartholomew]

I have tried changing the large function in named range, and tried adding more numbers to it

This should work provided the punctuation is correct.
In the attached I have replaced the hard-wired sequence with a sequence 'k' of length 'n' set by the user. This is based upon a helper field containing the record number.

How to apply the "All"

What I implemented was 'blank' (simply clear the filter field using Delete)

Because the formula 'filtered_score' started to get uncomfortably long, I introduced further Boolean arrays

Sector?: = N( IF(Sector="", TRUE, Table1[Sector]=Sector) )
Segment?: = N( IF( Segment="", TRUE, Table1[Segment]=Segment ) )
Channel?: = N( IF( Channel="", TRUE, Table1[Channel]=Channel ) )

to shorten the final formula. I hope this helps.

 

[bosco_yip]

Thank You Very Narayan, You really are a saviour.

There are couple of more things I was looking at

Is it possible to add a text as "All" in I2 to K2,
The reason I am looking for is:
Score type will be consistent for all:
For e.g: I have selected filer as "Preserving", then it should automatically bring the top score from the entire list of C3 to E156, then as and when I select any other drop down from the list it applies the conditions accordingly.

In other words, If I apply filters on Score Type and Sector as Preserving and Manufacturing respectively, then it should bring the top preserving scores of all the manufacturing accounts, now if select any other drop down either from channel or segment, it should apply consitions accordingly.

1] Added "All" to dropdown list of I2,J2 and K2

2] In I4, array formula (Ctrl+Shift+Enter) copied down :

=IFERROR(AGGREGATE(14,6,INDEX($C$3:$E$156,,MATCH($L$2,$C$1:$E$1,0))/IF($I$2="All",ISTEXT($B$3:$B$156),$B$3:$B$156=$I$2)/IF($J$2="All",ISNUMBER($F$3:$F$156),$F$3:$F$156=$J$2)/IF($K$2="All",ISTEXT($G$3:$G$156),$G$3:$G$156=$K$2),ROWS($1:1)),"")

3] J4, copied down :

=IF(I4="","",INDEX($A$3:$A$156,MATCH(I4,INDEX($C$3:$E$156,,MATCH($L$2,$C$1:$E$1,0)),0)))

Regards
Bosco

 

[msharma864512]

Tha

This should work provided the punctuation is correct.
In the attached I have replaced the hard-wired sequence with a sequence 'k' of length 'n' set by the user. This is based upon a helper field containing the record number.


What I implemented was 'blank' (simply clear the filter field using Delete)

Because the formula 'filtered_score' started to get uncomfortably long, I introduced further Boolean arrays

Sector?: = N( IF(Sector="", TRUE, Table1[Sector]=Sector) )
Segment?: = N( IF( Segment="", TRUE, Table1[Segment]=Segment ) )
Channel?: = N( IF( Channel="", TRUE, Table1[Channel]=Channel ) )

to shorten the final formula. I hope this helps.

Thank You Very much Peter, your formula is really amazing, and it is certainly helping me a lot.

However in output column its still only taking 36 entries (column N)
If I enter 154 in cell N8, it should return all of the entries in column N if I have not selected any other drop down.

 

[msharma864512]

T

1] Added "All" to dropdown list of I2,J2 and K2

2] In I4, array formula (Ctrl+Shift+Enter) copied down :

=IFERROR(AGGREGATE(14,6,INDEX($C$3:$E$156,,MATCH($L$2,$C$1:$E$1,0))/IF($I$2="All",ISTEXT($B$3:$B$156),$B$3:$B$156=$I$2)/IF($J$2="All",ISNUMBER($F$3:$F$156),$F$3:$F$156=$J$2)/IF($K$2="All",ISTEXT($G$3:$G$156),$G$3:$G$156=$K$2),ROWS($1:1)),"")

3] J4, copied down :

=IF(I4="","",INDEX($A$3:$A$156,MATCH(I4,INDEX($C$3:$E$156,,MATCH($L$2,$C$1:$E$1,0)),0)))

Regards
Bosco

Thank You very much Bosco, Your formula is great, thank you very very being a saviour.

 

[Peter Bartholomew]

The formula only gave 36 values because it is a multi-cell array formula and had been entered to output the first 36 values from the calculation (This is changed by selecting the entire range you wish to populate and committing the formula using Ctrl+Shift+Enter. Once that has been done the first time it is only necessary to edit the lead cell to change the formula and CSE will populate the entire range)

Note: I would dearly like to reverse the defaults and make the destruction of meaningful arrays involve additional steps

​

[I looked for the smiley with the horns and evil grin but he seems to have gone missing]

 

[bosco_yip]

1] Added "All" to dropdown list of I2,J2 and K2

2] In I4, array formula (Ctrl+Shift+Enter) copied down :

=IFERROR(AGGREGATE(14,6,INDEX($C$3:$E$156,,MATCH($L$2,$C$1:$E$1,0))/IF($I$2="All",ISTEXT($B$3:$B$156),$B$3:$B$156=$I$2)/IF($J$2="All",ISNUMBER($F$3:$F$156),$F$3:$F$156=$J$2)/IF($K$2="All",ISTEXT($G$3:$G$156),$G$3:$G$156=$K$2),ROWS($1:1)),"")

3] J4, copied down :

=IF(I4="","",INDEX($A$3:$A$156,MATCH(I4,INDEX($C$3:$E$156,,MATCH($L$2,$C$1:$E$1,0)),0)))

Regards
Bosco

A bit modified and shorter,

1] I4, array formula copied down :

=IFERROR(AGGREGATE(14,6,INDEX($C$3:$E$156,,MATCH($L$2,$C$1:$E$1,0))/IF($I$2="All",1,$B$3:$B$156=$I$2)/IF($J$2="All",1,$F$3:$F$156=$J$2)/IF($K$2="All",1,$G$3:$G$156=$K$2),ROWS($1:1)),"")

2] J4, formula copied down :

=IF(I4="","",INDEX($A$3:$A$156,MATCH(I4,INDEX($C$3:$E$156,,MATCH($L$2,$C$1:$E$1,0)),0)))

Regards
Bosco

 

[msharma864512]

The formula only gave 36 values because it is a multi-cell array formula and had been entered to output the first 36 values from the calculation (This is changed by selecting the entire range you wish to populate and committing the formula using Ctrl+Shift+Enter. Once that has been done the first time it is only necessary to edit the lead cell to change the formula and CSE will populate the entire range)

Note: I would dearly like to reverse the defaults and make the destruction of meaningful arrays involve additional steps

​

[I looked for the smiley with the horns and evil grin but he seems to have gone missing]

Thank You Very much Peter, It was great, and it really shows your high spirit towards helping others.

I just wanted to ask one more thing, as you can see that there are only 154 entries in the Table1. so the output are to the maximum limit of 154 only.

I have tried adding more entries to the table, but the same is not reflecting in the output column, I have even tried to reenter the lead formula to reflect the output.

If I am able to solve this one, all of my misery will certainly come to an end.

But all our help is commendable, and I really dont have any more words to thank you enough

 

[msharma864512]

A bit modified and shorter,

1] I4, array formula copied down :

=IFERROR(AGGREGATE(14,6,INDEX($C$3:$E$156,,MATCH($L$2,$C$1:$E$1,0))/IF($I$2="All",1,$B$3:$B$156=$I$2)/IF($J$2="All",1,$F$3:$F$156=$J$2)/IF($K$2="All",1,$G$3:$G$156=$K$2),ROWS($1:1)),"")

2] J4, formula copied down :

=IF(I4="","",INDEX($A$3:$A$156,MATCH(I4,INDEX($C$3:$E$156,,MATCH($L$2,$C$1:$E$1,0)),0)))

Regards
Bosco

Thats great Bosco, thank you very much

 

[NARAYANK991]

Thank You Very much Peter, It was great, and it really shows your high spirit towards helping others.

I just wanted to ask one more thing, as you can see that there are only 154 entries in the Table1. so the output are to the maximum limit of 154 only.

I have tried adding more entries to the table, but the same is not reflecting in the output column, I have even tried to reenter the lead formula to reflect the output.

Hi ,

Did you increase the value in Sheet1!$L$8 , from its present value of 154 ?

The output formulae in columns N and O will also need to be edited to include additional rows ; select the entire range N4:N157 , and then extend it to include as many rows as you want , say till row 200 , so that the range N4:N200 is selected ; press F2 and then press CTRL SHIFT ENTER , so that the formula is now entered over the entire range N4:N200.

Do the same for the formula in column O.

The value entered in Sheet1!$L$8 should be less than the number of data rows in the table.

Narayan

 

[msharma864512]

Hi ,

Did you increase the value in Sheet1!$L$8 , from its present value of 154 ?

The output formulae in columns N and O will also need to be edited to include additional rows ; select the entire range N4:N157 , and then extend it to include as many rows as you want , say till row 200 , so that the range N4:N200 is selected ; press F2 and then press CTRL SHIFT ENTER , so that the formula is now entered over the entire range N4:N200.

Do the same for the formula in column O.

The value entered in Sheet1!$L$8 should be less than the number of data rows in the table.

Narayan

Thank You Narayan, It worked
Thank u very much for all the help

 

[Peter Bartholomew]

@Narayan
Thank you for you support which appears to have established the successful communication of ideas between @msharma864512 and me! I have been out for the day but your intervention probably provided the best solution.

@MSHarma
I hope my way of working was not too unsettling (or even downright baffling) for you. There are other techniques which may be available to you that are more mainstream. For example, if you are going to sort and filter large datasets Power Query might offer an alternative approach. That too, could be something of a culture shock though!

 

[msharma864512]

@Narayan
Thank you for you support which appears to have established the successful communication of ideas between @msharma864512 and me! I have been out for the day but your intervention probably provided the best solution.

@MSHarma
I hope my way of working was not too unsettling (or even downright baffling) for you. There are other techniques which may be available to you that are more mainstream. For example, if you are going to sort and filter large datasets Power Query might offer an alternative approach. That too, could be something of a culture shock though!

Ofcourse not Peter,

I was overwhelmed with seeing so responses from more people like you and it certainly gave me more perspective of solving the complicated things.

 

[msharma864512]

Thats great Bosco, thank you very much

Hi Bosco,

The formula you created for me was really great,

I am looking for some change in formula.

Is it possible to select multiple entries from a single dropdown to pull the scores. For e.g. in cell I2, I should have the flexibility to select more than one entries at a time, and same is the case with other drop down.

I am looking for something like check box to select multiple entries.