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:

[TABLE="width: 907"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Invoice #[/TD]
[TD]Sold By[/TD]
[TD]Customer[/TD]
[TD]Product SKU[/TD]
[TD]Product Name[/TD]
[TD]Qty[/TD]
[TD]Contract #[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]W1404IN1431[/TD]
[TD]Rep1[/TD]
[TD]John Smith[/TD]
[TD]CLVZRB000069[/TD]
[TD]New Activation (Rate Plan Rebate)[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"] 161576959[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]W1404IN1431[/TD]
[TD]Rep1[/TD]
[TD]John Smith[/TD]
[TD]CLVZNS000064[/TD]
[TD]Prepaid Activation Rate Plan[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]161576959[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]W1404IN1431[/TD]
[TD]Rep1[/TD]
[TD]John Smith[/TD]
[TD]CLVZRB002625[/TD]
[TD]Prepaid - Smart[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]161576959[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]W1404IN1431[/TD]
[TD]Rep1[/TD]
[TD]John Smith[/TD]
[TD]CLVZNS000032[/TD]
[TD]Customer Owned Device (START PAW)[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]161576959[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]W1404IN1296[/TD]
[TD]Rep2[/TD]
[TD]Smith Doe[/TD]
[TD]CLVZRB000069[/TD]
[TD]New Activation (Rate Plan Rebate)[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]160533955[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]W1404IN1296[/TD]
[TD]Rep2[/TD]
[TD]Smith Doe[/TD]
[TD]CLVZNS000064[/TD]
[TD]Prepaid Activation Rate Plan[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]160533955[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]W1404IN1296[/TD]
[TD]Rep2[/TD]
[TD]Smith Doe[/TD]
[TD]CLVZRB002625[/TD]
[TD]Prepaid - Smart[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]160533955[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]W1404IN1296[/TD]
[TD]Rep2[/TD]
[TD]Smith Doe[/TD]
[TD]CLVZLG002605[/TD]
[TD]LG OPTIMUS ZONE 3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]160533955[/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="width: 355"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Prepaid - Basic & Customer Owned Device (START PAW)[/TD]
[/TR]
[TR]
[TD]
or

Prepaid - iPad & Customer Owned Device (START PAW)[/TD]
[/TR]
[TR]
[TD]
or

Prepaid - iPhone & Customer Owned Device (START PAW)

or


[/TD]
[/TR]
[TR]
[TD]Prepaid - MBB & Customer Owned Device (START PAW)

or

[/TD]
[/TR]
[TR]
[TD]Prepaid - Smart & Customer Owned Device (START PAW)

or

[/TD]
[/TR]
[TR]
[TD]Prepaid - Tablet & Customer Owned Device (START PAW)[/TD]
[/TR]
</tbody>[/TABLE]
<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","")