Formula, vba, or logic to remove certain occurrences

horees · Jun 25, 2014

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

horees · Jun 26, 2014

Can vba do it?

Leandrial · Jun 26, 2014

horees said:
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>

Yes VBA can prop do what you need... but I still dont understand what the rules are, that determines if a row should have a 1 or a 0 as result... Why would x - zz give 1 and not x - ww... and do you only have 4 variables with the number of results that gives (2 time 1 and 2 times 0)...

Best regards

Leandrial

horees · Jun 26, 2014

Leandrial said:
Yes VBA can prop do what you need... but I still dont understand what the rules are, that determines if a row should have a 1 or a 0 as result... Why would x - zz give 1 and not x - ww... and do you only have 4 variables with the number of results that gives (2 time 1 and 2 times 0)...

Best regards

Leandrial

Thank you for your reply. Originally the problem came from having to query on 2 different tables that has date common in between, but not the codes (x,y,zz,ww). So for the same record (date), we can have 2 records in table 1, 2 records in tables 2 (may be the same records or may be different).
Hence, the query will always show 4 different combination of the 2 X 2 records in each table.

If the 2 records are matched in both reports, I'll get
XX XX
YY YY
XX YY
YY XX
I was able to keep the first 2 lines where they match

BUT in case the 2 records don't match in both tables, I'll get the following combinations
XX ZZ
XX WW
YY ZZ
YY WW

SO the intention to show the user that the 2 records in both tables don't match, so I want to show 2 records where those records can show the 2 records in each table but in 2 lines instead of 4 lines. SO I want to only show either (XX ZZ & YY WW) OR (XX WW & YYZZ)...SO 2 records in which they show the 4 codes but in the 2 lines instead of 4.

Did I illustrate it better this time?

Leandrial · Jun 26, 2014

Yes, i think im getting there...

Do you already have the data joined in a single table now, as shown in your original post??

horees · Jun 26, 2014

I query on the database using vba in an excel file, then dump the data into worksheet then highlight the discrepancies by conditional formatting. Are you asking for a sample?

Leandrial · Jun 26, 2014

Well sample would be very good

If the joined list looks as the one from your original post, and the list is a table and the only table on the sheet, then this code will remove the rows you dont want there..
It is very crude, I admit, and prop not exactly what you are looking for... for ex. it asumes that the table is always sortet as shown in your example... but im not sure that it is??

Code:

Sub RemoveRows()


Dim lo As ListObject
Dim RowCount As Integer




Set lo = ActiveSheet.ListObjects(1)


RowCount = Range(lo).Rows.Count


For a = RowCount To 4 Step -4
    
    lo.Range.Rows(a).delete
    lo.Range.Rows(a - 1).delete
    
Next


End Sub

Please let me c the sample and let me know what the code does that you dont like... best with actual example again

Best regards

Leandrial

PS. if you dont want the rows deleted, but just marked, it would be fairly easy to change the "delete" to a value written to a cell next to the last column of the table..

horees · Jun 26, 2014

Leandrial said:
Well sample would be very good

If the joined list looks as the one from your original post, and the list is a table and the only table on the sheet, then this code will remove the rows you dont want there..
It is very crude, I admit, and prop not exactly what you are looking for... for ex. it asumes that the table is always sortet as shown in your example... but im not sure that it is??

Code:

Sub RemoveRows() Dim lo As ListObject Dim RowCount As Integer Set lo = ActiveSheet.ListObjects(1) RowCount = Range(lo).Rows.Count For a = RowCount To 4 Step -4 lo.Range.Rows(a).delete lo.Range.Rows(a - 1).delete Next End Sub

Please let me c the sample and let me know what the code does that you dont like... best with actual example again

Best regards

Leandrial

PS. if you dont want the rows deleted, but just marked, it would be fairly easy to change the "delete" to a value written to a cell next to the last column of the table..

This code works well taking into consideration your assumptions (which isn't the real case).

The problem is that the records aren't always 4 (may be 1, 2, or 4).

Please find below combination of data that possibly happens each time.

10-Jun

AA

AA

stay</SPAN>

10-Jun

AA

BB

stay</SPAN>

11-Jun

AA

AA

stay</SPAN>

12-Jun

BB

BB

stay</SPAN>

13-Jun

DD

DD

stay</SPAN>

13-Jun

AA

DD

stay</SPAN>

14-Jun

AA

AA

stay</SPAN>

14-Jun

BB

BB

stay</SPAN>

14-Jun

AA

BB

remove</SPAN>

14-Jun

BB

AA

remove</SPAN>

15-Jun

ZZ

ZZ

stay</SPAN>

16-Jun

XX

ZZ

stay</SPAN>

16-Jun

XX

WW

remove</SPAN>

16-Jun

YY

ZZ

remove</SPAN>

16-Jun

YY

WW

stay</SPAN>

Does this help or make it more complicated?

Thanks in advance

Leandrial · Jun 26, 2014

It helps alot

The table is always sortet on the data column, right??

horees · Jun 26, 2014

Leandrial said:
It helps alot

The table is always sortet on the data column, right??

the first sort is the date, then the second sort is the by the matched codes (AA, AA or BB, BB) if there is matching. I guess the last sort is defaul alphabitically based on the code column after the date. I can change the sort if that helps!

Thanks again!

Leandrial · Jun 26, 2014

Hi again

This code should solve your problem... just remember to change sheet name and range name at the top where they are Set

Code:

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 > 0 Then
        
            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
                
                    MyTable.Rows(a + 3).Delete
                    MyTable.Rows(a + 2).Delete
                                    
                    n = 0
                
                Else
                
                    MyTable.Rows(a + 2).Delete
                    MyTable.Rows(a + 1).Delete
                
                    n = 0
                    
                End If
            
            End If
        
        End If
    End If


Next


End Sub

Let me know how it works for you

Best regards

Leandrial

Formula, vba, or logic to remove certain occurrences

horees

Board Regular

horees

Board Regular

Excel Facts

Leandrial

Board Regular

horees

Board Regular

Leandrial

Board Regular

horees

Board Regular

Leandrial

Board Regular

horees

Board Regular

Leandrial

Board Regular

horees

Board Regular

Leandrial

Board Regular

Similar threads

Forum statistics

Share this page

Formula, vba, or logic to remove certain occurrences

Board Regular

Board Regular

Excel Facts

Board Regular

Board Regular

Board Regular

Board Regular

Board Regular

Board Regular

Board Regular

Board Regular

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock