Analyze Data with VBA and Create Specific Report

How to analyze data with VBA and create a specific report based on an interview question.

The user gets a set of data during an interview and the candidate is expected to create a report as shown in the images below:

 

raw data
raw data
specific report
specific report

Watch the video below before studying the VBA code:

Watch this video on YouTube.

Here’s the complete VBA code to create the specific report:

Sub getData()
Dim lastrow As Long, lastcolumn As Long
Dim countamaar As Long, countharry As Long, countramesh As Long
lastrow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
lastcolumn = Sheets(“Sheet1”).Cells(1, Columns.Count).End(xlToLeft).Column

Range(“I1:K1000”).Clear

ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Add Key:=Range(“A2”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(“Sheet1”).Sort
.SetRange Range(Cells(2, 1), Cells(lastrow, lastcolumn))
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

analyzeData

End Sub

Sub analyzeData()
Dim i As Long, lastrow As Long
Dim countamaar As Long, countharry As Long, countramesh As Long
Dim totsalesamaar As Long, totsalesharry As Long, totsalesramesh As Long
lastrow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
countamaar = 0
countharry = 0
countramesh = 0
totsalesamaar = 0
totsalesharry = 0
totsalesramesh = 0

For i = 2 To lastrow
If Cells(i, 1) = “Amaar” Then
countamaar = countamaar + 1
totsalesamaar = totsalesamaar + Cells(i, 5)
If countamaar = 1 Then
Cells(i, 9) = Cells(i, 1)
End If
Cells(i, 10) = Cells(i, 5)

ElseIf Cells(i, 1) = “Harry” Then
countharry = countharry + 1
totsalesharry = totsalesharry + Cells(i, 5)
If countharry = 1 Then
Cells(i, 9) = Cells(i, 1)
End If
Cells(i, 10) = Cells(i, 5)
ElseIf Cells(i, 1) = “Ramesh” Then
countramesh = countramesh + 1
totsalesramesh = totsalesramesh + Cells(i, 5)
If countramesh = 1 Then
Cells(i, 9) = Cells(i, 1)
End If
Cells(i, 10) = Cells(i, 5)

End If

Next i
Cells(countamaar + 1, 11) = totsalesamaar
Cells(countamaar + countharry + 1, 11) = totsalesharry
Cells(countamaar + countharry + countramesh + 1, 11) = totsalesramesh
Range(“I1”) = “Sales Person”
Range(“I1”).Font.Bold = True
Range(“J1”) = “Amount”
Range(“J1”).Font.Bold = True
Range(“K1”) = “Total Sales”
Range(“K1”).Font.Bold = True
End Sub

Further reading:

Formatting Data in Excel Using VBA

Bank Reconciliation Using Excel VBA – nested for loop

Nested If Function Using VBA

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

One thought on “Analyze Data with VBA and Create Specific Report”

Leave a Reply

Your email address will not be published. Required fields are marked *