I first started this with VBA, and I have been advised to use PowerBI. However, I do not really no if my issue can be solved through powerBI.
I have a file which will be send on a recurring basis, with specific information which need to be grouped. I am trying to create a VBA macro to automate the file preparation and make it suitable for import in another tool.
I have about 40 columns, but sometimes less. The place of columns sometimes change, I thus cannot refer to column place (A, B, C...) I have to look for the column name.
Also, I have to look for some specific column. If these columns have data, I need to add specific element for each cases to the previously concatenated columns.
I am providing sample code below
Here is a dummy data description:
| Client | ID | Info Superman | Info Product type | Spec 1 | Spec 2 | Spec a | Spec b | Info costumer type | Info facility type | Geography |
When neither "spec 1", "spec 2", "Spec a" or "Spec b" have values I need to group data in the following way (including headers): "Client"+"Superman"+Bravo+"Info costumer type"+Info facility type" i.e ClientSpermanBravoInfo
If there is a value for Spec 1 or 2 : "Client""Superman"AlphaBravo"Info costumer type"Info facility type"
If there is a value for Spec 1 or 2 and Spec a and b: "Client""Superman"AlphaAlphaBravo"Info costumer type"Info facility type".
So far, I have the following VBA code, but using PowerBI would provide a simpler and more elegant solution. I have been looking at resources close to that one for example : https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-april-update-feature-summary/ in order to query data in specific way, and create columns based on specific conditions, but the I cannot find the solution.
Dim data
data = .Resize(.Rows.Count - 1).Offset(1).Value
End With
Dim result As Dictionary
Set result = New Dictionary
Dim i
For i = 1 To UBound(data, 1)
Select Case True
Case _
data(i, headers("Costumer")) = "" Or _
data(i, headers("Zone ")) = "" Or _
MsgBox "Empty row"
Exit For
Case _
data(i, headers("Spec A")) = "" And _
data(i, headers("Spec B")) = "" And _
data(i, headers("Spec_C ")) = "" And _
data(i, headers("Spec_D ")) = ""
result(result.Count) = _
q & "Costumer" & data(i, headers("Costumer”)) & _
q & "Alpha" & _
q & "Zone" & data(i, headers("Zone")) & _
q
Case _
data(i, headers("Spec_1")) = "" And _
data(i, headers("Spec_2")) = "" And _
data(i, headers("Spec_3")) = "" And _
data(i, headers("Spec_4")) = "" And _
data(i, headers("Spec_5")) = "" And _
data(i, headers("Spec_6")) = "" And _
data(i, headers("Spec_7")) = ""
result(result.Count) = _
q & "Costumer" & data(i, headers("Costumer")) & _
q & "Alphabet" & _
q & "Zone" & data(i, headers("Zone")) & _
q
Case Else
result(result.Count) = _
q & " Costumer" & data(i, headers("Costumer ")) &
q & "AlphabetAlpha" & _
q & " Zone " & data(i, headers("Zone")) & _
q
End Select
Next
' output result data to sheet 2
If result.Count = 0 Then
MsgBox "No result data for output"
Exit Sub
End If
With ThisWorkbook.Sheets(2)
.Cells.Delete
.Cells(1, 1).Resize(result.Count).Value = _
WorksheetFunction.Transpose(result.Items())
End With
MsgBox "Completed"
Sub TralaNome()
Const q = """"
' get source data table from sheet 1
With ThisWorkbook.Sheets(1).Cells(1, 1).CurrentRegion
' check if data exists
If .Rows.Count < 2 Or .Columns.Count < 2 Then
MsgBox "No data table"
Exit Sub
End If
' retrieve headers name and column numbers dictionary
Dim headers As Dictionary
Set headers = New Dictionary
Dim headCell
For Each headCell In .Rows(1).Cells
headers(headCell.Value) = headers.Count + 1
Next
' check mandatory headers
For Each headCell In Array(("Costumer", "ID", "Zone“, "Product Quali", "Spec A", "Spec B", "Spec_C", "Spec_D", "Spec_1", " Spec_2", " Spec_3", " Spec_4", " Spec_5", " Spec_6", " Spec_7", "Chiavetta", "Tipo_di _prodotto", "Unicorno_Cioccolato", “cacao tree“)
If Not headers.Exists(headCell) Then
MsgBox "Header '" & headCell & "' doesn't exists"
Exit Sub
End If
Next
Dim data
' retrieve table data
data = .Resize(.Rows.Count - 1).Offset(1).Value
End With
' process each row in table data
Dim result As Dictionary
Set result = New Dictionary
Dim i
For i = 1 To UBound(data, 1)
MsgBox "Empty row"
Exit For
result(result.Count) = _
q & "ID " & data(i, headers("ID ")) & _
q & " Tipo_di _prodotto " & data(i, headers("Tipo_di _prodotto")) & _
q & " Unicorno_Cioccolato " & data(i, headers("Unicorno_Cioccolato")) & _
q & " cacao tree " & data(i, headers("cacao tree "))&_qEnd Select
Next
' output result data to sheet 2
If result.Count = 0 Then
MsgBox "No result data for output"
Exit Sub
End If
With ThisWorkbook.Sheets(2)
.Cells.Delete
.Cells(1, 1).Resize(result.Count).Value = _
WorksheetFunction.Transpose(result.Items())
End With
MsgBox "Completed"
End Sub
Sub NewColumnNames()
Dim OrignialColumnNames As String
Dim a As Integer
Dim NewColumnNames As Integer
Spec A = Amber A
Spec_B = Amber B
Spec_C = Amber C
Spec_D = Amber D
Spec_1 = Zio_1
Spec_2 = Zio_2
Spec_3 = Zio_3
Spec_4 = Zio_4
Spec_5 = Zio_5
Spec_6 = Zio_6
Spec_7 = Zio_7
Noup_Start = Mip_F
Noup_End = Nip_D
Snouba = Snup_N
SnipChocolat = Choco_F
End Sub
