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

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 *