Comparing Columns 1 & 2 vs. Columns 3 & 4

[bondra76]

Got two sets of data that I want to compare against each other. Here's what the data sets look like -

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Contract #[/TD]
[TD]Supplier[/TD]
[TD]Approved?[/TD]
[TD]Service[/TD]
[TD]LookupKey[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ABC Company[/TD]
[TD]Yes[/TD]
[TD]Trash Removal[/TD]
[TD]1;Trash Removal[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ABC Company[/TD]
[TD]Yes[/TD]
[TD]Landscaping[/TD]
[TD]1; Landscaping[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Bobs Company[/TD]
[TD]No[/TD]
[TD]Landscaping[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]XYZ Company[/TD]
[TD]Yes[/TD]
[TD]Arborist[/TD]
[TD]3; Arborist[/TD]
[/TR]
</tbody>[/TABLE]

I created a lookupkey that's a simple if statement to check if the contract is approved; if it is then Contract # and service are combined and a lookup key is populated. I need to filter out the non-approved contracts.

In table 2, I am trying to illustrate what services the supplier is approved to do:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Contract #[/TD]
[TD]Supplier[/TD]
[TD]Arborist[/TD]
[TD]Landscaping[/TD]
[TD]Trash Removal[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ABC Company[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Bobs Company[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]XYZ Company[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

The TRUE/FALSE results are what I am after....

Right now I have the following formula in the TRUE/FALSE cells (C2:E:4) -
=SUMPRODUCT(--ISNUMBER(SEARCH(LookupKey,$A2&";"&C$1)))>0

I think the second part of my search formula is throwing everything off with the text joins, but not sure how else to do this. Maybe it's as simple as a formatting issue?

Thanks for any help in advance!!!!

 

[sheetspread]

Can this be done without the lookup key (everything matched in the sample but perhaps there's more)?

Excel 2010
	A	B	C	D	E
1	Contract #	Supplier	Approved?	Service	LookupKey
2	1	ABC Company	Yes	Trash Removal	1;Trash Removal
3	1	ABC Company	Yes	Landscaping	1; Landscaping
4	2	Bobs Company	No	Landscaping
5	3	XYZ Company	Yes	Arborist	3; Arborist
6
7
8	Contract #	Supplier	Arborist	Landscaping	Trash Removal
9	1	ABC Company	0	1	1
10	2	Bobs Company	0	0	0
11	3	XYZ Company	1	0	0
Sheet30

Cell Formulas
Range	Formula
C9	=SUMPRODUCT(--($B$2:$B$5=$B9),--($D$2:$D$5=C$8),--($C$2:$C$5="Yes"))

 

[steve the fish]

Pasting your first table into A1 of Sheet1 and the second into A1 or Sheet2 use this formula in C2 of Sheet2 and copy down and across:

=IFERROR(IF(LOOKUP(2,1/((Sheet1!$B:$B=$B2)*(Sheet1!$A:$A=$A2)*(Sheet1!$D:$D=C$1)),Sheet1!$C:$C)="Yes",TRUE,FALSE),FALSE)

 

[bondra76]

thanks Steve that worked out great!!!!