Replace Data in Table based on result of IF Statement

[JTL9161]

I have a table (20 rows, 3 columns) with various data. I have 20 other cells with if statements that will result in 2 possible options, for example either AAA-1 or AAA-2 will be the result of the if statement. Not all the if statements are completed at the same time. What I am asking is when and IF statement comes up with an answer say AAA-2 I am looking to delete all the "AAA-2" from the 20 row 3 column table and do so every time an if statement comes up with one of it 2 possible answers.


AAA-1 AAA-3 AAA-2
AAA-2 AAA-1 AAA-4
AAA-3 AAA-2 AAA-5

I am looking for the result of

AAA-1 AAA-3
AAA-1 AAA-4
AAA-3 AAA-5


Thank you,
James

 

[Legacy 143009]

Here is an sample code assuming your table starts on A1 with no headers.

Sub myFunction()
  For r = 1 To 20 'Must be your row addresses
    For c = 1 To 3 'Must be your coulmn addresses
      If Cell(r, c).Value = "AAA-2" Then
        Select Case i
        Case 1
          Cell(r, c).Value = Cell(r, c+1).Value
          Cell(r, c+1).Value = Cell(r, c+2).Value
          Cell(r, c+2).Value = ""
        Case 2
          Cell(r, c).Value = Cell(r, c+1).Value
          Cell(r, c+1).Value = ""
        Case 3
          Cell(r, c).Value = ""
        End Select
      End If
    Next
  Next
End Sub

 

[JTL9161]

Sorry, I was looking for a formula not a VBA

 

[shinigamilight]

Maybe provide some sample data using XL2BB

 

[Fluff]

A formula cannot delete data from other cells.