Formula, vba, or logic to remove certain occurrences

[horees]

All,
I'm looking for a Formula, vba, or logic to remove certain occurrences of a pattern.

To make life easier, please see the table below.

I want A formula or logic to calculate the column D as shown below. The idea is to get as minimum of records that show x, zz, y, ww without repition.

i.e. to be able to identify the records of (x,zz) and (y,ww).

too hard???

Thank you

A	B	C	D
6/25	X	ZZ	1
6/25	X	WW	0
6/25	Y	ZZ	0
6/25	Y	WW	1

<TBODY>
</TBODY>

 

[horees]

Try this formula:

=IF(AND(B2="XX",C2="XX"), "MATCH", IF(AND(B2="YY",C2="YY"), "MATCH", IF(AND(B2="XX",C2="YY"), "MATCH", IF(AND(B2="YY",C2="XX"), "MATCH", "NO MATCH"))))

The problem is that the values XX, YY, WW and ZZ aint static values.. they can be anything... and not even the same from day to day...

Best regards

Leandrial

mrmmickle1
Thanks for the recommendation. As Leandrial mentioned, the data are dynamic. It can be anything...

 

[horees]

Here the code with the color changes...

It no longer deletes the rows we dont wanna keep.. only color them red

Sub RemoveRows()


Dim MyTable As Range
Dim ws As Worksheet
Dim RowCount As Integer
Dim b As String
Dim c As String
Dim x1 As String
Dim x2 As String
Dim x3 As String
Dim x4 As String
Dim x5 As String
Dim x6 As String
Dim x7 As String
Dim x8 As String
Dim n As Integer




Set ws = ActiveWorkbook.Worksheets("Sheet1")  'change Sheet1 to whatever the name of the worksheet
Set MyTable = ws.Range("Sample") 'change Sampe to whatever the name of the range




RowCount = MyTable.Rows.Count


For a = RowCount To 1 Step -1


    b = MyTable.Cells(a, 1).Value
    c = MyTable.Cells(a - 1, 1).Value
    
    If b = c Then
    
        n = n + 1
       
    Else
    
        If n = 3 Then
            
                x1 = MyTable.Cells(a, 2).Value
                x2 = MyTable.Cells(a, 3).Value
                x3 = MyTable.Cells(a + 1, 2).Value
                x4 = MyTable.Cells(a + 1, 3).Value
                x5 = MyTable.Cells(a + 2, 2).Value
                x6 = MyTable.Cells(a + 2, 3).Value
                x7 = MyTable.Cells(a + 3, 2).Value
                x8 = MyTable.Cells(a + 3, 3).Value
                
                If x1 = x2 And x3 = x4 Then
                
                    With MyTable.Rows(a + 3).Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 255
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
                    
                    With MyTable.Rows(a + 2).Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 255
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
                                    
                    n = 0
                
                Else
                
                    With MyTable.Rows(a + 2).Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 255
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
                    
                    With MyTable.Rows(a + 1).Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 255
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
                
                    n = 0
                    
                End If
            
        End If
    End If


Next


End Sub

Best regrads

Leandrial

Thanks for the code, now I can see which records shouldn't be highlight, and which records should have been highlighted. The data are confidential, but I masked it to be able to try it.

 

[horees]

3/30/2012	PPP	PPP	stay
3/31/2012	III	PPP	stay
4/1/2012	SSS	SSS	stay
4/2/2012	SSS	SSS	stay
4/3/2012	SSS	SSS	stay
4/3/2012	SSS	DDD	stay
4/5/2012	SSS	SSS	stay
4/6/2012	SSS	SSS	stay
4/7/2012	SSS	SSS	stay
4/8/2012	III	III	stay
4/8/2012	SSS	III	stay
4/9/2012	III	III	stay
4/10/2012	III	III	stay
4/10/2012	TTT	TTT	stay
4/10/2012	TTT	III	remove
4/10/2012	III	TTT	remove
4/14/2012	III	III	stay
4/15/2012	III	III	stay
4/16/2012	III	III	stay
4/16/2012	III	PD	stay
4/17/2012	III	III	stay
4/17/2012	III	SSS	stay
4/18/2012	III	III	stay
4/18/2012	PD	III	stay
4/19/2012	PD	PD	stay
4/19/2012	PD	III	stay
4/20/2012	PD	PD	stay
4/21/2012	PD	PD	stay
4/22/2012	PD	PD	stay
4/23/2012	RP	RP	stay
4/24/2012	RP	RP	stay
4/25/2012	RP	RP	stay
4/25/2012	RC	RP	stay
4/26/2012	RC	PT	stay	or remove</SPAN>
4/26/2012	RC	RP	remove	or stay</SPAN>
4/26/2012	WT	PT	remove	or stay</SPAN>
4/26/2012	WT	RP	stay	or remove</SPAN>
4/27/2012	WT	RP	stay
4/28/2012	WT	RP	stay

<TBODY>
</TBODY>

The combination in the last column is either to select them all or all in the column #3 (i.e. 4/26 should be stay-remove-remove-stay or remove-stay-stay-remove).

I would like to thank you for going so deep in this issue.

Regards

 

[Leandrial]

H Horees

You are very welcome Im learning here and kinda figured out the best way to learn this stuff was just to solve as many challenges as possible

Just to clearify, you are happy with the code as it is now?? We dont need no more work on it?? (not sure if you want me to work another rule into it for the 04-26-2012 issue)


best regards

Leandrial

 

[horees]

H Horees

You are very welcome Im learning here and kinda figured out the best way to learn this stuff was just to solve as many challenges as possible

Just to clearify, you are happy with the code as it is now?? We dont need no more work on it?? (not sure if you want me to work another rule into it for the 04-26-2012 issue)


best regards

Leandrial

Leandrial, I Appreciate it, but it's still need to be corrected. in the previous data that I posted, the code failed to highlight 2 records on 4/10.
It also highlghted 2 records that shouldn't have been highlighted on 4/18 & 4/19.

would you be easy?

Thanks again!

 

[Leandrial]

Exactly wich records this it miss-read.... they must somehow have been outside the rules applied by the code.... i need to know how they are different to work that into the code... or if they are the same as the rest why the code skipped those.. can you highlight the rows in question???

 

[horees]

Exactly wich records this it miss-read.... they must somehow have been outside the rules applied by the code.... i need to know how they are different to work that into the code... or if they are the same as the rest why the code skipped those.. can you highlight the rows in question???

After it highlights the records, I get this message "Application Defined or object defined error" on this line "c = MyTable.Cells(a - 1, 1).Value".

in the below example:
rows # 24 & 25 shouldn't have been highlighted --->2 lines for 4/18 that should stay & 2 lines for 4/19 that should stay too
rows # 15 & 16 should have been highlighted---> 4 lines for 4/10 in which the first 2 lines should the similarity in the codes (TTT,TTT & III, III).

make sense?

Thank you

 

[horees]

H Horees

You are very welcome Im learning here and kinda figured out the best way to learn this stuff was just to solve as many challenges as possible

Just to clearify, you are happy with the code as it is now?? We dont need no more work on it?? (not sure if you want me to work another rule into it for the 04-26-2012 issue)


best regards

Leandrial

Hi Back

I was able to include that records that should have been highlighted by chaniging the condition to "If n >= 3" instead of "If n =3"

But I'm still not able to remove the records that were highlighted by mistake (last record in 4/18 & first record in 4/19).

Any brain storming about this?

Thanks