vba code to transpose a dynamic range of data to a new worksheet

lizzie5 · Feb 22nd 2012

Hi, I need help with code for a dynamic range of data. I thought I might be able to figure out something based on help I recently received for a similar issue, but this data set is different I haven't figured out how to solve this one.

I have attached my data sample here. I have a range of values in columns R and S that will change in my future data samples, so the code has to be based on the number of values in this dynamic range. The values in columns R-S are prices, and they are listed in decreasing face value. OR – a second option to base the dynamic range off of are the values in column A; this value is vertically listed repeatedly for as many prices listed in columns R-S. In my current data set attached, there are 7 prices in my dynamic range.

MY DESIRED GOAL: I need the vertical data in columns A-B, R-T to output horizontally onto the “EIS” tab based on the dynamic range. See “EIS” tab in my attachment for an example of what I want the data to look like in the end. My example output uses the first two items from the “Set 1” tab; I used a different color for each item only to better illustrate the sequence. The “discount_key” and “qualifier” need to be listed in columns A-B, respectively. In adjacent cells in column C, the words “applies”, “weekend”, and “weekday” should be hard-coded. In adjacent cells beginning in column D, the price range information should be outputted horizontally. The first of the three rows of data for each “discount_key” should reference column T from the “Set 1” tab (i.e. “Yes” or “No” – which explains whether a discount applies to that price). The second row should output the range of prices for the weekend, coming from “Set 1” tab column S. The third row should output the range of prices for the weekday, coming from “Set 1” tab column R. The code will then go to the next “discount_key” and “qualifier” and repeat the same steps.

This needs to continue until all rows on the “Set 1” tab (i.e. in my sample there are 722 rows of data, but the number of rows will be dynamic in future data) have been outputted into the grid on the “EIS” tab. All data will change in my future samples (except the constant that I know what type of data will be in each column), but the output needs to be based on the dynamic price range (or the number of repeated values listed in column A – whatever is easier to use). If the dynamic range is based on the number of prices listed in columns R/S, it will need to be able to handle up to 24 values maximum.

I don’t have code to work from that applies directly to this issue. All I have at this point are some formulas I have in my attachment on the “Set 1” tab in cells Y2:AA2 to calculate how many values are in the dynamic range. The formula for the # of unique values located in Z2 needs to be dynamic though.

I hope I was able to explain what I’m looking for, please let me know if not. Any help would be GREATLY appreciated!! Thank you!

jindon · Feb 23rd 2012

Re: vba code to transpose a dynamic range of data to a new worksheet

Code

Sub test()
Dim i As Long, ii As Long, n As Long, LastR As Range
Application.ScreenUpdating = False
With Sheets("Set 1")
    For i = 2 To .Range("a" & Rows.Count).End(xlUp).Row Step 7
        Set LastR = Sheets("EIS").Cells(1)
        If Not IsEmpty(LastR) Then
            Set LastR = Sheets("EIS").Range("a" & Rows.Count).End(xlUp)(2)
        End If
        .Cells(i, 1).Resize(3, 2).Copy LastR
        LastR(, 3).Resize(3).Value = [{"applies";"weekend";"weekday"}]
        For ii = 20 To 18 Step -1
            .Cells(i, ii).Resize(7).Copy
            LastR(21 - ii, 4).PasteSpecial Transpose:=True
        Next
    Next
End With
Application.ScreenUpdating = True
End Sub

Display More

lizzie5 · Feb 23rd 2012

Re: vba code to transpose a dynamic range of data to a new worksheet

Hi jindon - thanks for your help!!! I tried your code on the data sample I sent you and it works great, HOWEVER - it doesn't seem to be dynamic as it doesn't work properly for a different data set. I attached a different data set for a second sample. In the previous sample, the range happened to be 7, but that range should be dynamic. In my second sample I attached here, the range happens to be 4.

When I try running the test code you provided on this second set of data the output is wrong. It seems to still go out to 7 columns, and the data outputted in columns A-B isn't accurate as well (it should be listed in groups of 3, one each for the "applies", "weekend", and "weekday" listed in the corresponding rows).

Does that make sense? Hopefully my attachment will help as well. Are you able to modify the code to account for a dynamic range?

jindon · Feb 23rd 2012

Re: vba code to transpose a dynamic range of data to a new worksheet

Code

Sub test()
    Dim i As Long, ii As Long, n As Long, LastR As Range
    Const mySize As Long = 4  '<- change
    Application.ScreenUpdating = False
    With Sheets("Set 1")
        For i = 2 To .Range("a" & Rows.Count).End(xlUp).Row Step mySize
            Set LastR = Sheets("EIS").Cells(1)
            If Not IsEmpty(LastR) Then
                Set LastR = Sheets("EIS").Range("a" & Rows.Count).End(xlUp)(2)
            End If
            .Cells(i, 1).Resize(3, 2).Copy LastR
            LastR(, 3).Resize(3).Value = [{"applies";"weekend";"weekday"}]
            For ii = 20 To 18 Step -1
                .Cells(i, ii).Resize(mySize).Copy
                LastR(21 - ii, 4).PasteSpecial Transpose:=True
            Next
        Next
    End With
    Application.ScreenUpdating = True
End Sub

Display More

lizzie5 · Feb 24th 2012

Re: vba code to transpose a dynamic range of data to a new worksheet

Hi, I notice that you wrote "change" to the value for the range. Is it impossible to make it a dynamic range? The users that will be running this code will not have the knowledge to go in and update the code, I won't be able to have people go in and modify that number.

If it's not possible to make the range dynamic within the code, is it possible to have it reference a cell within the worksheet (such as cell AA2 from the "Set 1" tab). I have a formula in that cell to calculate the number in that range that seems to work, can that be used within the code so we can avoid changing the code itself every time you want to run it?

Thanks!

jindon · Feb 24th 2012

Re: vba code to transpose a dynamic range of data to a new worksheet

Code

Sub test()
Dim a, i As Long, ii As Long, w(), y, n As Long
With Sheets("set 1")
    a = .Range("a1", .Cells.SpecialCells(11)).Value
End With
With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For i = 2 To UBound(a, 1)
        If Not .exists(a(i, 1)) Then
            ReDim w(1 To 4, 1 To 3)
            For ii = 1 To 3
                w(ii, 1) = a(i, 1)
                w(ii, 2) = a(i, 2)
                w(ii, 3) = Choose(ii, "applies", "weekend", "weekday")
            Next
            w(4, 1) = 3
            .Item(a(i, 1)) = w
        End If
        w = .Item(a(i, 1))
        w(4, 1) = w(4, 1) + 1
        ReDim Preserve w(1 To 4, 1 To w(4, 1))
        For ii = 20 To 18 Step -1
            w(21 - ii, w(4, 1)) = a(i, ii)
        Next
        .Item(a(i, 1)) = w
    Next
    y = .items
End With
With Sheets("EIS").Cells(1)
    .CurrentRegion.ClearContents
    For i = 0 To UBound(y)
        .Offset(n).Resize(3, UBound(y(i), 2)).Value = y(i)
        n = n + 3
    Next
End With
End Sub

Display More

lizzie5 · Feb 24th 2012

Re: vba code to transpose a dynamic range of data to a new worksheet

Thank you. So far it looks good but I will check it in full detail.

What is the difference in this one - is it referencing anything in the worksheet or were you able to modify the code? Just wondering if there are any assumptions or things I should be aware of in the code, I don't fully understand all of it. Thanks!

jindon · Feb 24th 2012

Re: vba code to transpose a dynamic range of data to a new worksheet

It creates the list according to the number of records for each unique items in col.A

So, if number of records differ from each item, it create different number of columns.
Data in col.A doesn't need to be grouped in a block of range (no need to be sorted).

lizzie5 · Feb 24th 2012

Re: vba code to transpose a dynamic range of data to a new worksheet

Perfect! Thank you SO much for all of your help, I really appreciate it

vba code to transpose a dynamic range of data to a new worksheet

Participate now!

Share

Users Viewing This Thread