News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Data processing using VBA code.

    Posted on shazzad.arla Comment on the AskWoody Lounge
    Viewing 5 reply threads
    • Author
      Posts
      • #1953552 Reply
        shazzad.arla
        AskWoody Lounger

        I am facing some complexity in sorting and modeling data properly. in the attached sample file, i have raw data. I want to sort the raw data sheet in process sheet where House Name will be in A column and SKU name will be in 2nd Row.
        I am in need of a system which will pull the value matched with Cell A2 and Cell B1. It would be a great assistance if you suggest a suitable way.
        I think Macro would a great way since in main file, there will be around 2000 house name and 50 SKU name. Could you please help me to build Macro for the operation??

        Thanks in advance for your cordial support.

      • #1953669 Reply
        Kirsty
        Da Boss

        It appears your spreadsheet sample didn’t get uploaded. Perhaps you could post it in a reply?

        Which version of Excel are you using? Do you have access to pivot tables?

        • #1953721 Reply
          shazzad.arla
          AskWoody Lounger

          Data-Processing

          Hi Kirsty,

          I am not sure actually. It was supposed to be uploaded. However, I am trying again.

           

          Regards

          Shazzad

          • This reply was modified 9 months, 4 weeks ago by shazzad.arla.
          • This reply was modified 9 months, 4 weeks ago by shazzad.arla.
          Attachments:
      • #1953791 Reply
        zeddy
        AskWoody_MVP

        Hi Shazzad

        I think you might be looking for a Pivot Table to get the results you want (see attached file).

        A Pivot Table lets you choose what you want for your rows (house name), what you want for your colums (SKU), and what you want to see as data (e.g sum of Volume).

        zeddy

        Excel WCCU -Worksheet Critical Cell Unit

        Data-Processing-zeddy

        Attachments:
      • #1954041 Reply
        shazzad.arla
        AskWoody Lounger

        Hi Shazzad

        I think you might be looking for a Pivot Table to get the results you want (see attached file).

        A Pivot Table lets you choose what you want for your rows (house name), what you want for your colums (SKU), and what you want to see as data (e.g sum of Volume).

        zeddy

        Excel WCCU -Worksheet Critical Cell Unit

        Data-Processing-zeddy

        Hi Zeddy,

        I am fully agreed with you. Even I worked on the same way you suggested. But there is some problem faced after processing. The range of data is not always same and mostly I need to insert some more columns in pivot table which is not possible. And that is why I am preferring any simple VBA that will allow me to work in the extracted data.

         

        Shazzad

      • #1956095 Reply
        zeddy
        AskWoody_MVP

        Hi Shazzad

        Kirsty has given some great links. Very nice!

        If you don’t want to use pivot tables, you could use some array formulas to get your data volume totals for matching House Name with SKU.

        To make the array formulas easier to follow, my attached example file uses named ranges for use in the formulas. (This is an Excel2003 .xls file)Data-Processing-zeddy-1a

        I have added a button [Click me to Refresh] on the worksheet named [Process Sheet] and assigned a macro to it.

        You should be able to adjust the macro to suit your needs.

        If you need any further help, just ask.

        zeddy

        Excel Ready When You Are

        Data-Processing-zeddy-1a

        Attachments:
        1 user thanked author for this post.
      • #1957197 Reply
        zeddy
        AskWoody_MVP

        This is the code I used for those who don’t want to download the file:

        
        '***************************************************'****************************************
        ' AskWoody - Data processing using VBA code         v1a                         TOP OF MODULE
        ' Prepared for: shazzad.arla
        ' FILE : [Data-Processing-zeddy-1a.xls]             last updated: 18-SEP-2019          by: RZ
        '***************************************************'****************************************
        
        'The following routine is assigned to the button labelled..
        '[Click me to Refresh]
        '..on the sheet named [Process Sheet]
        
        Sub processData()                                   'v1a
        
        Sheets("Process Sheet").Select                      'start on THIS worksheet
        
        [a1].CurrentRegion.Offset(1).EntireRow.Delete       'delete all rows beneath heading row 1
        
        Application.ScreenUpdating = False                  'freeze display till ready
        
        Sheets("RawData").Select                            'switch to worksheet named [RawData]
        
        r = Cells(Rows.Count, "A").End(xlUp).Row            'use column [A] to find last data row
        
        Range("A2:A" & r).Name = "colA"                     'assign name to range; exclude heading
        Range("B2:B" & r).Name = "colB"                     'assign name to range; exclude heading
        Range("C2:C" & r).Name = "colC"                     'assign name to range; exclude heading
        
        Sheets("Process Sheet").Select                      'switch back to THIS worksheet
        
        '***********************************************
        'EXTRACT LIST OF UNIQUE HOUSE NAMES..
        '***********************************************
        Sheets("RawData").[a1].CurrentRegion.AdvancedFilter _
        Action:=xlFilterCopy, _
        CopyToRange:=[a1], _
        Unique:=True
        
        'find last row number for [Process Sheet]           'need this for formula-copying
        r = Cells(Rows.Count, "A").End(xlUp).Row            'use column [A] to find last data row
        
        'sort extracted list of House Name..
        [a1].Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes     'use ascending sort
        
        'place array-formula into cell [B2]
        'NOTE:
        'this array formula sums the colC Volume values where the colA matches the House Name
        'and the colB SKU matches the heading row cell.
        'We use relative-cell-references in the formula so that it can be copied to other cells..
        [b2].FormulaArray = "=SUM((colA=$A2)*(colB=B$1)*colC)"
        
        'now copy that array-formula from cell [B2] across to cells [C2:E2]
        [b2].Copy [c2:e2]                                   'copy formula to specified range
        
        'now we need to copy that row of array-formulas in row 2, down to the last row..
        temp = "b3:e" & r                                   'formula copy-to range; e.g. "B3:E12"
        [b2:e2].Copy Range(temp)                            'copy from source to destination range
        
        [a1].Select                                         'putcellpointer in tidy location
        Application.ScreenUpdating = True                   'refresh display before showing message
        '***********************************************
        'DISPLAY COMPLETION MESSAGE..
        '***********************************************
        saywhat = "all DONE!"                               'define message-box text
        boxtitle = "SHAZZAD DATA PROCESSING TOOL"           'define message-box heading
        btns = vbOKOnly + vbExclamation                     'define message-box buttons
        
        answer = MsgBox(saywhat, btns, boxtitle)            'display message box
        '***********************************************
        
        End Sub
        '***************************************************'****************************************
        
        

        zeddy

        Excel Manipulator

        1 user thanked author for this post.
    Viewing 5 reply threads

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Data processing using VBA code.

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.