1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Combining Large with If function

Discussion in 'Ask an Excel Question' started by msharma864512, May 24, 2018.

  1. msharma864512

    msharma864512 Member

    Messages:
    101
    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

    Attached Files:

  2. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,909
    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

    Attached Files:

    Thomas Kuriakose likes this.
  3. msharma864512

    msharma864512 Member

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

    Thank you Once Again
  4. msharma864512

    msharma864512 Member

    Messages:
    101
    B
    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.

    Attached Files:

  5. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    See if this is OK.

    Narayan

    Attached Files:

  6. msharma864512

    msharma864512 Member

    Messages:
    101
    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.
  7. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

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

    Give me some time.

    Narayan
  8. msharma864512

    msharma864512 Member

    Messages:
    101
    Great Narayan,

    You can take your time, there is no hurry
  9. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    381
    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 ) )

    Attached Files:

    Thomas Kuriakose likes this.
  10. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    381
    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) )
  11. msharma864512

    msharma864512 Member

    Messages:
    101
    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.
  12. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    381
    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.

    Attached Files:

  13. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,909
    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

    Attached Files:

  14. msharma864512

    msharma864512 Member

    Messages:
    101
    Tha
    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.
  15. msharma864512

    msharma864512 Member

    Messages:
    101
    T
    Thank You very much Bosco, Your formula is great, thank you very very being a saviour.
  16. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    381
    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
    :DD
    [I looked for the smiley with the horns and evil grin but he seems to have gone missing]

    Attached Files:

  17. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,909
    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

    Attached Files:

  18. msharma864512

    msharma864512 Member

    Messages:
    101
    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
  19. msharma864512

    msharma864512 Member

    Messages:
    101
    Thats great Bosco, thank you very much
  20. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    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
  21. msharma864512

    msharma864512 Member

    Messages:
    101
    Thank You Narayan, It worked
    Thank u very much for all the help
  22. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    381
    @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!
    NARAYANK991 likes this.
  23. msharma864512

    msharma864512 Member

    Messages:
    101
    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.
    Peter Bartholomew likes this.

Share This Page