Please help to extract the data's from group data

[sampath]

Hello,

I have huge data like alphanumaric. I need to extract the particular data from that huge data. example data are mentioned here.

Input.
A B C D
121 MA 645 756
R212 365 7501 645 756
231 565 756 521 SE2222 - 751 SE22
SE2222 - MG RN SE2222 - 754 SE2222 - RG
RG-SE

Output.

A
SE2222 - MG
SE2222 - RG-SE
SE2222 - 754
SE2222 - RG

Sense "SE" and extracting that SE*************** and paste to another sheet.These kind of extracting i need for our project.

Kindly help me for the same.

Note : Herewith, I have attached the examples in excel file.

Thank with regards,
Sampath.S

 

[SirJB7]

Hi, sampath!

Give a look at the uploaded file. It uses 3 named ranges: SourceTable (fixed for input data), TargetList (dynamic for output data), ArgumentCell (search argument, e.g. SE). This is the code:

Option Explicit

Sub ExtractListFromTable()
    ' constants
    Const ksSourceWS = "Sheet1"
    Const ksSourceRange = "SourceTable"
    Const ksTargetWS = "Sheet2"
    Const ksTargetRange = "TargetList"
    Const ksArgument = "ArgumentCell"
    ' declarations
    Dim rngS As Range, rngT As Range, c As Range
    Dim vArgument As Variant
    Dim I As Long, A As String
    ' start
    Set rngS = Worksheets(ksSourceWS).Range(ksSourceRange)
    Set rngT = Worksheets(ksTargetWS).Range(ksTargetRange)
    vArgument = Worksheets(ksTargetWS).Range(ksArgument)
    With rngT
        If .Rows.Count > 1 Then Range(.Rows(2), .Rows(.Rows.Count)).ClearContents
    End With
    I = 1
    ' process
    With rngS
        Set c = .Find(vArgument, .Cells(1, 1), xlValues, xlPart, xlByColumns, , True)
        Do Until c Is Nothing
            ' save 1st found
            If A = "" Then A = c.Address
            ' add entry
            I = I + 1
            rngT.Cells(I, 1).Value = c.Value
            ' cycle
            Set c = .FindNext(c)
            ' check if restarted
            If A = c.Address Then Exit Do
        Loop
    End With
    ' end
    Set rngT = Nothing
    Set rngS = Nothing
    Beep
End Sub

Just advise if any issue.

Regards!

 

[sampath]

Hello SirJB 7,

Thanks for your reply, but how can i find the "SE" in this script. when i copy this script and paste to the macro area.

It shows error.

Please give any suggestion for the same.

Best regards,
Sampath.S

 

[SirJB7]

It uses 3 named ranges: SourceTable (fixed for input data), TargetList (dynamic for output data), ArgumentCell (search argument, e.g. SE).

Hi, sampath!
Had gone to the Name Manager you'd have seen the 3 named ranges definitions, there it says that ArgumentCell is located in worksheet Sheet2 at cell D1.
So if you just copied the code into your actual workbook, it won't work. You should either copy your data to the uploaded workbook or define the named ranges in your actual workbook, accordingly to your worksheets structure and names.
If you keep my original structure, and you want to test with other data selection, just go to Sheet2!D1 (yellow cell), type the search argument and run the macro.
Regards!