Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Create a summary list without using PivotTables?
I know my way around Excel pretty well, though not an expert.
I have created my an Excel file with information for each player in a sports league whose playing rights is held by one of our teams. Essentially, the file serves as a crude player database with the following column headings saved as named arrays: Master (this is a unique ID number) Last_N First_N Team_ID (number assigned to a specific team) Team (text name for team) Status (designates player status by number denoting 1 for active, 2-inactive, 3-suspended...) Jersey (uniform number) Pos. Ht. Wt. Birthdate Age Hometown College Each row has also been saved as a named array starting with P_1 through P_329, noting that there are 329 players in the League. Is there a method (without using Pivot Tables) to produce a roster of active players for each team in a separate list? If team=x and status=1 then display Jersey, Last_N, First_N, Pos., Ht., Wt., Birthdate, Age, Hometown, College for Team X If team=y and status=1, then display Jersey, Last_N, First_N, Pos., Ht., Wt., Birthdate, Age, Hometown, College for Team Y |
#2
|
|||
|
|||
Create a summary list without using PivotTables?
Have you tried Autofilter, Jaye?
http://www.officearticles.com/excel/...soft_excel.htm (MrExcel and I have a running joke about how I "don't do pivot tables") ************ Anne Troy www.OfficeArticles.com "Jaye" wrote in message ... I know my way around Excel pretty well, though not an expert. I have created my an Excel file with information for each player in a sports league whose playing rights is held by one of our teams. Essentially, the file serves as a crude player database with the following column headings saved as named arrays: Master (this is a unique ID number) Last_N First_N Team_ID (number assigned to a specific team) Team (text name for team) Status (designates player status by number denoting 1 for active, 2-inactive, 3-suspended...) Jersey (uniform number) Pos. Ht. Wt. Birthdate Age Hometown College Each row has also been saved as a named array starting with P_1 through P_329, noting that there are 329 players in the League. Is there a method (without using Pivot Tables) to produce a roster of active players for each team in a separate list? If team=x and status=1 then display Jersey, Last_N, First_N, Pos., Ht., Wt., Birthdate, Age, Hometown, College for Team X If team=y and status=1, then display Jersey, Last_N, First_N, Pos., Ht., Wt., Birthdate, Age, Hometown, College for Team Y |
#3
|
|||
|
|||
Create a summary list without using PivotTables?
Anne,
Thanks for the suggestion. I have tried Advanced Filter and it will filter my list by the criteria I want (team=x and status=y), but: 1. I have to keep everything on one worksheet even if I copy the data to another area; 2. I have to apply the filter six times to get the data for the six teams; 3. The filtered list isn't dynamic. I guess I want my cake and eat it too. I would like to keep a dynamic filtered list of each team with a player by status. Still not sure how I can do that. "Anne Troy" wrote in message ... Have you tried Autofilter, Jaye? http://www.officearticles.com/excel/...soft_excel.htm (MrExcel and I have a running joke about how I "don't do pivot tables") ************ Anne Troy www.OfficeArticles.com "Jaye" wrote in message ... I know my way around Excel pretty well, though not an expert. I have created my an Excel file with information for each player in a sports league whose playing rights is held by one of our teams. Essentially, the file serves as a crude player database with the following column headings saved as named arrays: Master (this is a unique ID number) Last_N First_N Team_ID (number assigned to a specific team) Team (text name for team) Status (designates player status by number denoting 1 for active, 2-inactive, 3-suspended...) Jersey (uniform number) Pos. Ht. Wt. Birthdate Age Hometown College Each row has also been saved as a named array starting with P_1 through P_329, noting that there are 329 players in the League. Is there a method (without using Pivot Tables) to produce a roster of active players for each team in a separate list? If team=x and status=1 then display Jersey, Last_N, First_N, Pos., Ht., Wt., Birthdate, Age, Hometown, College for Team X If team=y and status=1, then display Jersey, Last_N, First_N, Pos., Ht., Wt., Birthdate, Age, Hometown, College for Team Y |
#4
|
|||
|
|||
Create a summary list without using PivotTables?
I don't know if this is a repeated posting, but I think another to ask my
question is whethere there is a method to store only the true values of an IF statement in an array and then be able to output those values. "Anne Troy" wrote in message ... Have you tried Autofilter, Jaye? http://www.officearticles.com/excel/...soft_excel.htm (MrExcel and I have a running joke about how I "don't do pivot tables") ************ Anne Troy www.OfficeArticles.com "Jaye" wrote in message ... I know my way around Excel pretty well, though not an expert. I have created my an Excel file with information for each player in a sports league whose playing rights is held by one of our teams. Essentially, the file serves as a crude player database with the following column headings saved as named arrays: Master (this is a unique ID number) Last_N First_N Team_ID (number assigned to a specific team) Team (text name for team) Status (designates player status by number denoting 1 for active, 2-inactive, 3-suspended...) Jersey (uniform number) Pos. Ht. Wt. Birthdate Age Hometown College Each row has also been saved as a named array starting with P_1 through P_329, noting that there are 329 players in the League. Is there a method (without using Pivot Tables) to produce a roster of active players for each team in a separate list? If team=x and status=1 then display Jersey, Last_N, First_N, Pos., Ht., Wt., Birthdate, Age, Hometown, College for Team X If team=y and status=1, then display Jersey, Last_N, First_N, Pos., Ht., Wt., Birthdate, Age, Hometown, College for Team Y |
#5
|
|||
|
|||
Create a summary list without using PivotTables?
I don't understand your question, Jaye.
************ Anne Troy www.OfficeArticles.com "Jaye" wrote in message ... I don't know if this is a repeated posting, but I think another to ask my question is whethere there is a method to store only the true values of an IF statement in an array and then be able to output those values. "Anne Troy" wrote in message ... Have you tried Autofilter, Jaye? http://www.officearticles.com/excel/...soft_excel.htm (MrExcel and I have a running joke about how I "don't do pivot tables") ************ Anne Troy www.OfficeArticles.com "Jaye" wrote in message ... I know my way around Excel pretty well, though not an expert. I have created my an Excel file with information for each player in a sports league whose playing rights is held by one of our teams. Essentially, the file serves as a crude player database with the following column headings saved as named arrays: Master (this is a unique ID number) Last_N First_N Team_ID (number assigned to a specific team) Team (text name for team) Status (designates player status by number denoting 1 for active, 2-inactive, 3-suspended...) Jersey (uniform number) Pos. Ht. Wt. Birthdate Age Hometown College Each row has also been saved as a named array starting with P_1 through P_329, noting that there are 329 players in the League. Is there a method (without using Pivot Tables) to produce a roster of active players for each team in a separate list? If team=x and status=1 then display Jersey, Last_N, First_N, Pos., Ht., Wt., Birthdate, Age, Hometown, College for Team X If team=y and status=1, then display Jersey, Last_N, First_N, Pos., Ht., Wt., Birthdate, Age, Hometown, College for Team Y |
#6
|
|||
|
|||
Create a summary list without using PivotTables?
Jaye,
Here is a crude approach that should work (if coded properly!). It is easier to describe without using your array names, although they could be used also, but it would require OFFSETs and all that jazz ... Assuming: Players are on a sheet called Players Header row is row 1 on your Players sheet and first data row is row 2, Assume team ID is in col C and Status is in col E On your Players sheet, in a new column (X) enter in row 2: =IF(E2=1,C2&"-"&SUMPRODUCT(C$2:C2=C2)*(E$2:E2=1)),"") and copy down to last player Call that range TeamActive. This will concatenate team_ID with a serial number representing the first, second, third, etc. active player on each team. (The "-" delimiter is used to avoid ambiguities that might arise from adjacent numbers - you could use a space instead) Allocate a new sheet to each team. In A1 of this sheet enter the team ID for a specific team. In B1 enter =SUMPRODUCT((Team_ID=A1)*(Status=1)) This should count the number of active players in this team. To list your active players: Enter consecutive numbers 1 through n, where n represents the maximum number of active players per team, in column A from, say Row 3, down. In B3 enter =IF(A3<=$B$1,MATCH($A$1&"-"&A3,TeamActive,0),"") and copy down This creates a displacement or index for each active player in the players sheet. From here on all you need to enter is a formula in the remaining columns of the team sheet such as =IF($B3="","",INDEX(Last_N,$B3)) in C3 to get the Last Name, and so on for the other attributes, and copy down. (Assuming Last_N is defined as a vector - a single column range). You should get blank cells after the last active player. You can move the Team_ID and Active Count anywhere on your sheet, as you wish, to suit your formatting needs. After you test it, and it works(!) copy the sheet as many times as you have teams and enter the appropriate team_ID in the Team_ID cell on each sheet to get the active players for that team. I've no doubt that this crude, but easy to follow, approach could be refined with some array formulas but it tends to work for me and is easy to debug. I hope I haven't made too many typos ... HTH |
#7
|
|||
|
|||
Create a summary list without using PivotTables?
Thanks. This helped a great deal. And I was able to tweak the code for
some other uses. Now I will spend a little more time creating arrays for somethings 'cause I'm afraid other shared users might accidentally delete some important rows of information. Thanks again. "DOR" wrote in message oups.com... Jaye, Here is a crude approach that should work (if coded properly!). It is easier to describe without using your array names, although they could be used also, but it would require OFFSETs and all that jazz ... Assuming: Players are on a sheet called Players Header row is row 1 on your Players sheet and first data row is row 2, Assume team ID is in col C and Status is in col E On your Players sheet, in a new column (X) enter in row 2: =IF(E2=1,C2&"-"&SUMPRODUCT(C$2:C2=C2)*(E$2:E2=1)),"") and copy down to last player Call that range TeamActive. This will concatenate team_ID with a serial number representing the first, second, third, etc. active player on each team. (The "-" delimiter is used to avoid ambiguities that might arise from adjacent numbers - you could use a space instead) Allocate a new sheet to each team. In A1 of this sheet enter the team ID for a specific team. In B1 enter =SUMPRODUCT((Team_ID=A1)*(Status=1)) This should count the number of active players in this team. To list your active players: Enter consecutive numbers 1 through n, where n represents the maximum number of active players per team, in column A from, say Row 3, down. In B3 enter =IF(A3<=$B$1,MATCH($A$1&"-"&A3,TeamActive,0),"") and copy down This creates a displacement or index for each active player in the players sheet. From here on all you need to enter is a formula in the remaining columns of the team sheet such as =IF($B3="","",INDEX(Last_N,$B3)) in C3 to get the Last Name, and so on for the other attributes, and copy down. (Assuming Last_N is defined as a vector - a single column range). You should get blank cells after the last active player. You can move the Team_ID and Active Count anywhere on your sheet, as you wish, to suit your formatting needs. After you test it, and it works(!) copy the sheet as many times as you have teams and enter the appropriate team_ID in the Team_ID cell on each sheet to get the active players for that team. I've no doubt that this crude, but easy to follow, approach could be refined with some array formulas but it tends to work for me and is easy to debug. I hope I haven't made too many typos ... HTH |
#8
|
|||
|
|||
Create a summary list without using PivotTables?
You should consider using sheet protection to prevent inadvertent
deletions by other users. Without seeing your sheets it is hard to explain to you how and where to use it, but you can look up Protection in Help. Array formulas per se don't do anything to protect your work. Because of their complexity and need for special confirmation, they can be more sensitive to inexperienced meddling. Just ensure that all your cells with formulas are locked (Format/Cells/Protection) or rather, since cells are locked initially by default, unlock your data entry cells, and then protect the sheets. You may want to choose to allow row insertion with the protection, so that you can enter new players using Copy/Insert Row and then change the entered data in the duplicate row you get to that for the new player. You could also supply a simple macro tied to a button that does this and clears the cells without formulas to prepare them for new data. You might also want to change the cells containing the consecutive numbers in column A of the "Active Players" sheet to something like =Row()-Row(HeaderRow) so as to preserve the consecutive numbering if, by chance, you Copy/Insert or Delete a row in that sheet. It is usually dangerous to assume that consecutive numbers will survive row insertions or deletions. Good formula design makes lists with formulas impervious to row insertions and deletions. |
#9
|
|||
|
|||
Create a summary list without using PivotTables?
Thanks.
I am not concerned about the sheets displaying each team's active players. I have protected those. I am concerned about the "list" worksheet, which is the sheet that contains all of the player data with each column and row heading used as the name for an array. I haven't experimented with what happens when a row of player data gets deleted, particularly with the TeamActive range you helped me setup. "DOR" wrote in message oups.com... You should consider using sheet protection to prevent inadvertent deletions by other users. Without seeing your sheets it is hard to explain to you how and where to use it, but you can look up Protection in Help. Array formulas per se don't do anything to protect your work. Because of their complexity and need for special confirmation, they can be more sensitive to inexperienced meddling. Just ensure that all your cells with formulas are locked (Format/Cells/Protection) or rather, since cells are locked initially by default, unlock your data entry cells, and then protect the sheets. You may want to choose to allow row insertion with the protection, so that you can enter new players using Copy/Insert Row and then change the entered data in the duplicate row you get to that for the new player. You could also supply a simple macro tied to a button that does this and clears the cells without formulas to prepare them for new data. You might also want to change the cells containing the consecutive numbers in column A of the "Active Players" sheet to something like =Row()-Row(HeaderRow) so as to preserve the consecutive numbering if, by chance, you Copy/Insert or Delete a row in that sheet. It is usually dangerous to assume that consecutive numbers will survive row insertions or deletions. Good formula design makes lists with formulas impervious to row insertions and deletions. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to Create a Conditional Drop down list | Excel Worksheet Functions | |||
i want to create a simple mailing list can u help | Excel Worksheet Functions | |||
The pivot tables 101 article says to use the "Create List" comman. | New Users to Excel | |||
How to create a macro that compares a list to another list | New Users to Excel | |||
how do i create a list from a cell? | New Users to Excel |