Creating a Summary Table by Group

[Isara1]

I am having a difficult time trying to figure out how to create a Summary Table by Group. I am making a template which will be used over and over with different experimental data. So I have several Tabs (different days) in a worksheet where I'm collecting Subject results for 28 animals when a subset of subjects were put through a number of treatment group within each day. Eventually every animal was put through each group. So in the summary tab off to the right, I have created a long helper section where I list all the results along with period ID, Subject ID, Group Abbreviation from each tab.
So in the main section I basically want to make a summary grouping all the same group data in 1 column so that then I can create a chart. I am not sure how I can create a formula that would go through the list and pick all the same group and list them down in a column. Can this be done by a formula or do I have to look into making a macro? Ca someone please advise how I can accomplish this?

I am attaching the Summary file so that you can see what I am talking about.

Thanks,
Isara

[FlameRetired]

Not sure I have interpreted correctly.

Try array entering this in B2 fill down and across.

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Then this in B31 (AGGREGATE ignores errors) and fill across.

Please Login or Register  to view this content.

I am not a statistician. This next might not suite the purpose. I used this next AGGREGATE function because of the ignore errors option.

Please Login or Register  to view this content.

That's STDEV.S ; for STDEV.P change the 7 to 8.

I filled the formula you had in B33 and filled across.


Wasn't sure what you wanted to do with the #N/A errors. I left them in place.

[Isara1]

This was great and did the trick. I was trying a standard index match and it had to be in alphabetical order but your formula fixed that. I just never quite understand all of that. I would be interested to find out how you came to the formula. Thanks!

[FlameRetired]

Glad to here it.

I assume you mean this one.

=INDEX($Z$2:$Z$309,MATCH(1,1/($A2=$X$2:$X$309)*(B$1=$Y$2:$Y$309),0))

That is a construction I learned from Tony Valko. It's a good alternative to concatenation ... ie .... $A2&B$1 matched to $X$309&$Y$2:$Y$309.

The ($A2=$X$2:$X$309)*(B$1=$Y$2:$Y$309) part creates an array of 1s and 0s which is the result of multiplying TRUEs and FALSEs.

{1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

In these cases there should only be one 1 in each of the arrays. Reciprocating those produces this.

{1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV}

I shortened this because the real array is about 20 lines of text.

MATCH ignores the errors and returns the row number of the first 1 that 1 matches. In this case it would be the first row. Of course it passes that to INDEX. It is a very fast matching scheme.

Does this help?

Edit Since there will only be one 1 in each array a better and faster formula would be an approximate match:

=INDEX($Z$2:$Z$309,MATCH(1,1/($A2=$X$2:$X$309)*(B$1=$Y$2:$Y$309),1))