simple excel equation help

[Brandon21]

So, if you were to look at this spreadsheet you would see that there are some repeating formations. There are two MaA formations listed for well RA-0001. This is because there were two interpreters for this formation, named 1 and 2. In total in this spreadsheet there are 6 interpreters, 1 through 6. 1 being the best, 6 being the worst.

I need to create a worksheet that only has one interpreter for each formation.

I cant just filter out interpreters 2 through 6 though because interpreter 1 may not have picked every formation. (as you can see in bold, MaC1 was only picked by interpreter 2).

What I need to be able to do is select the best one interpreter for each specific formation for each specific well. (there are about 500 wells or so).

I have organized the data so that it is by well, formation, then interpreter (smallest to largest) aka (best to worst for me).

I was wondering if there was some way I could delete the row of any repeating formation.

Is there an equation I could write? I cant figure it out.

I just want to ensure that I have the best interpreter for each formation, and delete the information of any other interpreter for that formation. simple enough, but i cant figure it out.

Any help would be great! thanks.

WELL NAME	FORMATION	INTERPRETER
RA-0001	MaA	1
RA-0001	MaA	2
RA-0001	MaA_L	1
RA-0001	MaA_L	2
RA-0001	MaA_Mrk1	1
RA-0001	MaA_Mrk1	2
RA-0001	MaA_Mrk2	1
RA-0001	MaA_Mrk2	2
RA-0001	MaB	1
RA-0001	MaB	2
RA-0001	MaB_Mrk1	1
RA-0001	MaB_Mrk1	2
RA-0001	MaB_Mrk2	1
RA-0001	MaB_Mrk2	2
RA-0001	MaB_Mrk3	1
RA-0001	MaB_Mrk3	2
RA-0001	MaC	1
RA-0001	MaC	2
RA-0001	MaC1	2
RA-0001	MaC2	1
RA-0001	MaC2	2
RA-0001	MaC2	3
RA-0001	MaC3	2
RA-0001	MaC4	2
RA-0001	MaD	1
RA-0001	MaD	2
RA-0001	MaD_Mrk1	1

<colgroup><col width="124" span="3" style="width:93pt"> </colgroup><tbody>
</tbody>

 

[Brandon21]

is there like an IF AND statment I could do?

 

[Rick Rothstein]

is there like an IF AND statment I could do?

A formula cannot physically delete a row, so you are going to need a macro in order to modify your original data that way.. Give this one a try...

Sub KeepBestInterpretter()
  Dim LR As Long
  LR = Cells(Rows.Count, "A").End(xlUp).Row
  Range("B2:B" & LR) = Evaluate("IF(B2:B" & LR & "=B1:B" & LR - 1 & ","""",B2:B" & LR & ")")
  On Error Resume Next
  Range("B2:B" & LR).SpecialCells(xlBlanks).EntireRow.Delete
  On Error GoTo 0
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (KeepBestInterpretter) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm).

 

[Brandon21]

wow, that works perfectly! thank you so much.