Luking for a VBA code to pull data

[faizumarali]

I have an excel file which contains 7 columns and 212708 rows


The data looks as shown below

REC PIN_NO REF_NO T AMT DATE TIME
SIF 10015026737994 110000689043 C 1262.00 2010-11-23 10:37:16
SIF 10015026737994 110000828303 C 1181.00 2010-12-21 11:18:26
DIF 10015026737994 210001537302 D 1181.00 2010-12-22 13:43:41
SIF 10015026737994 111000104330 C 1195.00 2011-01-22 13:54:41
DIF 10015026737994 211000111528 D 1195.00 2011-01-24 10:22:00
SIF 10015026737994 111000272774 C 1148.00 2011-02-23 10:51:38
DIF 10015026737994 211000318043 D 1148.00 2011-02-26 12:49:46
SIF 30417018370966 110000126740 C 1100.00 2010-05-17 13:46:28
DIF 30417018370966 210000280421 D 1100.00 2010-05-17 17:43:35
SIF 30417018370966 110000185616 C 1100.00 2010-06-22 11:32:43
DIF 30417018370966 210000379506 D 1100.00 2010-06-23 11:29:08
SIF 30417018370966 110000248304 C 1100.00 2010-07-18 11:44:04
DIF 30417018370966 210000481198 D 1100.00 2010-07-21 16:19:42
SIF 30417018370966 110000369304 C 1100.00 2010-08-31 11:22:38
DIF 30417018370966 210000703331 D 1100.00 2010-09-14 15:37:36
SIF 30417018370966 110000440772 C 1100.00 2010-09-22 10:54:38
DIF 30417018370966 210000796880 D 1100.00 2010-09-26 15:39:54
SIF 30417018370966 110000546464 C 1100.00 2010-10-17 11:50:59
DIF 30417018370966 210001168612 D 1100.00 2010-10-18 11:26:54
SIF 30417018370966 110000689652 C 1100.00 2010-11-23 12:09:13
SIF 30417018370966 110000821443 C 1100.00 2010-12-19 12:27:16
DIF 30417018370966 210001537359 D 1100.00 2010-12-22 13:46:41
SIF 30417018370966 111000103982 C 1100.00 2011-01-22 13:06:40
DIF 30417018370966 211000111613 D 1100.00 2011-01-24 10:28:30
SIF 30417018370966 111000238113 C 1100.00 2011-02-13 12:18:26
DIF 30417018370966 211000293771 D 1100.00 2011-02-22 10:14:35


In the above data Column Rec shows either SIF or DIF.

Under each SIF records there should be DIF records. If not say under SIF record again SIF record is coming the SIF record details should be moved sheet2.

From the above example the below two records should be moved to sheet2


SIF 10015026737994 110000689043 C 1262.00 2010-11-23 10:37:16
SIF 30417018370966 110000689652 C 1100.00 2010-11-23 12:09:13


Please help

 

[Andrew Poulsom]

What have you tried? You need a backwards loop testing if the cell above any cell containing SIF also contains SIF. If it does cut the offending row and insert it at row 1 on Sheet2.

 

[MrKowz]

Give this a go:

Public Sub SplitSifDif()
Dim i       As Long, _
    LR      As Long
    
LR = Range("A" & Rows.Count).End(xlUp).row
Application.ScreenUpdating = False
For i = LR To 1 Step -1
    If Range("A" & i).Value = "SIF" And Range("A" & i + 1).Value = "SIF" Then
        Sheets("Sheet2").Rows(2).Insert Shift:=xlDown
        Rows(i).Cut Destination:=Sheets("Sheet2").Range("A2")
        Rows(i).Delete
    End If
Next i
Application.ScreenUpdating = True
End Sub

 

[faizumarali]

Thanks for the reply. I have tried with VBA CODE given by MrKowz but my PC stopped working.

As the data is huge like 212708 rows cause problem.

SIF 10015026737994 110000689043 C 1262.00 2010-11-23 10:37:16
SIF 10015026737994 110000828303 C 1181.00 2010-12-21 11:18:26
DIF 10015026737994 210001537302 D 1181.00 2010-12-22 13:43:41
SIF 10015026737994 111000104330 C 1195.00 2011-01-22 13:54:41
DIF 10015026737994 211000111528 D 1195.00 2011-01-24 10:22:00
SIF 10015026737994 111000272774 C 1148.00 2011-02-23 10:51:38
DIF 10015026737994 211000318043 D 1148.00 2011-02-26 12:49:46

From the above My task is in the sequential data if two SIF records comes i want to pick the first SIF records like

SIF 10015026737994 110000689043 C 1262.00 2010-11-23 10:37:16


to a different sheet or to a new file in any location for example C:\Report.xlsx

 

[MrKowz]

With that amount of data, it is going to take a while for the macro to run. I used a line of code that prevents the Excel screen from updating. The screen would appear frozen, but by preventing the screen from updating, it makes the macro run significantly faster. I have tested it on my end with your sample data and it works as intended.

In this updated code, I amended an indication of progression that you will see in the lower left of excel (in the status bar), showing how many rows it has left to check and a % of its overall progression.

Public Sub SplitSifDif()
Dim i       As Long, _
    LR      As Long
 
LR = Range("A" & Rows.Count).End(xlUp).row
Application.ScreenUpdating = False
For i = LR To 1 Step -1
    If Range("A" & i).Value = "SIF" And Range("A" & i + 1).Value = "SIF" Then
        Application.StatusBar = "Rows left to check: " & i & ". " & Format((LR - i) / LR, "0.00%") & " complete."
        Sheets("Sheet2").Rows(2).Insert Shift:=xlDown
        Rows(i).Cut Destination:=Sheets("Sheet2").Range("A2")
        Rows(i).Delete
    End If
Next i
With Application
    .ScreenUpdating = True
    .StatusBar = False
End With
End Sub

 

[faizumarali]

Thanks a lot MrKowz i'm getting the result but the source data that i have is a bit messy.

 

[MrKowz]

Thanks a lot MrKowz i'm getting the result but the source data that i have is a bit messy.

How so?

 

[faizumarali]

the code you have given to me was wht exactly i was luking for but from the extracted data the total amount to be reconciled is not matching. I have to get some more information regarding this than i will come back to you. Thanks a lot for helping me on this.