Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Macro to help match and sort data

  1. #1
    Join Date
    12th September 2017
    Posts
    3

    Question 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 Images Attached Images
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    13th September 2013
    Location
    Scotland
    Posts
    191

    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:

    VB:
    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:

    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    12th September 2017
    Posts
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    13th September 2013
    Location
    Scotland
    Posts
    191

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    12th September 2017
    Posts
    3

    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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 8
    Last Post: April 24th, 2014, 11:38
  2. Problem with macro to sort data end up deleting data
    By adw223 in forum Excel General
    Replies: 3
    Last Post: August 5th, 2013, 03:05
  3. Macro Code To Sort Data
    By jason2007 in forum Excel General
    Replies: 7
    Last Post: June 29th, 2008, 22:12
  4. Sort Data Macro
    By shab620 in forum Excel General
    Replies: 3
    Last Post: January 4th, 2007, 22:10
  5. Sort and Match Uneven Columns of Data
    By PictureMeRollin in forum Excel General
    Replies: 3
    Last Post: January 1st, 2005, 14:33

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno