• 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

Grumpy88

Grumpy88

Member
Hi.


I have created a fairly large Excel "database" that consists of several worksheets, each containing a different type of data about the same people. There is also a fair degree of overlap between the worksheets due to generic info that appears on all of the worksheets (i.e. the same columns containing the same info), with the golden thread running between all of the worksheets being the same people's names.


My present set-up is that I use data validation dropdown lists to enter the generic info on each worksheet, and enter the data specific to each individual worksheet's purpose manually on each sheet. All of the data on each worksheet is linked to the same names (although not every name appears always appears on every worksheet). I then have a separate pivot table for each worksheet, to summarise that worksheet's data according to name or any of the other generic info.


This method works, but is probably not the most efficient (either in terms of entering the data initially or summarising it afterwards). I can live with the data entering method (although using one form as a single point of entry that would automatically populate all of the necessary columns on the various worksheets with just a single data entry action would obviously be far better), but ideally what I am really interested in now is to have a single data summary display that

would show me all of the data from each worksheet for a selected person in one place - rather than having to use three or four different pivot tables in turn to obtain this data progressively.


My idea was thus to open a new worksheet that would display the overall summarised data from each worksheet in one place for a given selected person. I thus created a data validation dropdown list on this new worksheet to serve as the tool for selecting the person whose data I want to view, and then added a number of other columns next to that, each designed to display a specific data type obtained from one of the other worksheets.


Therein lies the problem for me though, as I've been unable to find a formula for these columns that will look for the selected name in all of the other worksheets, identify the relevant data for that person, and sum / count the data as a summary in the appropriate column on the new overall summarised data worksheet. I thought that COUNTIFS might work, with the criterion being the selected person's name, but I don't know how to establish the link between the name on the overall summarised data worksheet and the same name on each of the other worksheets.


Any ideas please? Thanks!
 
Hi Grumpy88


Welcome to the Forum.


I hope you have gone through the first Three Posts in Green on Forum Page. Since you have posted a long description of your problem it always helps to post a sample workbook in order to visualize the data and understand the output/result required. Please post a sample workbook. follow this post to do the same: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi shailyog.


Unfortunately I'm using my work's network, which has blocks built into the system to prevent access to online storage sites. I thus cannot access any of the websites listed in your link.


Can I perhaps email the sample worksheet to you, so that you can perhaps post it for me please (it's less than 750KB in size)?


Thanks!
 
Hi kaushik03.


Did you get the attached sample worksheet? Are you going to link it for me please?


Thanks very much.
 
Okay.


I've managed to get formulas to work for all but two of the cells in the "Career Summary" worksheet - K7 and R7 (see dropbox link to spreadsheet at bottom). Can somebody please help me with formulas for those two cells please?


Cell K7 must find all rows in the "Indiv Performances" worksheet that match the name selected in B3 of the "Career Summary" worksheet. It must then compare all the matches in which that player bowled (each match has its own ID, in column E of the "Indiv Performances" worksheet), pick up in which indivdual match that player took the most wickets (which will be one or two entries per match, as indicated in column AH of the "Indiv Performances" worksheet), and return the highest number. In addition, the corresponding number of runs conceded (as in column AG) should that number in the same row(s) as those of the most wickets must be returned on the other side of the hyphen in cell K7, and should the highest number of wickets taken in a match occur more than once, then the instance with the least number of corresponding runs conceded must be displayed here.


Cell R7 must count the wickets taken by the player in each individual match, and return the number of times that ten or more wickets were taken in the same match.


Anyone fancy their ability?


Thanks!


https://www.dropbox.com/s/bstmt4wgk4euj12/Sample%202.xlsm
 
Grumpy88


I am extremly sorry as your email went into my spam folder due to which I could not check your email at first place.


It is good to hear that you have managed to get the formulas for your most of the work.


I have answered your second(R7) question as follows:


Run a pivot to get the number of wickets taken by each players in each match(in Invd Wickt sheet)

Place a combobox(form control) to get the list of all players(in Career summary Sheet)


Write index formula to fetch the name in other cell (at B15 in 'career summary' sheet)


Create named range for report filter name(B1 at Invd Wickt) and Indexed name (at B15' of Career Summary)


Link "R7" of 'career summary' to "E1" (where we have calculated the number of times more that 2 wickets taken by each player) of Invd Wickt


[Please note that I have set the criteria of 2 instead of 10 because I have not found any cases (afetr random search) where oany player has taken more than 10 wickets in a single match. You can change the criteria to your need by making changes at D1 of Invd Wickt]

[For example, I could see that Carl W Mellors has got more that 2 wickets for 7 times]

Run a simple macro and assign to the form control button


I have emailed the workbook for your reference


I will work on your "K7" part and get back to you soon.


Regards,

Kaushik
 
Dear Grumpy88,


I have hard time in understanding your first point (K7).


Ok let me ask:


Assume the following scenario:

Player "A"

Match1 2 wkts

Match2 5 wkts

Match3 10 wickts

Match 4 3 wkts


So the formula should return 10(according to your statement:"It must then compare all the matches in which that player bowled (each match has its own ID, in column E of the "Indiv Performances" worksheet), pick up in which indivdual match that player took the most wickets (which will be one or two entries per match, as indicated in column AH of the "Indiv Performances" worksheet), and return the highest number")


am I correct?


Kaushik
 
Hi Kaushik.


Thanks very much for your efforts to help me out!


Your formula for R7 is not working correctly though. There are in fact two instances of bowlers taking ten or more wickets in a match - Nick Preston took 6-22 in the first innings and 4-5 in the second innings of Match #2, giving him match figures of 10-27, and Donovan G Holman took 6-75 in the first innings and 4-64 in the second innings of Match #14, giving him match figures of 10-139. Selecting either of these two players' names in cell B3 of the "Career Summary" worksheet should thus indicate a "1" in cell R7.


I understand that your formula in R7 was written to pick up two rather then ten wickets, but I'm not sure how it arrived at the value of "7" for Carl W Mellors. A check on his data on the "Indiv Bowling Pivt Table" worksheet will indicate that as a bowler he only ever took one three-wicket haul, one two-wicket haul and once just a single wicket.


Essentially what the formula in R7 must do is the following:

- Find all rows in the "Indiv Performances" worksheet which have, in column AC, the same name that was selected in cell B3 of the "Career Summary" worksheet.


- In all such identified rows, it must then add the data in column AH that share the same entry in column E (there will be either one or two rows per separate column E entry).


- It must then indicate the number of times (i.e. per separate column E entry) that this sum totalled ten or more.


Perhaps an example will help: Say in Match #5 (the column E entry in the "Indiv Performances" worksheet) a player takes 5-57 (i.e. a "5" in column AH and a "57" in column AG) in one innings (row), and 6-18 in the other.


The formula should thus detect that these two rows represent returns in the same match (they share the same entry in column E), so it should add the two column AH entries and check whether they total ten or more. In this case they did (5+6=11), so cell R7 of the "Career Summary" worksheet should dislay "1" - indicating that this player has taken ten or more wickets in a single match once in his career. Should he do it again in a subsequent match, cell R7 should then return the value "2", etc.


The formula that I need for cell K7 is unfortunately even more complicated. It needs to follow the same steps described above for the formula in R7, but then do the following as well:


- It must then return the highest value of the column AH sum as the first part of its display, followed by a hyphen.


- The second part of the display, after the hyphen, must display the summed totals of column AG for the same row(s) selected.


- In addition, where there is more than instance of the same result being returned re the highest number of wickets taken in a match (i.e. the column AH sum returned above), then the second part of the display must return the lowest value of the sum of the column AG row entries concerned.


Continuing the above example, therefore, cell K7 of the "Career Summary" worksheet should firstly display "11". In addition, it must then place a hyphen after the "11", and then display the sum of the corresponding column AG entries thereafter (i.e. "75"). This indicates that the best bowling performance by that player in a match was "11-75", i.e. 11 wickets taken for 75 runs conceded.


However, if the player in a later match again took eleven wickets (for example 4-16 in one row, and 7-35 in another, again both sharing the same entry in column E for the player concerned), then the formula in K7 must obviously do the same calculations again, but realise that the return of "11-51" in the second match is better than the "11-75" first achieved (he only conceded 51 runs instead of 75 runs the second time around, for the same number of wickets taken). The formula in K7 must the change from "11-75" to "11-51", to reflect his career-best bowling performance in a full match.


Would you be able to conjure this up?
 
For "R7":


At "Invd Wickt" worksheet, change the report filter parameter from player to bowler; and instead of taking count of wickets, change it ot sum....now change the drop down option(Career Summary)....for Nick Preston and Donovan G Holman the R7 should return 1 for both the cases.hope this is what you want.


Try and let me know.


Kaushik
 
Hi ,


This is incidental , but can you check ?


When a filter is done on Carl Mellors on the Matches tab , there are 15 entries , but the Career Summary shows 14.


Narayan
 
NARAYANK991


The difference is probably brought about by the Team dropdown list - Carl Mellors played 14 games for Cape Town and one against them (for Kuils River), making 15 in total.
 
Kaushik


That did work for R7, thanks.


I'm not sure about the dropdown on the Career Summary worksheet though, because it only seems to control what is displayed in that one cell R7. Is there not a way that selectinbg a player in B3 will auto update all of the Career Summary cells, including R7, without having to resort to a second dropdown list?


Is there any hope for a formula for K7?


Thanks for all of your time and effort thus far!
 
Glad that it works.


You can link B3 with B15 right. Whenever you cange the drop down selection, your B3 will be changed right.


Just an advice, if suits to your need.


I will definately try to provide a solution for K7 to you...


Kaushik
 
Actually, I am really stucked with a work in my office now...Give some hrs time. I will get back to you.


Regards,

Kaushik
 
Hi Grumpy88,


If I look at the Nick Preston, I coiuld see the following:


Row Labels Sum of Runs Sum of Wkts

2 27 10

12 2 1

16 48 5


His best figure seems to be 10-27 in match 2.


I n a word you want the best bowling figure as maximum number of wickets taken and lowest runs conceded.


Before we proceed further, let me know if my understanding is correct.


Regards,

Kaushik
 
Hi Grumpy88,


Your formula for K7 is solved.


In Career Summary sheet, change the name from "drop down" to update "K7".


The logic I have applied is clearly mentioned in "Best Bowl Fig" worksheet( I have created).


I have applied Min-IF array formula to get your work done.


I have emailed you the workbook. Please check it out.


For your reference, your entire work( whatever the fomula you have written) can be done in the way I have done (using that combobox for names, running pivot, use that simple macro to link pivot report filter name and indexed name, linking with formula etc.)for K7 and R7.


However, please check if your K7 and R7 part are working properly.


Regards,

Kaushik
 
You are awesome, Kaushik!! Thanks very much for your dedication to solving this for me.


I will study what you did and report back if everything is working as it should.
 
Thank you for your kind words...


Let me know if you need any further help...will be glad to help u...


Anyways, May I know where are u from?


Kaushik
 
Kaushik, I'm from Cape Town, South Africa. I'm the scorer / statistician for the First XI of my cricket club, Cape Town Cricket Club, and am putting this spreadsheet together to help me generate all the stats and records I need for my season review booklet that I compile after every season.


I've had a look at what you've done for K7, and as I understand it the process was as follows (correct me where I go wrong please):


1. Create a new pivot table for the Indiv Performances worksheet, that per individual bowler summarises his wickets taken and runs conceded per separate match number (does doing it this way automatically find the rows applicable to the bowler in question and then add together all the runs and wickets per match? Seems too simple!)


2. Create a formula in a new cell that picks up the highest number of wickets taken for a single match number from the pivot table.


3. Create an array formula in another new cell, which finds the fewest runs conceded from the appropriate column and row in the pivot table that matches the highest number of wickets.


4. Create a third formula in another new cell that combines the results of the above-mentioned two formulas. This third formulaccould just as well be in cell K7 of the Career Summary itself.


Seems very organised and logical. Unfortunately I have never quite managed to wrap my head around the concept of array formulas and what makes them apparently so much better and more powerful than regular formulas. As such, I don't reall understand how your array formula in F3 works. Could you perhaps explain it to me in layman's terms please?


Thanks so much again for all of your help!
 
Hey... that is excellent the kind of work you do. I would have loved to do such work if I could as 'CRICKET' is my favourite.......game.


So first question..is it working for you perfectly right?


And yes, your understanding about the process is perfectly correct.


Regarding the logic behind "Array Formula" I would request you to visit the following website:

http://www.cpearson.com/excel/ArrayFormulas.aspx


Moreover Chandoo has written some awesome articles on array formulas which you can see at the link below:

http://chandoo.org/wp/tag/array-formulas/


Now, regarding our formula(Min-IF array), I will try to explain it as simple as I can (I will not go by excel specific jargon rather in simple english).


Taking the gray sahded area in "Best Bowl Fig" worksheet as an example:


The array formula({=MIN(IF($F$19:$F$22=H19,$E$19:$E$22)}) at "K20" evaluates the condition by comparing the values one by one between "E19:E22" and "F19:F22".


Our condition is, "we want to get the minimum value from "E19:E22" when the corresponding cell(of E19 or E20 or E21 or E22) of "F19:F22" is 10(value at H19) .


Now lets talk about the part "IF($F$19:$F$22=$H$19,$E$19:$E$22)" first:


If you write this formula in some other cell and press F9, you will see excel will show the values with black shaded color as follows:


={27;FALSE;FALSE;25}

That means we have the condition met at following combinations:


For E19 and F19 the array formula finds the condition as true (because F19 = 10)

For E20 and F20 the array formula finds the condition as not true (because F20 <> 10)

For E21 and F21 the array formula finds the condition as not true (because F21 <> 10)

For E22 and F22 the array formula finds the condition as true (because F22 = 10)


Now, once we punch this formula with MIN, it gives the minimum value of two conditions met between E19XF19 and E22XF22; and we get the minimum value as 25 (between 27 and 25)


[Note: Array formula always needs to be entered as ctrl+shift+enter]


I would request you to first read Chandoo's Array Formula articles" and then read my explanation in order to understand this in a better way.


Hope I am able to explain you.


Looking forward to your reply.


Best Regards,

Kaushik
 
Hey Grumpy,


One more thing I was just wondering about!!


How frequently you need to do this work? I mean 1-2 or 2-3 months interval kind of....


If you need to do it again and again, then why don't you think of building an automated tool kind of a stuff which will help you to pull all the info you need in one click of a button within a minute time?


It's just an advice. Please ignore if it doesn't suit to your requirement.


Regards,

Kaushik
 
Unfortunately my cricket duties are a hobby, not a profession - I would also love to do that for a living!! I figured that you were probably a cricket-lover - India's love affair with the sport is well-known, and your continued involvement in this post is proof of your passion as well!


I will certainly look at the array links you included. Thanks.


I compile my stats book at the end of every season, so it's once a year. What did you have in mind in terms of an automated tool, as once I've finally finsihed entering all of the match data in the Indiv Performances worksheet, a simple click of a dropdown list, the tweaking of a pivot table or the filtering / sorting of a column should hopefully give me what I need pretty quickly.


Now if there was an easier way to enter the data into the Indiv Performances worksheet in the first place (considering that I still have another eleven seasons' worth of matches to enter before the new season starts in October)...!


Are you volunteering your further assistance ...(lol)??!


PS: Just out of curiosity, is there another way to create the K7 and R7 formulas without first creating new pivot table worksheets, or are they an integral part of the process?


PPS: You have indeed got the correct cricket club website - that's another aspect of my scorer / statistician duties!
 
You must log in or register to reply here.
Back
Top