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

    This topic contains 7 replies, has 3 voices, and was last updated by  zeddy 3 weeks, 5 days ago.

    • 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

        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.

    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.