If statement

bwlytkr

Board Regular
Joined
Jun 8, 2012
Messages
102
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 2MTRNSFR0OP
TNW POLE 8TH ST NJUNS# 1748092 TRI-PLEX COIL ON POLE. MTEMC JOINT USE AGREEMENT. 3 DROPS, 1 DOWN GUY PWO PWO6E3G81 MURFREESBORO 622MMOVE3OP
<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>
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
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

Board Regular
Joined
Jun 8, 2012
Messages
102
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

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

May be you mean this?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">TNW </td><td style=";">PWO </td><td style=";">PWO6E3G81</td><td style=";">MURFREESBORO </td><td style="text-align: right;;"></td><td style=";">2M</td><td style=";">TRNSFR</td><td style="text-align: right;;">0</td><td style=";">OP</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">TNW </td><td style=";">PWO </td><td style=";">PWO6E3G81 </td><td style=";">MURFREESBORO </td><td style="text-align: right;;"></td><td style=";">622M</td><td style=";">MOVE</td><td style="text-align: right;;">3</td><td style=";">OP</td><td style="text-align: right;;">1</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K1</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">G1="622M",COUNTIF(<font color="Green">D$1:D$1000,D1</font>)</font>),1,0</font>)</td></tr></tbody></table></td></tr></table><br />

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

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
You're welcome.

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

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>M</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">TNW </td><td style=";">PWO </td><td style=";">PWO6E3G81</td><td style=";">MURFREESBORO </td><td style="text-align: right;;"></td><td style=";">2M</td><td style=";">TRNSFR</td><td style="text-align: right;;">0</td><td style=";">OP</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">TNW </td><td style=";">PWO </td><td style=";">PWO6E3G81 </td><td style=";">MURFREESBORO </td><td style="text-align: right;;"></td><td style=";">622M</td><td style=";">MOVE</td><td style="text-align: right;;">3</td><td style=";">OP</td><td style="text-align: right;;">1</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">M1</th><td style="text-align:left">=--AND(<font color="Blue">G1="622M",COUNTIF(<font color="Red">D$1:D$1000,D1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,109,451
Messages
5,528,829
Members
409,839
Latest member
akashsadhu
Top