No announcement yet.

Macro to help match and sort data

  • Filter
  • Time
  • Show
Clear All
new posts

  • Macro to help match and sort data

    Hi, I am looking for a formulae or a script which would help me execute this problem.

    Column B contains the name of the sample and column A contains the following breakdown of the sample but this data contains other sample informations.
    Every sample is Column B starts with a Character ID (A to Z) followed by a space and then "-". Example (B - or A - )
    The data which lists the breakdown of the sample in Column A will also start with the same character ID but can have a number attached to it (Number ID range A1-A99).

    In Column Output 1 - I want to extract the relevant sample breakdown data from Column A for the sample listed in column B.
    Example - If Sample started with the ID "B -", I want the ouput1 cell to contains all the sample data starting with "B and a number"

    In Column Output 2 - I want to copy the data from Output 1 but without the starting ID (example B1 or A1 or A99)
    Attached Files

  • #2
    Re: Macro to help match and sort data

    Try this out.

    You'll need to alter some references to suit your data setup.

    Module Code:

    Option Explicit
    'Handles the extraction of data
    Public Sub ExtractSampleList()
    Dim rngInput    As Range
    Dim rngSmpls    As Range
    Dim strCharCode As String
    Dim varWrkCell  As Variant
    Dim colSmpls    As Collection
    Dim clsOutputs  As OutputClass
    'Get the input and set the search range
    With wsInputSheet
        Set rngInput = .Range("SelectionInput")
        Set rngSmpls = .Range("SampleList")
    End With
    'Get the Single Letter Character Code
    strCharCode = Left(rngInput.Value2, 1)
    'Search the range where the samples are located
    Set colSmpls = New Collection
    For Each varWrkCell In rngSmpls
        'If the first character of the sample equals the single character code then extract the data
        If Left(varWrkCell.Value2, 1) = strCharCode Then
            'Set up new instance of class and construct it
            Set clsOutputs = New OutputClass
            Call clsOutputs.Construct(varWrkCell)
            'Add class instance to collection
            colSmpls.Add clsOutputs
        End If
    Next varWrkCell
    'Call the output routine (passing the collection of objects extracted
    Call OutputData(colSmpls)
    'Clear up objects
    Set rngInput = Nothing
    Set rngSmpls = Nothing
    Set varWrkCell = Nothing
    Set colSmpls = Nothing
    Set clsOutputs = Nothing
    End Sub
    Private Sub OutputData(ByRef colSmpls As Collection)
    Dim lngOutputRow    As Long: lngOutputRow = 1 'Assuming Header in row 1 so this will move to row 2 on entering the loop
    Dim lngChrCdCol     As Long: lngChrCdCol = 7
    Dim lngSmplCol      As Long: lngSmplCol = 8
    Dim clsOutputs      As OutputClass
    'Loop round each object in the collection and output
    With wsOutputSheet
        For Each clsOutputs In colSmpls
            lngOutputRow = lngOutputRow + 1
            .Cells(lngOutputRow, lngChrCdCol) = clsOutputs.CharacterCode
            .Cells(lngOutputRow, lngSmplCol) = clsOutputs.CharacterCode & " " & clsOutputs.SampleData
        Next clsOutputs
    End With
    End Sub

    Class Module Code:

    Option Explicit
    Private pStr_SampleData As String
    Private pStr_CharCode   As String
    Public Sub Construct(ByRef varInput As Variant)
    Dim varSplitString  As Variant
    varSplitString = Split(CStr(varInput.Value2), " ", 2)
    pStr_CharCode = varSplitString(0)
    pStr_SampleData = varSplitString(1)
    End Sub
    Public Property Get CharacterCode() As String
        CharacterCode = pStr_CharCode
    End Property
    Public Property Get SampleData() As String
        SampleData = pStr_SampleData
    End Property
    Class Module was also reamed to OutputClass.


    • #3
      Re: Macro to help match and sort data

      Thank you very much for your help.

      I have just started to learn VBA and therefore would not understand how to play with your script.

      Can I kindly request if you can apply the macro in my attached workbook so that I can execute it?

      Really appreciate all your help.


      • #4
        Re: Macro to help match and sort data

        Unfortunately I do most of my help waiting for things to run while I'm in the office and the firewall will stop me downloading your file.

        Essentially you need to take the code in the first box, create a module in your VBA project and paste it into that.

        Then create a Class Module within your VBA project and paste the contents of the second box into there.

        Then on the sheet where the user makes the selection I've created a named range called "SelectionInput".

        The Sample data that the code needs to check through is a named range called "SampleList".

        After that it's just the sheet references.

        If that still doesn't make sense, perhaps someone else could download the file and paste the code in and post it back for you.


        • #5

          Re: Macro to help match and sort data

          Thank you very much for trying to help. I have done what you have instructed but it seems that I am not doing everything that is required to run the script, therefore generating errors.

          Would request someone to help me by adding the script to the excel file so that I can execute it.