Extract specific data from large Excel data set using formulas

[Silha]

Hi there,

I am looking for a way of extracting specific data from a large Excel data set I have.


For example, if the below is my large data set...

Team	Account Manager	Jan	Feb	Mar	Total
A	Daniel	2	5	5	12
B	Jane	5	8	3	16
C	Gill	3	6	6	15
A	Gary	6	1	1	8
B	Brendan	1	3	3	7
C	Hillary	2	3	6	11
A	Sylvia	1	6	1	8
B	Monica	2	1	2	5
C	Sean	5	2	1	8
A	Graham	4	1	2	7
B	Dean	6	2	5	13
C	Sarah	1	5	3	9

<colgroup><col><col><col span="4"></colgroup><tbody>
</tbody>

Then, I want to apply formulas to end up with (for example) the "Total" hours worked by the different Account Managers in team A, like below:

Account Manager	Total
Daniel	12
Gary	8
Sylvia	8
Graham	7

<colgroup><col><col></colgroup><tbody>
</tbody>

I know that I can use pivot tables to do this but I can't create specific team-based tables except by adding 'Team' category as the Report Filter. I want to be able to generate these tables to have tables for teams A, B and C sitting next to each other on the spreadsheet so they can all be seen at a glance. Can I generate these tables using a formula instead?

Thanks a lot!!

 

[texasalynn]

so why don't you setup 3 pivots side by side for the three teams? Would be so much simplier

 

[Silha]

I could do that but I am also creating corresponding charts to go with the sub-tables and using pivot tables doesn't give me any control over the formatting. Also, it seems that I can't suppress any 0 values in my charts if the chart is connected to a pivot table.

 

[texasalynn]

try this

	A	B	C	D	E	F	G	H	I	J	K
1	Team				Team				Team
2	A				B				C
3	4				4				4
4	Team	Account Manager	Total		Team	Account Manager	Total		Team	Account Manager	Total
5	A	Daniel	12		B	Gary	16		C	Gill	15
6	A	Gary	8		B	Brendan	7		C	Hillary	11
7	A	Sylvia	8		B	Monica	5		C	Sean	8
8	A	Graham	7		B	Dean	13		C	Sarah	9

<tbody>
</tbody>

Formulas:
=COUNTIF(Sheet1!$A$1:$A$13,A2) copy this into E3 & I3

Formula is an array formula (curls are added by pressing Ctrl + Shift + Enter)
A5 = {=IF(ROWS(A$5:A5)<=$A$3,INDEX(Sheet1!A$2:A$16,SMALL(IF(Sheet1!$A$2:$A$16=A$2,ROW(Sheet1!$A$2:$A$16)-ROW($A$2)+1),ROWS(A$5:A5))),"")}
B5 = {=IF(ROWS(B$5:B5)<=$A$3,INDEX(Sheet1!B$2:B$16,SMALL(IF(Sheet1!$A$2:$A$16=A$2,ROW(Sheet1!$A$2:$A$16)-ROW($A$2)+1),ROWS(B$5:B5))),"")}
C5 = {=IF(ROWS(C$5:C5)<=$A$3,INDEX(Sheet1!F$2:F$16,SMALL(IF(Sheet1!$A$2:$A$16=A$2,ROW(Sheet1!$A$2:$A$16)-ROW($A$2)+1),ROWS(C$5:C5))),"")}

 

[Silha]

Thanks so much!

Unfortunately, when I enter that (with my own cell references) and press CSE, it returns an error saying I typed it in wrong.

I found a similar array formula online which I also adapted to my cell references and had the same problem: =IF(ROWS(U$22:U22)<=V18,INDEX(INDIRECT(U$21),SMALL((IF(Team=$U$18, ROW(Team)-ROW($B$2)+1),ROWS(U$22:U22))),"")

Any idea what could be wrong?

 

[texasalynn]

if TEAM a range name? if so what is it

 

[Silha]

I found something that works really well, in case it is of use to anyone else.

{=IFERROR(INDEX(Project_Coordinator, SMALL(IF($I$3=Team,ROW(Team)-MIN(ROW(Team))+1,""),ROW(A1))),"")}

"Team" is the range A2:A12 in my original table example above.