Compare 1 column 2 rows

[imraja12]

New to excel and I can not fit this out.

I have a sheet that have a single column with data in rows which I need to match if 2 items are common.

Example:

	A	B	C	D	E	F	G
1	Invoice #	Sold By	Customer	Product SKU	Product Name	Qty	Contract #
2	W1404IN1431	Rep1	John Smith	CLVZRB000069	New Activation (Rate Plan Rebate)	1	161576959
3	W1404IN1431	Rep1	John Smith	CLVZNS000064	Prepaid Activation Rate Plan	1	161576959
4	W1404IN1431	Rep1	John Smith	CLVZRB002625	Prepaid - Smart	1	161576959
5	W1404IN1431	Rep1	John Smith	CLVZNS000032	Customer Owned Device (START PAW)	1	161576959
6	W1404IN1296	Rep2	Smith Doe	CLVZRB000069	New Activation (Rate Plan Rebate)	1	160533955
7	W1404IN1296	Rep2	Smith Doe	CLVZNS000064	Prepaid Activation Rate Plan	1	160533955
8	W1404IN1296	Rep2	Smith Doe	CLVZRB002625	Prepaid - Smart	1	160533955
9	W1404IN1296	Rep2	Smith Doe	CLVZLG002605	LG OPTIMUS ZONE 3	1	160533955

<tbody>
</tbody>

I would like it to display "Prepaid CPE" if E4&E5 of contract# 161576959 match. Contract #160533955 wouldn't do anything since "Customer Owned Device (START PAW) is not found in that grouping.

Thanks anyone for help.

Common items linking would be contract# or Invoice#

 

[Special-K99]

Here's one way

=IF(SUMPRODUCT((G2=G$2:G$9)*((E$2:E$9="Prepaid - Smart")+(E$2:E$9="Customer Owned Device (START PAW)")))=2,"CPE","")

 

[imraja12]

Here's one way

=IF(SUMPRODUCT((G2=G$2:G$9)*((E$2:E$9="Prepaid - Smart")+(E$2:E$9="Customer Owned Device (START PAW)")))=2,"CPE","")

Thanks that worked but the issue I am having is its returning "CPE" next to each cell where I have the contract number since that contract number is in 4 places. Can I only get it to display one next to any 1 of the group of contract number. Also the second is Prepaid - Smart is just one 4 items, can I just add if(and to the 1st part of ((E$2:E$9="Prepaid - Smart) with additional items? .

 

[Special-K99]

"Can I only get it to display one next to any 1 of the group of contract number."
And the one you want to display against is...?

"can I just add if(and to the 1st part of ((E$2:E$9="Prepaid - Smart) with additional items?"
What are ALL the values you want to compare for the "Prepaid" items, I don't want to waste time guessing them.

 

[imraja12]

"Can I only get it to display one next to any 1 of the group of contract number."
And the one you want to display against is...?

"can I just add if(and to the 1st part of ((E$2:E$9="Prepaid - Smart) with additional items?"
What are ALL the values you want to compare for the "Prepaid" items, I don't want to waste time guessing them.

Sorry. Only next to the one that states Customer Owned Device (START PAW) so in that example only next to G5.

The other pairs could be

Prepaid - Basic & Customer Owned Device (START PAW)

or Prepaid - iPad & Customer Owned Device (START PAW)

or Prepaid - iPhone & Customer Owned Device (START PAW) or

Prepaid - MBB & Customer Owned Device (START PAW) or

Prepaid - Smart & Customer Owned Device (START PAW) or

Prepaid - Tablet & Customer Owned Device (START PAW)

<colgroup><col></colgroup><tbody>
</tbody>

<strike></strike>


Thanks.

 

[Special-K99]

Sio anything that begins "Prepaid" ?

Try (untested)

=IF(AND(G2="Customer Owned Device (START PAW)",SUMPRODUCT((G2=G$2:G$9)*((LEFT(E$2:E$9,7)="Prepaid")+(E$2:E$9="Customer Owned Device (START PAW)")))=2),"CPE","")