Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 7th 05, 12:41 AM
Jim
 
Posts: n/a
Default 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   Report Post  
Old April 7th 05, 03:38 AM
Max
 
Posts: n/a
Default

One set-up 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   Report Post  
Old April 7th 05, 03:51 AM
Biff
 
Posts: n/a
Default

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   Report Post  
Old April 7th 05, 04:10 AM
Max
 
Posts: n/a
Default

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   Report Post  
Old April 7th 05, 04:10 AM
Max
 
Posts: n/a
Default

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   Report Post  
Old April 7th 05, 03:33 PM
Jim
 
Posts: n/a
Default

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   Report Post  
Old April 7th 05, 03:46 PM
Max
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to summarize and chart raw data Leesa Charts and Charting in Excel 3 March 28th 05 04:44 PM
How to summarize data based on specific parameters Water guy Excel Discussion (Misc queries) 1 February 7th 05 11:26 PM
Help with data not getting plotted Scott Ehrlich Excel Discussion (Misc queries) 2 January 25th 05 06:17 AM
Pulling data from 1 sheet to another Dave1155 Excel Worksheet Functions 1 January 12th 05 06:55 PM
Summarize data with multiple conditions OkieViking Excel Discussion (Misc queries) 1 December 16th 04 10:17 PM


All times are GMT +1. The time now is 03:15 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017