• 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

Kaushik, you are absolutely right. Sorry. I misread what A10 was displaying, and was expecting that "out" would then appear at F3.


Narayan, you are correct. What we want is that if either of a player's highest or second-highest score was "not out", then the formula must display that score with an asterisk after it. If either of his or second-highest score was the same score, but one was not out and he was dismissed making the other, then the formula must display the not out score (with the asterisk) as being the higher of the two.


Example: A player's top four scores are 79*, 79, 61* and 61. His highest score should thus be returned as 79*, as this was not out, making it a "greater" score than the other innings of the same number runs but in which he was dismissed. The formulas must thus return 79* for his HS1, and 79 for his HS2. On the other hand, if his top four scores were 79, 61* and 61, then his HS1 should be 79 and his HS2 61*.


Kaushik has written a great formula that does this perfectly for the highest score, but we are struggling to replicate the same thing for the second-highest score. We have (i.e. he has) got it to work that the second-highest score is correctly displayed, but thus far it has either appeared without an asterisk when it should have one, or with an asterisk when it shouldn't.
 
Hi ,


Now things are clear. Let me recapitulate :


1. You have a list of values in a column , where some values are purely numeric , like 79 and 61 in your example , and where others have the asterisk appended , like the 79* and 61*. If you do not have a column of values like this , you have two columns , one having the numeric value , and the other having the status flag OUT or NOT OUT.


2. You want that in any ranking , the asterisked values should have higher priority than the ones without the asterisk , when their numeric value is the same.


Correct so far ?


The easiest way I can think of , is to use a helper column , where the asterisked values are replaced by the numeric value + a small enough value such as even 0.1 ; thus 79* would be 79.1


When retrieving the highest and second-highest scores , the NOT OUTs will naturally be ranked ahead of the OUTs.


Is this acceptable to you ?


Narayan
 
That sounds like it might work, Narayan. What do you think, Kaushik?


The link to the sample workbook appears earlier in this post if you want to experiment. However, by way of summary, we have purely numeric data in one column (representing the scores made by batsmen), while another column is

"How Out", which contains text-only entries such as "caught", "bowled", "not out", etc.


Will your approach of adding 0.1 to not out scores be converted to being displayed as the original score followed by an asterisk though?
 
Hi ,


Absolutely no problem.


Can you check out this file ? Download it and open it in Excel ; change the values , and see if the highest and second-highest scores are displayed correctly.


https://docs.google.com/open?id=0B0KMpuzr3MTVaElNMG1mbEdIbTA


Narayan
 
Hi Narayan.


That was incredibly, amazingly, awesome! Your formulas are so simple, yet they work completely perfectly. I can even understand exactly what they are doing! THANK YOU SO MUCH!


Any chance that you can apply your obvious ability to my other issue (I'm still waiting for a reply on this from my great ally Kaushik in this post):


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)".


I have uploaded an updated sample workbook (sample 3) via dropbox for you to look at:

https://www.dropbox.com/s/okv0fgdk5isuipt/Sample%203.xlsm
 
My dear friend Graemy,


Further to our discussion regarding the way I did with that macro concept, I have applied the same logic in your sample2 workbook at Summary Sheet in order to solve the * tagging part with highest and second highest score. And, ultimately it is now working perfectly fine.


At career summary sheet, if you change the value(Player's name) at "B3",macro will run automatically, hence your highest and second highest score at K3 and L3 will be changed and * will be appended to these values accordingly if the player is found to be not out.


[Note: I have copied the three cols (Player,Inngs,How Out) from "Indiv Performances" worksheet and place it at "BA col" at "career summary" worksheet.However, we can avoid doing this and can directly fetch the data from Indiv Performances worksheet only]


For example,

1) For the player Carl W Mellors, the value at K3 and L3 are not appended with *, as there is no not out case

2) For Russell C Adams, both the scores are are appended with * because, for his highest score (38 at innings col) he is found to be not out (at how out col) and same reason for second highest score(35) as well.

3) For Kemm, Norman A; both the scores are appended with * because of the same reason mentioned above(at example 2). But if you inspect Kem's scores, you will see there are two same second highest scores(zero). But for one zero he was out and for second zero he was caught, and we are getting "not out" for him at L1 of Career Summary sheet due to which L3 gets appended with *


You may want to test the outcome with some other players example as well. Hope we are done now.


Link for the file:

https://hotfile.com/dl/164003400/1584dad/Sample_2-1.xlsm.html


I have e-mailed you the same.


Looking forward to hearing from soon.


Regards,

Kaushik
 
Thanks Kaushik - I think our last posts crossed in cyberspace!


I have yet to learn the whole macro thing with Excel, and so will look at your example with great interest. Hopefully I can learn a lot from them!


I did find that Narayan's formulas worked beautifully as well though, so I'm not going to tinker with something that now works at long last! Thanks for all of your head scratching thought that you've put into solving this for me - it has made me an instant fan of this forum for sure!!
 
Narayan.....the true excel Ninja..


I have not much to talk about as Graemy has mentioned all the adjectives of your skills.


You are the master of excel and has tremendous ability to make intricate things so simple.


Thank you so much for your time Narayan and helping Graemy to solve this issue.


Best Regards,

Kaushik
 
@Graemy,


I absolutely agree with you. We started working on this to get a formula based approach to solve this, and the great Narayan come up with an outstanding but so simple formula and logic which, at least I, could not even imagine of...


So I also do suggest to follow Narayan's formula to get your work done.


I am sure Narayan will solve your other issue as well very quickly.


I am very happy that I have got a chance to work on this problem with you and Narayan, which indeed help me to learn a lot.


Thank you,

Kaushik
 
Trust me when I say this, Kaushik, the pleasure (and the benefits) were all mine!!


Are you signing off on this post now? I hope not!
 
Absolutely not dear.


Let me tell you the reason I replied you so late.


Today, actually I did not have any food in my home as my cook took an off suddenly. So I went outside to get some food.


I will be back after an hour's time from now, and will definitely chip in thereafter.


Kaushik
 
Hi Graeme ,


Sorry about the weekend delay. I just downloaded your file this morning ; can you please let me know what you need to have in C3 and D3 on the Career Summary tab.


I saw your tab Dropdown Lists Sources , and teams don't appear there. However , I assume that a list of teams can be put in there.


Given that these two lists are available on that tab , what is required ?


Will you want to see only one player's statistics at a time ?


If so , I think it should be possible by extending the range of the formulae on the Career Summary tab ; instead of B2:D3 , making it B2:D4 or even B2:D5 , provided the player's name is duplicated in the remaining cells in column B.


Narayan
 
Hi Narayan.


Thanks for your response. I actually felt very inspired yesterday by your and Kaushik's efforts in helping me with the career summary formulas, and ended up redesigning and radically extending the information that it contains. I must therefore upload the new version for you guys to look at / work on further.


In answer to your question though, what I still really need the Career Summary worksheet (now renamed Career Stats) to do, is to allow me to select not only a player (by using a single dropdown list action) to see his career stats, but also to choose which teams' stats that he represented should be included.


In other words, if the selected player has represented more than one club (several of them have played for a number of different clubs against Cape Town, and a few have played both for and against Cape Town as well), I should like to be able to choose the stats for his performances for which club should be included. This should also include the option "All", which will then automatically display the selected players stats for all games, regardless of who he was playing for.


At the moment, because my Team dropdown list is sourced from a data validation dropdown list (not sure why you say that the teams don't appear on the Dropdown Lists Sources worksheet, as they are listed in the range A34:A63 there), I can only select data for one club that the player represented at a time. This is limiting both in terms of giving a complete picture of a player's performances, as well as requiring me to remember offhand which clubs he has played for (so that I can select them from the dropdown).


In short, therefore, I would please like a different arrangement for my dropdown list in P1 of the Career Stats worksheet - one that allows multiple selections, including an "All" option - that will allow me to filter the selected player's stats (yes, only one player at a time is necessary) according to clubs played for; i.e. in the manner of a Pivot Table's dropdown Report Filter, or a VBA Combo / List Box. You would know best.


If you are realling feeling creative, it would also be great if you could find a way to combine the two separate player selection actions at present (I select Batting & Fielding stats via the dropdown at D1 on the Career Stats worksheet, and the Bowling stats via the Pivot Table Report Filter on the BB Figures Pivot worksheet)! I couldn't get the "10wM" and "BB M" formulas in the Career Stats worksheet to work, and Kaushik gave me this approach by creating a separate pivot table to do that. It's not a major isue by any means, as it is only two dropdown list selections, but it would be cool if I could select everything wth a single player dropdown list on the Career Stats sheet.


Thanks for your willingness to share your expertise with me! It's really, really appreciated!!
 
Hi Graemy,


I just quickly read your post now..


I am just about to go to my office and am bit in a hurry...


Reply you soon..


Thank you for your patience.

Kaushik
 
No problem.


Unfortunately I cannot upload the revised sample workbook from the office, so I'll do it tonight when I get home.


Thanks guys.
 
Hi Graeme ,


I don't think I can say that all that you want can be done using only formulae ; using VBA would make it easier to achieve. Is this acceptable to you ?


Narayan
 
Hi Graeme ,


I don't think I can say that all that you want can be done using only formulae ; using VBA would make it easier to achieve. Is this acceptable to you ?


Narayan
 
Narayan, I am basically looking for an elegant and highly effective solution that brings a smile to my face whenever I use it and a thrill of excitement to see it work perfectly!


If that requires VBA in addition to formulas, then I am fully 100% onboard with that approach. Just bear in mind though that I have had no exposure at all to VBA, so please don't expect me to have a clue about what's going on!
 
Hi Graeme ,


Most of VBA is plain English !


I am sure you will be fully in the picture once everything's done.


Just upload your file and give us some time.


Narayan
 
I have complete faith in you guys!


Keep an eye out for the dropbox link to the file, maybe around 19h00 or so (UTC+02:00).


Thanks!
 
Here is the link now:

https://www.dropbox.com/s/9zvkh199x4z5srf/Sample%204.xlsm


A slight correction to what I said earlier as well: the pivot table Report Filter on the BB Figures Pivot worksheet is not used to get the selected player's full bowling career stats, but only his "BB M" and "10wM" figures - everything else is drawn from the name selected on the dropdown list on the Career Stats worksheet (provided that one also remembers to change the Fielding Team name accordingly!)


Thanks.
 
<div class="img"><img title=" Karen Millen Sculptural Frill Short Dress Neutral " alt="Karen Millen Sculptural Frill Short Dress Neutral" src="http://www.karen-millen-km.co.uk/bmz_cache/5/59f751f4c4a599bf6434efdf5e039af6.image.220x342.jpg" width="220" height="342" /></div>

<div class="prod-info">Karen Millen Sculptural Frill Short Dress Neutral

<div class="text">Description:* Stretch satin one shoulder pencil dress with i...</div>


<p> An actual <span style="color: #ff6600"><span style="color: #ff6600">karen millen dresses</span></span>, additionally regarded as being a trumpet marriage ceremony get together gown, will begin higher than a neck holster under fairly around or even underneath the hips the way through which where it is completed finding a broad skirt.This particular peek karen millen sale is remarkable on the woman that offers accomplish it karen millen attire the undertake it the diy self-assurance together with A single another using would really like pertaining to obtaining appealing to be with her enormous evening.teffwen blogspot blog</p>


<p>Karen millen big event gown is likely to be most it's quite likely the most perceived as by ladies that should truly show your shapes.Normally, this kind of truly can be credited towards the crucial fact the reason is scenario uses the shapes belonging on the strategy coming from greatest in order to bottom.</p>


<p> </p>


<p> that is created being donned through the body <span style="color: #ff6600">Karen Millen sale</span> Colour Block Halter Neck Red Dress.Minibag refers to small sized dress within several different styles, desired of kids in manufacturers like XOXO, chinese language Laundry, Fossil, Guess and others.So whether you select up that new Prada at Bergdorf Goodman, the Fendi at Neiman Marcus, a fabulous new hobo in a neighborhood boutique, or scour the internet for just the right bag, the options are endless, regardless of how several you own.dress are fun, fashionable, and right here to stay.</p>

<p>Somehow, "Sex and the City" would not be the same if Carrie Bradshaw and her friends went about town wearing baggy t-shirts, mom jeans and flip-flops. No, "Sex and the City" is all about the clothes. Of course, Sarah Jessica Parker and the rest of the cast have become fashion icons because they have an army of stylists and a Mr. Big budget at their disposal. But you can still get the "Sex and the City" look for less, thanks to these tips from our panel of fashion school experts who are crazy for style - and bargains.Mix designer labels with discount pieces. Sarah Jessica Parker made it all right to mix high-end pieces with basics and flea market finds. So don't feel like you have to wear head to toe designer labels. Try a luxurious jacket with a simple tank top. Or pair a designer top with a $5 tutu! Mixing it up not only saves you money, it makes your look more interesting. And Carrie would be so proud.Hit the second hand stores. Carrie also transformed the image of vintage frocks, taking them out of the realm of "bohemian" into high-end fashion design. Again, one reason she made vintage work was because she frequently paired it with designer pieces. Also, the fit was impeccable, never sloppy; the key to buying from a second hand store is to get the garment tailored.Shop high-fashion mass retailers. The "Sex and the City" gals always seem to be ahead of the fashion curve, and so can you. Retail chains like H&M, Zara and Forever 21 are replicating high-end designer looks and getting them into stores mere weeks after their runway debuts, for prices that are a fraction of the fashion design labels.Look for affordable versions of the movie's clothes. The minute the "Sex and the City 2" trailer came out, fashionistas were going gaga over the clothes in it, particularly the white v-neck Halston Heritage jersey dress Sarah Jessica Parker wears. It's not exorbitantly priced at $325, but even more affordable facsimiles have cropped up because of demand, like XOXO's $59 version.Wow with accessories. As beautiful as the clothes are in "Sex and the City," the real traffic stoppers are the accessories. For example, Carrie's aforementioned white jersey dress in the movie trailer may be chic, but it's the rhinestone bib necklace she's wearing that really stands out, along with her gold aviator sunglasses and glittery clutch purse. This has to be one of the biggest money-saving secrets of fashion school experts: let the accessories create the "wow" factor.Replicating the "Sex and the City" look can be both fun and empowering, especially when you do it so affordably. </p>

<p>Sex and the City Style on a Budget - Fashion School Secrets For Getting the Look For Less</p>


<p> </p>


</div>

Related articles：

 
Hi Graeme ,


Your workbook seems to be complete ! Can you please specify what you are looking for ?


As I see it , your Career Stats tab has provisions for the following :


1. Batsman / Fielder name

2. Bowler Name

3. Batting Team Name

4. Fielding Team Name


I presume at present you are typing in the data into these cells ; what is the change you want ? Since you will be seeing only one player's statistics at a time , should the bowler name be the same as the batsman / fielder name ? What do you want to happen if any of these cells are left blank ?


What is supposed to happen if a player has played in more than 2 teams ?


I would think that it would be better if you have several rows ( at least up to 5 ) for data entry ; I don't think a player would have played for more than 5 teams.


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