Remember Me?

#1
April 7th 05, 12:41 AM
 Jim Posts: n/a
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
April 7th 05, 03:38 AM
 Max Posts: n/a

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
April 7th 05, 03:51 AM
 Biff Posts: n/a

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

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

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

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

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

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

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.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Leesa Charts and Charting in Excel 3 March 28th 05 04:44 PM Water guy Excel Discussion (Misc queries) 1 February 7th 05 10:26 PM Scott Ehrlich Excel Discussion (Misc queries) 2 January 25th 05 05:17 AM Dave1155 Excel Worksheet Functions 1 January 12th 05 05:55 PM OkieViking Excel Discussion (Misc queries) 1 December 16th 04 09:17 PM

All times are GMT +1. The time now is 06:57 PM.