(Solved) – Transposing dummy variables with a vba script

  • by
(solved)-–-transposing-dummy-variables-with-a-vba-script

I would like to transform the structure of an excel file so that I can import it into a system which needs it to be structured in a given way.

Here is a small extraction of the excel file. There is a combination of categorical variables such as Line of business and four dummy variables indicating which data categories are used in a given process – such as Customer.

| Process name | Line of business | Customer | Potential customer | Employee | Vendor |
|--------------|------------------|----------|--------------------|----------|--------|
| Ad campaign  | Marketing        | x        | x                  |          | x      |
| Payroll      | HR               |          |                    | x        | x      |

What I want is to change the structure so that a new row is created for each variation of the dummy variables, and with a Data category column that applies/transposes the relevant data category name. The desired output would look like this:

| Process name | Line of business | Data category      |
|--------------|------------------|--------------------|
| Ad campaign  | Marketing        | Customer           |
| Ad campaign  | Marketing        | Potential customer |
| Ad campaign  | Marketing        | Vendor             |
| Payroll      | HR               | Employee           |
| Payroll      | HR               | Vendor             |

What I have tried is making a COUNTIF statement that counts the number of “x” per row. I have then used a vba script that creates a new line with the process name for each variation of data categories.
Here is the code and the letters in the script refers to the columns in excel, so A is the Process name column and G is the COUNTIF column and it creates the n number of rows I need.

Sub KopyKat()
   Dim N As Long, i As Long, K As Long
   Dim v As String, kk As Long, m As Long
   N = Cells(Rows.Count, "G").End(xlUp).Row
   K = 1

   For i = 2 To N
      kk = Cells(i, "G").Value
      v = Cells(i, "A").Value
      For m = 1 To kk
         Cells(K   1, "H") = v
         K = K   1
      Next m
   Next i
End Sub

So that it goes from this:

| Process name | Line of business | Customer | Potential customer | Employee | Vendor | COUNTIF |
|--------------|------------------|----------|--------------------|----------|--------|---------|
| Ad campaign  | Marketing        | x        | x                  |          | x      | 3       |
| Payroll      | HR               |          |                    | x        | x      | 2       |

To this:

| Process name | Line of business | Customer | Potential customer | Employee | Vendor | COUNTIF | Process name_2 |
|--------------|------------------|----------|--------------------|----------|--------|---------|----------------|
| Ad campaign  | Marketing        | x        | x                  |          | x      | 3       | Ad campaign    |
| Payroll      | HR               |          |                    | x        | x      | 2       | Ad campaign    |
|              |                  |          |                    |          |        |         | Ad campaign    |
|              |                  |          |                    |          |        |         | Payroll        |
|              |                  |          |                    |          |        |         | Payroll        |

This is where my limited vba knowledge have taken me. I would like to change the code so that I get my desired output.

Thanks in advance!

Leave a Reply

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