vba code to transpose dynamic ranges of data

lizzie5 · Feb 17th 2012

Hi – I’m really hoping someone can help me with some code to display data in the format I’m looking for. I’m still learning about vba code and I’ve been stuck trying to find a solution, as the ranges in my data will change from week to week.

I have attached my data sample here. I have a range of values in column H that will change in my future data samples, so the code has to be based on the number of unique values in this dynamic range. The values in column A are also unique, they are listed vertically to correspond with each unique value in column H.

MY DESIRED GOAL: I am trying to have the vertical data in columns A, H-J output horizontally based on my dynamic range in row H. I used a formula to calculate the number of unique values in column H, but I still need to figure out how to make the formula dynamic (the formula is located in cell L1).

I created an example of what I want the data to look like in the end, you can see this to the right of column L. I have a grid where the unique values from column H are listed across the top starting in cell O1. Underneath that, there are two horizontal rows for each value in column A, one row is hard-coded titled “weekend” and the second row is hard-coded titled “weekday”. I used two different colors in attempt to better illustrate. The data from columns I-J should output horizontally in this grid, starting in cell O2 in my example. Once the first two rows of data have been populated in the grid, the code needs to know to move down to begin the next loop (i.e. move to O4-O5 to work on the data for the next unique value in column A, which starts in row 22 in my sample). This needs to continue until all rows (i.e. in my sample there are 1121 rows of data, but the number of rows will be dynamic in future data) have been outputted into the grid. The biggest problem I have is that this data sample will change from week to week, the only thing constant is that I know what type of data will be in each column, but the number of unique values in column H will always change.

Note: it doesn’t matter to me if the final data is outputted on the same tab like I have it now, or if it’s on a new tab.

CODE: I don’t have much code at this point since I’m not quite sure from what angle to come at this with, I just have a few very basic samples that I have come up with so far. Here is some static code for getting the values for O2-O3 based on the values in I2 and J2; this is just a model to work off of to build out the grid I need, again I need to figure out how to make it dynamic:

Code

'if it's a weekday event
    If Range("I2") = "No" Then
    Range("O2").Value = "No"
    End If
    If Range("I2") = "No" And Range("J2") = "Yes" Then
    Range("O3").Value = "Yes"
    End If
    If Range("I2") = "No" And Range("J2") = "No" Then
    Range("O3").Value = "No"
    End If
   
    'if it's a weekend event
    If Range("I2") = "Yes" Then
    Range("O3").Value = "No"
    End If
    If Range("I2") = "Yes" And Range("J2") = "Yes" Then
    Range("O2").Value = "Yes"
    End If
    If Range("I2") = "Yes" And Range("J2") = "No" Then
    Range("O2").Value = "No"
    End If

Display More

I also have some code to transpose the number of unique values listed in column H to list them horizontally starting in O1, but my formula in L1 somehow needs to be modified to be dynamic.

Code

Dim iNumEvents As Integer
 
    iNumEvents = Range("L1") + 1
   
    Range(Cells(2, 8), Cells(iNumEvents, 8)).Select
    Selection.Copy
    Range("O1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
    Range("O1").Select

Any help would be truly appreciated. This is my first time posting so please forgive anything I may have overlooked.

jindon · Feb 17th 2012

Re: vba code to transpose dynamic ranges of data

try

Code

Sub test()
Dim a, i As Long, b(), n As Long, t As Long
Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = 1
a = Range("a1").CurrentRegion.Resize(, 10).Value
ReDim b(1 To UBound(a, 1), 1 To 100)
n = 1: t = 2
With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For i = 2 To UBound(a, 1)
        If Not .exists(a(i, 1)) Then
            n = n + 2
            b(n - 1, 1) = a(i, 1)
            b(n, 1) = a(i, 1)
            b(n - 1, 2) = "weekend"
            b(n, 2) = "weekday"
            .Item(a(i, 1)) = n
        End If
        If Not dic.exists(a(i, 8)) Then
            t = t + 1
            b(1, t) = a(i, 8)
            dic(a(i, 8)) = t
        End If
        b(.Item(a(i, 1)) - 1, dic(a(i, 8))) = a(i, 9)
        b(.Item(a(i, 1)), dic(a(i, 8))) = a(i, 10)
    Next
End With
Columns("m").Resize(, 100).ClearContents
With Range("m1").Resize(n, t)
    .Value = b
    .Columns.AutoFit
End With
End Sub

Display More

lizzie5 · Feb 17th 2012

Re: vba code to transpose dynamic ranges of data

Hi jindon - wow, thanks for your help! So far I do see something that isn't working correctly though. It seems as though anything with a "yes" in column "I" isn't outputting in the grid properly.

Example 1: see the first discount_key from column A ("C151P2050B5378R"). After running the code - in the output grid see performance_code "EUC1118E" - the weekday & weekend output BOTH say "yes". There should never be a time when both the weekend and weekday output rows say "yes" at the same time since that's contradictory. In this specific case, based on the values in columns I-J, the grid should say: weekend "yes" / weekday "no"

Example 2: see the discount_key "G3D0116". After running the code - in the output grid see performance_code "EUC1118E" - here both weekend and weekday say "yes". In this specific case, based on the values in columns I-J, the grid should say: wekeend "no" / weekday "no"

Are you able to help modify the code to fix this? Thanks again.

jindon · Feb 17th 2012

Re: vba code to transpose dynamic ranges of data

I thought ColumnJ(Applied) for weekday.

Quote

based on the values in columns I-J

Can you explain it with logically understandable format?

lizzie5 · Feb 17th 2012

Re: vba code to transpose dynamic ranges of data

Column I tells us if the performance_code is on a weekend or a weekday.

Column J tells us if the discount_key applies to that performance_code.

Here is some static code from my original post to help explain; I added verbal descriptions for each possible scenario:

Code

‘if value in column I is NO then it’s a weekday event, so put NO in the weekend rowIf Range("I2") = "No" Then
    Range("O2").Value = "No" 
End If
 
‘if value in column I is NO, then it’s a weekday event AND
‘if value in column J is YES, then it applies, so put YES in the weekday row
If Range("I2") = "No" And Range("J2") = "Yes" Then
    Range("O3").Value = "Yes" 
End If
 
‘if value in column I is NO, then it’s a weekday event AND
‘if value in column J is NO, then it does not apply, so put NO in the weekday row
If Range("I2") = "No" And Range("J2") = "No" Then
    Range("O3").Value = "No" 
End If
 
 
 
‘if value in column I is YES, then it’s a weekend event, so put NO in the weekday row
If Range("I2") = "Yes" Then
    Range("O3").Value = "No" 
End If
 
‘if value in column I is YES, then it’s a weekend event AND
‘if value in column J is YES, then it applies, so put YES in the weekend row
If Range("I2") = "Yes" And Range("J2") = "Yes" Then
    Range("O2").Value = "Yes" 
End If
 
‘if value in column I is YES, then it’s a weekend event AND
‘if value is column J is NO, then it does not apply, so put NO in the weekend row
If Range("I2") = "Yes" And Range("J2") = "No" Then
    Range("O2").Value = "No" 
End If

Display More

Sorryif that wasn't clear, does it make sense now?

jindon · Feb 17th 2012

Re: vba code to transpose dynamic ranges of data

If J = No, both No
If J = Yes and I = Yes, Yes in weekend, No in weekday
If J = No and I = Yes, No in weekend, Yes in weekday

Is this correct?

lizzie5 · Feb 17th 2012

Re: vba code to transpose dynamic ranges of data

Almost. See below with correction:

If J = No, both No
If J = Yes and I = Yes, Yes in weekend, No in weekday
If J = Yes and I = No, No in weekend, Yes in weekday

jindon · Feb 17th 2012

Re: vba code to transpose dynamic ranges of data

Yeah, of course.

Check this

Code

Sub test()
Dim a, i As Long, b(), n As Long, t As Long
Dim dic As Object
Dim weekEnd As String, weekDay As String
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = 1
a = Range("a1").CurrentRegion.Resize(, 10).Value
ReDim b(1 To UBound(a, 1), 1 To 100)
n = 1: t = 2
With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For i = 2 To UBound(a, 1)
        If Not .exists(a(i, 1)) Then
            n = n + 2
            b(n - 1, 1) = a(i, 1)
            b(n, 1) = a(i, 1)
            b(n - 1, 2) = "weekend"
            b(n, 2) = "weekday"
            .Item(a(i, 1)) = n
        End If
        If Not dic.exists(a(i, 8)) Then
            t = t + 1
            b(1, t) = a(i, 8)
            dic(a(i, 8)) = t
        End If
        weekDay = "No"
        weekEnd = "No"
        If a(i, 10) = "Yes" Then
            If a(i, 9) = "Yes" Then
                weekEnd = "Yes"
            Else
                weekDay = "Yes"
            End If
        End If
        b(.Item(a(i, 1)) - 1, dic(a(i, 8))) = weekEnd
        b(.Item(a(i, 1)), dic(a(i, 8))) = weekDay
    Next
End With
Columns("m").Resize(, 100).ClearContents
With Range("m1").Resize(n, t)
    .Value = b
    .Columns.AutoFit
End With
End Sub

Display More

lizzie5 · Feb 17th 2012

Re: vba code to transpose dynamic ranges of data

Jindon – thank you SO much! I can’t tell you how thankful I am for your quick and brilliant help!

I am going through and checking the output for accuracy, so far everything looks great and I haven’t seen any issues.

I’m not sure if you/other posters in the forum ever explain what a code actually is doing – I am just curious if you could share any insight on the code itself. I haven’t seen anything like that before and don’t fully understand it. I am 100% happy as long as it works, it would just be nice to know more about it. BUT if you don’t normally do that really don’t worry about it, I won’t expect a response on this question. Either way, THANK YOU!!!

jindon · Feb 18th 2012

Re: vba code to transpose dynamic ranges of data

I'm not good at doing this....
You can step through with LocalWindow open,
so that you can see what is going on within array

Code

Sub test() 
    Dim a, i As Long, b(), n As Long, t As Long 
    Dim dic As Object 
    Dim weekEnd As String, weekDay As String
    ' Set the reference to Dictionary Object
    ' for unique items in Col.H (sub Dicationary)
    Set dic = CreateObject("Scripting.Dictionary")
    ' Set its comparemode to non-case sensitive
    dic.CompareMode = 1
    ' Store data in variable a (array) to speed up the process
    ' This enbales to process data within memory
    a = Range("a1").CurrentRegion.Resize(, 10).Value
    ' prepare output array
    Redim b(1 To UBound(a, 1), 1 To 100)
    ' Initialise variables: n for row counter
    ' t for column counter
    n = 1: t = 2 
    ' Set the reference to Dictionary Object for main (Col.A)
    With CreateObject("Scripting.Dictionary") 
        .CompareMode = 1 
        For i = 2 To UBound(a, 1) ' Loop commence from row 2
            ' If Col.A newly appered
            If Not .exists(a(i, 1)) Then
                ' Increase row counter by 2 (for W-End and W-Day)
                n = n + 2
                ' Copy, not actually copying, Col.A from array a to array b
                b(n - 1, 1) = a(i, 1) 
                b(n, 1) = a(i, 1) 
                b(n - 1, 2) = "weekend" 
                b(n, 2) = "weekday"
                ' Store row reference (n) for unique item
                .Item(a(i, 1)) = n
            End If
            ' If Col.H newly appeared
            If Not dic.exists(a(i, 8)) Then
                ' Increase col counter by 1
                ' This is a column reference for unique Col.H in array b
                t = t + 1
                ' Write a header in array b that from array a col.H
                b(1, t) = a(i, 8) 
                ' Store Col reference to dic (sub Dictionary)
                dic(a(i, 8)) = t 
            End If
            ' Initialise weekDay, weekEnd to No
            weekDay = "No" 
            weekEnd = "No"
            ' Evaluate only if Col J = Yes
            ' No need for No, because both variable set to No
            If a(i, 10) = "Yes" Then 
                If a(i, 9) = "Yes" Then 
                    weekEnd = "Yes" 
                Else 
                    weekDay = "Yes" 
                End If 
            End If 
            b(.Item(a(i, 1)) - 1, dic(a(i, 8))) = weekEnd 
            b(.Item(a(i, 1)), dic(a(i, 8))) = weekDay 
        Next 
    End With
    ' Clear output range 
    Columns("m").Resize(, 100).ClearContents 
    ' Dump the data
    With Range("m1").Resize(n, t) 
        .Value = b 
        .Columns.AutoFit 
    End With 
End Sub

Display More

lizzie5 · Feb 18th 2012

Re: vba code to transpose dynamic ranges of data

Thank you!

vba code to transpose dynamic ranges of data

Participate now!

Share

Users Viewing This Thread