Home 
Search 
Today's Posts 
#1




Summarize Data Set
I have a real challenge. I am trying to summarize a large data set.
I need to determine the number of different employees involved in delivering service per specific reservation, if the service type and product match criteria. Sample data: type product res_num emp_num A IA 3 50 B IA 2 60 B R5 1 60 B R5 1 60 B R5 2 60 B R5 2 80 B R5 2 80 C IA 3 40 If type = B or C And if product = R5 Calculate number of different emp_num utilized per res_num Sample result: Res_num Different emp_num 1 1 2 2 Is there a formula(s) that will do this? Thanks 
#2




One setup to try ...
Assuming the table below is in Sheet1 cols A to D, data from row2 down type product res_num emp_num A IA 3 50 B IA 2 60 B R5 1 60 B R5 1 60 B R5 2 60 B R5 2 80 B R5 2 80 C IA 3 40 Using 4 empty cols to the right, say cols F to I Put in F2: =IF(AND(OR($A2=Sheet2!$A$1,$A2=Sheet2!$B$1),$B2=Sh eet2!$C$1),C2,"") Copy F2 across to G2 Put in H2: =IF(F2="","",IF(COUNTIF(F$2:F2,F2)1,"",ROW())) Put in I2: =IF(G2="","",SUMPRODUCT(($F$2:F2=F2)*($G$2:G2=G2)) ) Select F2:I2, copy down to say, I100 to cover the expected data range in the table In Sheet2  A1:C1 will be earmarked for inputs Input the "type" into A1:B1, e.g.: B, C (i.e. type = B or C) Input the "product" into C1, e.g.: R5 Put in D1: =IF(ISERROR(SMALL(Sheet1!H:H,ROWS($A$1:A1))),"",IN DEX(Sheet1!C:C,MATCH(SMALL (Sheet1!H:H,ROWS($A$1:A1)),Sheet1!H:H,0))) Put in E1: =IF(D1="","",SUMPRODUCT((Sheet1!$F$2:$F$100=D1)*(S heet1!$I$2:$I$100=1))) Select D1:E1, fill down to E99 (cover the same range as in Sheet1) Cols D & E will return the desired results for the inputs in A1:C1 For the sample inputs in A1:C1, you'll get: 1 1 2 2 If A1:C1 contains: A, B, IA you'll get: 3 1 2 1 and so on ..  Rgds Max xl 97  GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom  "Jim" wrote in message ... I have a real challenge. I am trying to summarize a large data set. I need to determine the number of different employees involved in delivering service per specific reservation, if the service type and product match criteria. Sample data: type product res_num emp_num A IA 3 50 B IA 2 60 B R5 1 60 B R5 1 60 B R5 2 60 B R5 2 80 B R5 2 80 C IA 3 40 If type = B or C And if product = R5 Calculate number of different emp_num utilized per res_num Sample result: Res_num Different emp_num 1 1 2 2 Is there a formula(s) that will do this? Thanks 
#3




Hi!
Assume your layout is as follows: A2:An = Type B2:Bn = Product C2:Cn = Res_Num D2n = Emp_Num G2:Gn = list of unique Res_Num's Enter this formula in H2 and copy down as needed: =CEILING(SUMPRODUCT((A$2:A$9={"B","C"})*(B$2:B$9=" R5")* (C$2:C$9=G2)/COUNTIF(D$2$9,D$2$9)),1) Biff Original Message I have a real challenge. I am trying to summarize a large data set. I need to determine the number of different employees involved in delivering service per specific reservation, if the service type and product match criteria. Sample data: type product res_num emp_num A IA 3 50 B IA 2 60 B R5 1 60 B R5 1 60 B R5 2 60 B R5 2 80 B R5 2 80 C IA 3 40 If type = B or C And if product = R5 Calculate number of different emp_num utilized per res_num Sample result: Res_num Different emp_num 1 1 2 2 Is there a formula(s) that will do this? Thanks . 
#4




A small tweak to the formula in Sheet1's F2 ..
Put in F2: =IF(AND(OR($A2=Sheet2!$A$1,$A2=Sheet2!$B$1),$B2=Sh eet2!$C$1),C2,"") Copy F2 across to G2 Put instead in F2: =IF(C2="","",IF(AND(OR($A2=Sheet2!$A$1,$A2=Sheet2! $B$1),$B2=Sheet2!$C$1),C2, "")) Copy F2 across to G2 (as before)  Rgds Max xl 97  GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom  
#5




A small tweak to the formula in Sheet1's F2 ..
Put in F2: =IF(AND(OR($A2=Sheet2!$A$1,$A2=Sheet2!$B$1),$B2=Sh eet2!$C$1),C2,"") Copy F2 across to G2 Put instead in F2: =IF(C2="","",IF(AND(OR($A2=Sheet2!$A$1,$A2=Sheet2! $B$1),$B2=Sheet2!$C$1),C2, "")) Copy F2 across to G2 (as before)  Rgds Max xl 97  GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom  
#6




Thanks for both of the ideas! I will try them today.
"Jim" wrote: I have a real challenge. I am trying to summarize a large data set. I need to determine the number of different employees involved in delivering service per specific reservation, if the service type and product match criteria. Sample data: type product res_num emp_num A IA 3 50 B IA 2 60 B R5 1 60 B R5 1 60 B R5 2 60 B R5 2 80 B R5 2 80 C IA 3 40 If type = B or C And if product = R5 Calculate number of different emp_num utilized per res_num Sample result: Res_num Different emp_num 1 1 2 2 Is there a formula(s) that will do this? Thanks 
#7




You're welcome !
Let us know how it went for you ..  Rgds Max xl 97  GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom  "Jim" wrote in message ... Thanks for both of the ideas! I will try them today. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
how to summarize and chart raw data  Charts and Charting in Excel  
How to summarize data based on specific parameters  Excel Discussion (Misc queries)  
Help with data not getting plotted  Excel Discussion (Misc queries)  
Pulling data from 1 sheet to another  Excel Worksheet Functions  
Summarize data with multiple conditions  Excel Discussion (Misc queries) 