Create a summary list without using PivotTables?

[Jaye]

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

 

[Anne Troy]

Have you tried Autofilter, Jaye?
http://www.officearticles.com/excel/using_autofilter_in_microsoft_excel.htm
(MrExcel and I have a running joke about how I "don't do pivot tables")
************
Anne Troy
www.OfficeArticles.com

 

[Jaye]

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.

 

[Jaye]

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]

I don't understand your question, Jaye.
************
Anne Troy
www.OfficeArticles.com

 

[DOR]

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

 

[Jaye Cavallo]

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]

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.

 

[Jaye Cavallo]

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.