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...

[TABLE="width: 430"]
<colgroup><col><col><col span="4"></colgroup><tbody>[TR]
[TD]Team[/TD]
[TD]Account Manager[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Daniel[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Jane[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Gill[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Gary[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Brendan[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Hillary[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Sylvia[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Monica[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Sean[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Graham[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Dean[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Sarah[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 174"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Account Manager[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]Daniel[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Gary[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Sylvia[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Graham[/TD]
[TD="align: right"]7
[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 748"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Team[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Team[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Team[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Team[/TD]
[TD]Account Manager[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD]Team[/TD]
[TD]Account Manager[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD]Team[/TD]
[TD]Account Manager[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]A[/TD]
[TD]Daniel[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]Gary[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD]C[/TD]
[TD]Gill[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]A[/TD]
[TD]Gary[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]Brendan[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD]C[/TD]
[TD]Hillary[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]A[/TD]
[TD]Sylvia[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]Monica[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]C[/TD]
[TD]Sean[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]A[/TD]
[TD]Graham[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]Dean[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD]C[/TD]
[TD]Sarah[/TD]
[TD="align: right"]9[/TD]
[/TR]
</tbody>[/TABLE]


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.