If statement

[bwlytkr]

Does anyone know of a formula I can use to remove rows if statement is true. Ex. If value in column g is 622m and column D's value is duplicated in column D then place a 1 in cell if true. below is a sample
[TABLE="width: 627"]
<colgroup><col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;" span="3"> <col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"> <col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;" span="6"> <tbody>[TR]
[TD="class: xl64, width: 84, bgcolor: #F8FBFC"]TNW [/TD]
[TD="class: xl64, width: 84, bgcolor: #F8FBFC"]POLE 8TH ST NJUNS# 1748092 TRI-PLEX COIL ON POLE. MTEMC JOINT USE AGREEMENT. 3 DROPS, 1 DOWN GUY [/TD]
[TD="class: xl64, width: 84, bgcolor: #F8FBFC"]PWO [/TD]
[TD="class: xl64, width: 80, bgcolor: #F8FBFC"]PWO6E3G81[/TD]
[TD="class: xl64, width: 84, bgcolor: #F8FBFC"] MURFREESBORO [/TD]
[TD="class: xl65, width: 84, bgcolor: #F8FBFC"] [/TD]
[TD="class: xl64, width: 84, bgcolor: #F8FBFC"]2M[/TD]
[TD="class: xl64, width: 84, bgcolor: #F8FBFC"]TRNSFR[/TD]
[TD="class: xl66, width: 84, bgcolor: #F8FBFC"]0[/TD]
[TD="class: xl64, width: 84, bgcolor: #F8FBFC"]OP[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: white"]TNW [/TD]
[TD="class: xl67, bgcolor: white"]POLE 8TH ST NJUNS# 1748092 TRI-PLEX COIL ON POLE. MTEMC JOINT USE AGREEMENT. 3 DROPS, 1 DOWN GUY [/TD]
[TD="class: xl67, bgcolor: white"]PWO [/TD]
[TD="class: xl67, bgcolor: white"]PWO6E3G81 [/TD]
[TD="class: xl67, bgcolor: white"]MURFREESBORO [/TD]
[TD="class: xl68, bgcolor: white"] [/TD]
[TD="class: xl67, bgcolor: white"]622M[/TD]
[TD="class: xl67, bgcolor: white"]MOVE[/TD]
[TD="class: xl69, bgcolor: white"]3[/TD]
[TD="class: xl67, bgcolor: white"]OP[/TD]
[/TR]
</tbody>[/TABLE]

 

[Johnny C]

To identify multiples you could use COUNTIFS

=IF(COUNTIFS($D:$D,$D1)=1,0,1)
in the first row and copy down (change the $D1 to whatever row its in)

 

[bwlytkr]

This seems to just put a 1 in the cell if column D is duplicate. I need it to look in Column G first to search for the 622M then look in Column D to see if the corresponding cell in that row is duplicated in Column D and if so place 1 or 0

 

[jtakw]

Hi,

May be you mean this?

Excel 2010
	A	C	D	E	F	G	H	I	J	K
1	TNW	PWO	PWO6E3G81	MURFREESBORO		2M	TRNSFR	0	OP	0
2	TNW	PWO	PWO6E3G81	MURFREESBORO		622M	MOVE	3	OP	1
Sheet2

Cell Formulas
Range	Formula
K1	=IF(AND(G1="622M",COUNTIF(D$1:D$1000,D1)),1,0)

K1 formula copied down. I used the range D$1:D$1000, adjust to your data range.

 

[bwlytkr]

Thanks. That Did it.

 

[jtakw]

You're welcome.

Actually, just realized, we don't even need the IF statement, this will do as well:

Excel 2010
	A	C	D	E	F	G	H	I	J	M
1	TNW	PWO	PWO6E3G81	MURFREESBORO		2M	TRNSFR	0	OP	0
2	TNW	PWO	PWO6E3G81	MURFREESBORO		622M	MOVE	3	OP	1
Sheet2

Cell Formulas
Range	Formula
M1	=--AND(G1="622M",COUNTIF(D$1:D$1000,D1))