• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

All sequences required from this range

shili12

Member
I want all sequences from the below see column First serial to Last Serial :-

#Transaction DateCompany NameCertificate TypeFirst Serial #Last Serial #Qty(Out/In)User NameAvailable Balancecomment
379​
04/01/2022 16:09​
T Insurance Company Ltd.Type B - Commercial VehicleB12071013B12071512
-500​
William K
I want all sequences
380​
04/01/2022 16:25​
T Insurance Company Ltd.Type B - Commercial VehicleB12063010B12063010
-1​
Insurance API
381​
04/01/2022 16:28​
T Insurance Company Ltd.Type B - Commercial VehicleB12063011B12063011
-1​
Insurance API
382​
04/01/2022 16:28​
Stephen TooType B - Commercial VehicleB12063012B12063038
-27​
Aggrey O
383​
04/01/2022 16:29​
R Insurance AgenciesType B - Commercial VehicleB12063039B12063048
-10​
Aggrey O
384​
04/01/2022 16:29​
K Insurance Agency LtdType B - Commercial VehicleB12063049B12063051
-3​
Aggrey O
385​
04/01/2022 16:30​
J Insurance AgencyType B - Commercial VehicleB12063052B12063065
-14​
Aggrey O
386​
04/01/2022 16:30​
H Insurance AgencyType B - Commercial VehicleB12063066B12063069
-4​
Aggrey O



the result should be a single column in the next sheet as displayed below, see attachment as well.
Output Column B
B12071013 T insurance company Ltd
B12071014 T insurance company Ltd
B12071015 T insurance company Ltd
B12071016 T insurance company Ltd
B12071017 T insurance company Ltd
B12071018 T insurance company Ltd
B12071019 T insurance company Ltd
B12071020 T insurance company Ltd
B12071021 T insurance company Ltd
B12071022 T insurance company Ltd
B12071023 T insurance company Ltd
B12071024 T insurance company Ltd
B12071025 T insurance company Ltd
B12071026 T insurance company Ltd
B12071027 T insurance company Ltd
and so on and so forth till the end.......
 

Attachments

  • New Microsoft Excel Worksheet (15).xlsx
    20 KB · Views: 3
Last edited:
As your explanation expected result does not match the attachment worksheet result so at least which one is the correct expected ?‼​
 
So according to your attachment a starter Excel basics VBA demonstration :​
Code:
Sub Demo1()
        Dim Rc As Range, Rd As Range, R&
        Sheets(2).UsedRange.Offset(1).Clear
    For Each Rc In Sheets(1).Range("E2", Sheets(1).[E1].End(xlDown))
        Set Rd = Sheets(2).Cells(Rows.Count, 1).End(xlUp)(2)
        Rc.Copy Rd
        L = 1 + Mid(Rc(1, 2), 2) - Mid(Rc, 2)
        If L > 1 Then Rd.AutoFill Rd.Resize(L)
    Next
        Set Rd = Nothing
End Sub
 
Like this. Right

Code:
Sub Demo1()

        Dim Rc As Range, Rd As Range, R&

        Sheets(2).UsedRange.Offset(1).Clear

    For Each Rc In Sheets(1).Range("E2", Sheets(1).[E1].End(xlDown))

        Set Rd = Sheets(2).Cells(Rows.Count, 1).End(xlUp)(2)

        Rc.Rd=Rc

        L = 1 + Mid(Rc(1, 2), 2) - Mid(Rc, 2)

        If L > 1 Then Rd.AutoFill Rd.Resize(L)

    Next

        Set Rd = Nothing

End Sub
 
i believe the data i have is so voluminous so i got this errors and also the no. of duplicates was also high, it went as far as row 989365:-
Maybe another solution can be found on a newer thread ?:rolleyes:
83300

83301
83302:rolleyes:
 
Maybe without any duplicate, did you try ?​
Or if you can save your workbook as binary format .xlsb and attach it here in order I can reproduce your issue …​
 
Back
Top