Return a value if two other values exist

[pidoling]

I am trying to figure out how to set up a formula for the following
example:
Two different worksheets A and B

worksheet A: column A = ID, column b = service type

worksheet B: column A = ID, column b =service type(cleaning), column c
= service type(repair), column C = service type(delivery).

worksheet A can have the same id on multiple rows with a different
type of service for example
id, services ---- column header
123, cleaning
124, cleaning
123, repair
124, delivery

on Worksheet B, I want one id per row like this:
id, cleaning, repair, delivery ------column header
123, yes, yes, no

So baically I want one row per id on worksheet b going across.

Thanks,
Rob

 

[Bernard Liengme]

Assumptions:
1) data on SheetA goes from 2 to 101 (row 1 having labels)
2) first ID in SheetB is in A2 and row I has labels "ID", "Cleaning",
"Repair", etc

In B2 of SheetB enter
=SUMPRODUCT(--('SheetA'!$A$2:$A$101=$A1),--('SheetA'!$B$2:$B$101=B$1))
Copy across and down the table on SheetB

For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
Debra Dalgleish
http://www.contextures.com/xlFunctions04.html#SumProduct


Or maybe its time to learn about Pivot Tables
Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx


best wishes