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

TNW	POLE 8TH ST NJUNS# 1748092 TRI-PLEX COIL ON POLE. MTEMC JOINT USE AGREEMENT. 3 DROPS, 1 DOWN GUY	PWO	PWO6E3G81	MURFREESBORO		2M	TRNSFR	0	OP
TNW	POLE 8TH ST NJUNS# 1748092 TRI-PLEX COIL ON POLE. MTEMC JOINT USE AGREEMENT. 3 DROPS, 1 DOWN GUY	PWO	PWO6E3G81	MURFREESBORO		622M	MOVE	3	OP

<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> </tbody>

 

[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))