Data processing using VBA code.

Topic

New Reply

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.

Reply | Quote

Replies

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?

Reply | Quote

[shazzad.arla]

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 5 years, 5 months ago by shazzad.arla.
• This reply was modified 5 years, 5 months ago by shazzad.arla.

Reply | Quote

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

Reply | Quote

zeddy wrote:

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

Reply | Quote

shazzad.arla wrote:

The range of data is not always same and mostly I need to insert some more columns in pivot table which is not possible.

I’ve had some success with adding columns to pivot tables. These pages may give some help in tweaking the pivot table design:
How to Add a Column in a Pivot Table (WikiHow)
Design the layout and format of a PivotTable (MS)

1 user thanked author for this post.

zeddy

Reply | Quote

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

1 user thanked author for this post.

shazzad.arla

Reply | Quote

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.

shazzad.arla

Reply | Quote