• 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 a way to summarise data from several worksheets into one place

Certainly Graeme...It would be my pleasure to assist you in your work and learn at the same time.


Actually, as of now, I have not think of doing this(K7 and R7) without having combobox in place. But as you have suggested, I will try to get the work done for R7 and K7 the way you have done for your other formulas and get back to you.


Wish you a wonderful evening ahead!!


Regards,

Kaushik
 
Can I post another request please?


Cells K3 and L3 of the Career Summary worksheet display the highest and second-highest scores made in an innings by a batsman. Can you perhaps tweak these formulas too so that they display an asterisk after the score if the innings finished as "not out"?


This should also be a sorting mechanism if a player's highest and second-highest scores are the same, but one of them was not out. In that case, the not out score (with the asterisk) should appear in K3, and the other in L3.


An example is Dean G van Blerk, who scored 79 twice. As one of those was not out and he was dismissed in the other, K3 should thus reflect 79* as his highest score, and L3 just 79 as his second-highest score.


Thanks (I'm getting spoiled here!)
 
Hi Graeme,


My apologies for replying you so late!!!


I tried to answer your query in the following way.


Let me explain the logic I applied:


Find the corresponding text value(Caught, not out, lbw etc.) from "How Out" col(of Indiv Performances sheet) of the max runs scored by a player (which your Dmax formula picks up)


If the value for the plyer(at B3) turns out to be "Not Out" then append the K3 value with an 'asterisk' sign


To do the same:


At Career Summary sheet, I placed your K3 formula (DMAX) at J1

At B5 I write "not out"

At H4, I write the following array formula to fetch the data from 'how out' col(Y) from Indiv Performances worksheet when player = B3 (of Career Summary sheet) and max runs = J1(of Career Summary sheet):

{=INDEX('Indiv Performances'!Y2:Y927,MATCH('Career Summary'!B3,IF(('Indiv Performances'!S2:S927='Career Summary'!J1),'Indiv Performances'!B2:B927),0))}


Then at K3 I write the following IF formula to format the J1 value with asterisk if H4="not out"


IF(AND($H$4=$B$5,$J$1<>0),$J$1&"*",$J$1)


I emailed you the workbook as well


Let me know if its done or I have missed doing anything


Looking forward to your reply


Best Regards,

Kaushik
 
That seems to work quite nicely, thank you. I have even managed to adapt this approach to do the same for a player's second-highest score in L3, should that also be a not out innings. My Career Summary workbook is starting to look very impressive!


Just one thing though. You include in your formula IF(AND$H$4=$B$5,$J$1<>0),$J$1&"*",$J$1) the section $J$1<>0. Why is that there, as for tail-end batsmen their highest or second-highest score could easily be 0*?


A case in point is a player such as Norman A Kemm, whose only played three innings - 2*, 0* and 0. His second-highest score is therefore 0*, but the formula in H4 picks up the dismissal for 0, not the "not out" innings. His second-highest score is thus reflected as 0, instead of 0*.
 
You are absolutely right Graeme.


I just thought you want the highest scores which are >0 (how stupid I am!!)


So you can get rid of IF-AND and just stick to simple IF formula:

=IF($H$4=$B$5,$J$1&"*",$J$1)


I am sorry as I unnecessarily get you into confusion.


Let me know in case you need any further help.


Kaushik
 
I did try that with the copied and adapted formula for L3, changing it to a plain IF formula, but obviously I'm doing something wrong because it isn't correcting itself to 0*.


The problem seems to be with the array formula though (which I can't figure out, being my Excel weakness), because it is returning "caught" for Kemm's innings of 0, instead of the "not out" innings.


Is there something in that formula that only works for identifying the highest score, not the second-highest score?


Thanks!
 
So I am confused now..


You want only K3's score to be appended with * ,not L3's right..(according to your previous post.


And our if formula is based on the logic that, if the player is found to be "not out" then append the score with * else leave as it is.


For Norman A Kemm, his highest score is 2 where he was not out. So at K3 we find 2*,but L3 is giving 0 (not 0*)...the reason is, there are tow 0s(zero) for this player, but in one case he was caught and another case he was not out. Formula is considering the first entry where he found to be caught...and hence we are getting only 0 at L3( not 0*).


Do you want L3 score to be appended with *?


Kaushik
 
Correct yes.


The not out asterisk applies equally to second-highest scores (L3) as it does to highest scores (K3). The exact same set of rules applies to both cells, with the exception that the highest score must appear in K3., and the second-highest in L3.


In addition, the same sorting principle applies to the second-highest scores too - if a player has two second-highest scores of the same number of runs, and one of them was not out, then the second-highest score displayed in L3 should be the one with the asterisk.


Example:

A player's top three scores are 79*, 61* and 61.

Cell K3 must show 79*, and cell L3 61*


Am I giving you a hard time???!
 
Hmmm......understood..


Let me have a cup of coffe and think how can we approach to this!


Thank you for your patience and co-operation.


Kaushik
 
Hi Graemy,


I was trying to solve your query at my own but I was not able to come up with the right formula.


I asked the same by using a dummy data in this forum and the great "LUKE M" immediately reply with the solution.Please see the link below:


http://chandoo.org/forums/topic/lookup-value-when-condition-is-met


Hope it solves your query now.


Really, there is lot of things to learn from this forum as we have such wonderful experts like Luke..


Kaushik
 
Thanks Kaushik.


Any chance you could convert this to our worksheet though - our scenario is a little more complicated, in as far as we also have to sift out the correct player's name as well (in your generic example, you used "A" as the player name throughout, so it wasn't an issue).


I appreciate all of your efforts very much!
 
Can I make one other request as well please (I know I'm pushing my luck)?


Is there any way to alter the data validation dropdown list setup that I have as C3 and D3 on the "Career Summary" worksheet? As it now stands I can only select one option at a time for both the "Team" and the "Competition", which makes it problematical to get the career figures for players across more than one competition, or who have played for more than one team.


I tried using a pivot table for the "Matches" worksheet, remove the data validation from ceels C3 and D3 and then link them instead to the pivot table's selected Report Filter (which gives the option of multiple selections). However, this doesn't work well with the DCOUNT and DSUM formulas used in the "Career Summary" worksheet, as they are looking for specific entries rather than "(Multiple Items)".


Any ideas?


Thanks!
 
Graemy,

This is regarding the last problem that we were trying to solve..


see the same post again where I rephrased the problem and Luke solved it


http://chandoo.org/forums/topic/lookup-value-when-condition-is-met


Try to use the same logic to get the "not out" case . If you face any challenge, please write back.


Regards,

kaushik
 
Hi Kaushik.


I did manage to get Luke M's formula adapted to the needs of my Career Summary worksheet. It looks like this:

=INDEX('Indiv Performances'!Y:Y,MAX(ROW(Indiv_Performances[How Out])*(Indiv_Performances[How Out]=B5)*(Indiv_Performances[Inngs]=LARGE(IF(Indiv_Performances[How Out]=B5,Indiv_Performances[Inngs]),2))))


where B5 contains the wording "not out" (without the inverted commas).


However, there is still problem in that his formula seems to be written in such a way that it guarantees that an asterisk be placed after the HS2 score in L3. I say this because this array formula always seems to return the result "not out". As a consequence, even when a player's second-highest score was a completed innings, it still gets the asterisk added in L3.


Unless I am missing something in my adaptation of the formula (which I probably am). I do wonder though, because if I change the example data range that you gave him in any way (adding more rows or changing the status in any row), then his formulas no longer work).
 
But it is working fine for me...


Do you change the range in the formula too while changing the data range in sheet?


Kaushik
 
Yes, of course.


I placed your data in exactly the same cell references as you mentioned in your post, and everything worked fine. Then, when I added further data (rows) beyond B5 and C5 (and altered the range in each formula accordingly), the formulas cease to function - the formula for the highest score no longer selected the MAX number, and the formula for the corresponding status didn't update either. Maybe I should email a screenshot to you (look for it on your spam folder)?


Anyway, when you say that they are working fine for you, is that after placing them in the Sample 2 workbook? Because my version of Luke M's formula for adding the "not out" asterisk to the HS2 score (as quoted in my above post) kept returning a "not out" formula result, no matter which player's record I selected. Did you get a different result with the same formula?


Thanks.
 
Dear Graemy,


Hope you are doing great.


I was wrong in my understanding but you were absolutely right.


Luke's formula does not give us the desired result as I failed to come up with the right logic while posting the query which Luke solved. I have already bothered Luke a lot and I do not want to bother him anymore(I do admire Luke a lot!!)


I have tried but still not able to solve your query(to get the right status--whether "not out" for the max score) with a direct formula based approach.


However, I have manged to solve it by writing a simple macro.


I will explain it to you in detail:

Our requirement is to know, IF there are two max scores for a player,THEN there is any "not out" case for that player for that max score or not.

To check the same, I write the following macro using the sample data:


Sub GetStatus()

Application.ScreenUpdating = False

Application.DisplayAlerts = False


Dim lstrow As Long

Dim counter As Integer

Dim i As Integer

Dim j As Integer

Dim k As Integer


lstrow = ThisWorkbook.Worksheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row

i = 1

j = 1

k = 1


For counter = 1 To 8

If Cells(i, 1).Value = Cells(1, 5).Value And Cells(j, 2).Value = Cells(1, 6).Value And Cells(k, 3).Value = Cells(1, 7).Value Then


Range("A10").Value = "not out"

Exit Sub

Else


Range("A10").Value = "no not out case found"

End If

i = i + 1

j = j + 1

k = k + 1


Next counter


Application.ScreenUpdating = True

Application.DisplayAlerts = True

End Sub


and then call the macro at worksheet_change event which will enable to get you the status (whether not out or not) whenever you make changes in the player's name at "E1"


I have uploaded the workbook in the following link with all explanation (please enable macro while opening the workbook)

https://hotfile.com/dl/163963724/2d529c7/Book1.xlsm.html


If you are fine with this approach we can apply the same in sample2 workbook.


I have emailed the same to you.


Thank you for your patience and co-operation.


Regards,

Kaushik
 
Hi Kaushik ,


Finally , I have understood what you wanted !


If I have understood you correctly , you want to find out the maximum score of a player , say A , and then check whether the maximum score was an OUT or a NOT OUT ; is this correct ?


If so , can you not use the following formula ?


=IFERROR(INDEX(B2:B5,MATCH(TRUE,MAX((A2:A5="A")*(B2:B5))=((A2:A5="A")*(C2:C5="Yes")*(B2:B5)),0)),"")


entered as an array formula , using CTRL SHIFT ENTER.


This formula gets an array of scores where the player is A ; it then returns the maximum value from this array of values ; now , it compares this maximum value with the array of scores where player is A and status is NOT OUT. If the maximum value figures in the second array , it means the maximum score of player A was a NOT OUT.


Is this reasoning correct ?


Narayan
 
Hi Narayan,


You are absolutely right and I am sorry that earlier I was not able to put my words correctly and get you into the confusion unnecessarily....apologies for that.


However, your formula gives me the maximum score (as you explained). Probably if I change the B2:B5 reference to C2:C5 , I should get the yes(not out) or no(out or other types) status.

at a7: {=IFERROR(INDEX(C2:C5,MATCH(TRUE,MAX((A2:A5="A")*(B2:B5))=((A2:A5="A")*(C2:C5="Yes")*(B2:B5)),0)),"")}

And again if C col is entirely filled with "no" for the two duplicate max values then I will get blank(as you have wrapped the formula with IFERROR)...which is also fine.


Then we can write the IF formula to append the max scores with * as follows:


If(formula cell= "",maxvalue,maxvalue&"*")...something like this.


So I think we have got a formula based approach for this. Thank you Narayan for your help. Actually I am in the very initial stage of learning excelVBA but I have been learning a lot from you people in this forum.


Can you please also help me in one more thing?


I was trying to create an user defined formula(UDF) for this( with the macro I have written) which I was unable to do(actually I was unable come up with the write syntax while creating the function); due to which I had to tackle that issue by calling the macro at worksheet_change event(you can see the file I have uploaded).


Can u plz help in this by generating an UDF with same macrologic I have written? I know it's very simple task for u.

I just want to know how to do it?


Regards,

Kaushik
 
Hi Kaushik ,


If you want to know the score itself , assuming that there were two maximum scores , one of them with an OUT status , and the other with a NOT OUT status , the embedded MATCH function :


=MATCH(TRUE,MAX((A2:A5="A")*(B2:B5))=((A2:A5="A")*(C2:C5="Yes")*(B2:B5)),0)


entered as an array formula , using CTRL SHIFT ENTER , will return the position of the maximum score within the list of scores , so that the correct score can be tagged with the asterisk.


Narayan
 
Hi Kaushik - I'm very pleased to see you still wrestling with this problem.


Your macro didn't seem to work when I downloaded the workbook though - changing E1 from "A" to "B" gave me B's highest score in F1, but G1 continued to display "not out". A10 also gave an incorrect setting. I have maile you a screenshot.


Just so I'm not missing something though, we are clear that your previous formulas work perfectly fine in terms of placing an aterisk after a player's highest score if that was not out? The problem we are grappling with is doing the same thing for his second-highest score.


Thanks again for your commitment - and thanks for getting involved as well, Narayan!
 
Hi Graemy,


When you change E1 value from "A" to "B", you will get the max value at F1(23) and status at "A10" will change(which will change to "no not out case found") not G1.

note: E1, F1, and G1 are reference cells I have included in the IF condition in the macro.


Got the point.


check it and let me know.


we will apply the same in second highest score.


looking forward to your reply


Kaushik
 
Hi ,


The confusion still persists !


As far as I know , in cricket , an asterisk after any score signifies that that score is a NOT OUT score. Do you want that the asterisk should be appended to only certain scores ? If so , what are these , the maximum or the top two or the top five or what ?


Can you specify ?


Retrieving the second-highest instead of the highest is done by using the LARGE(....,2) function instead of the MAX(....) function.


Narayan
 
Narayan,


According to Gramemy's requirement, he wants to append the highest score(or second highest score) with * if if

1)there are multiple highest scores for that player and in one case the player is not out

2) No multiple entries of highest score for the player and in that case the player was not out


So in one word, if corresponding cell of highest score is not out, the score shouldbe tagged with *.


Make sense?


Graemy might want to explain it more.


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