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:
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