Sleeper: How can I automate arranging cell values by repeating and transposing values

[User_Renee]

Hi all,

First off, this community is so helpful and godsent. I'm grateful to be here.

I have one more pretty tough issue that I at least hope to discuss ideas to go about and I've decided to ask here since I've tried what I can think of from my side.
I've used some simple VBA myself to clean the data and narrow down it down by moving around some columns. So the data is still similar to how it is formatted based on its raw form where I have an item and several rows of descriptions along with it. I'll attach an image of an example too of what I have in "From" and want I hope to achieve in "To" (on another sheet). I'll always learn bits to apply to my own.

What I'm trying to figure out a logic around is, for every value in Column A, there's a row of descriptions from Column B to F.
So I would like to transpose Column B to F's data, and then duplicate Column A's value respective to these rows of descriptions previously.

imagex.jpg

[georgiboy]

Maybe with some arrays as below:

Sub test()    
    Dim rng As Range, var As Variant, oVar As Variant, x As Long, y As Long, z As Long
    
    Set rng = Sheet1.UsedRange
    Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)
    var = rng.Value
    ReDim oVar((UBound(var)) * (UBound(var, 2) - 1) - 1, 1)
    
    For x = 1 To UBound(var)
        For y = 2 To UBound(var, 2)
            oVar(z, 0) = var(x, 1)
            oVar(z, 1) = var(x, y)
            z = z + 1
        Next y
    Next x
    
    Sheet2.Range("A2").Resize(UBound(oVar) + 1, UBound(oVar, 2) + 1) = oVar
End Sub

[Aussiebear]

@georgiboy. I have a question. I notice that initially you dim oVar as Variant and almost immediately redim oVar as multi type variant Is there a way around this?

[georgiboy]

Yes, we can omit the first Dim - I get carried away with declaring at times. I create my dim's before I write the code sometimes - as I did with this code.

Sub test()    
    Dim rng As Range, var As Variant, x As Long, y As Long, z As Long
    
    Set rng = Sheet1.UsedRange
    Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)
    var = rng.Value
    ReDim oVar((UBound(var)) * (UBound(var, 2) - 1) - 1, 1)
    
    For x = 1 To UBound(var)
        For y = 2 To UBound(var, 2)
            oVar(z, 0) = var(x, 1)
            oVar(z, 1) = var(x, y)
            z = z + 1
        Next y
    Next x
    
    Sheet2.Range("A2").Resize(UBound(oVar) + 1, UBound(oVar, 2) + 1) = oVar
End Sub

may as well share the file I was playing with for reference, attached.

[User_Renee]

Thank you for saving me again. I'm googling up UBound to learn from your code and played around with the numerical settings you have in your VBA code to see what lines of codes affect what. Hope you don't mind me asking one more favor for help on:
If I have several items to repeat/duplicate in according to the number of descriptions, shall I modify from the Ubound function, such as Ubound(var,2) for two "Items" (ex: Book, Children)?

I'm learning that the value that y starts with is the row to start transposing. So I think for 2 "Items" that I'll copy for each transposed "description". I'm still playing around and dissecting it to learn from your aid and thank you again

Yes, we can omit the first Dim - I get carried away with declaring at times. I create my dim's before I write the code sometimes - as I did with this code.

Sub test()    
    Dim rng As Range, var As Variant, x As Long, y As Long, z As Long
    
    Set rng = Sheet1.UsedRange
    Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)
    var = rng.Value
    ReDim oVar((UBound(var)) * (UBound(var, 2) - 1) - 1, 1)
    
    For x = 1 To UBound(var)
        For y = 2 To UBound(var, 2)
            oVar(z, 0) = var(x, 1)
            oVar(z, 1) = var(x, y)
            z = z + 1
        Next y
    Next x
    
    Sheet2.Range("A2").Resize(UBound(oVar) + 1, UBound(oVar, 2) + 1) = oVar
End Sub

may as well share the file I was playing with for reference, attached.

[georgiboy]

If I have several items to repeat/duplicate in according to the number of descriptions, shall I modify from the Ubound function, such as Ubound(var,2) for two "Items" (ex: Book, Children)?

Not sure I fully understand, if you add more rows and more columns to the data the code should pick that up and transpose the new data.

I have just noticed an error in the sizing of the output array - I will provide fresh code below which I have annotated to help understand (in my words) what is going on.

This line:

ReDim oVar((UBound(var)) * (UBound(var, 2) - 1) - 1, 1)

Should be:

ReDim oVar(UBound(var) * UBound(var, 2) - 1, 1)

Annotated code below:

Sub test()    
   Dim rng As Range, var As Variant, x As Long, y As Long, z As Long
    
    Set rng = Sheet1.UsedRange ' set rng to be the used range of the sheet
    Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1) ' reset rng to be the same range but without header
    var = rng.Value ' set var to be the values in rng, rows/ columns
    
    
    ' resize the output array to the size of the new data, rows * columns
    ' UBound(var) = var rows
    ' UBound(var, 2) = var columns
    ' the -1 part is becuase the var array starts at 1 and the output array starts at 0
    ' the 1 at the end is the amount of columns in the output array, as oVar starts at 0 the 1 means two columns
    ' Rows: (Rows * columns)-1     Columns:1
    ReDim oVar(UBound(var) * UBound(var, 2) - 1, 1)
    
    For x = 1 To UBound(var) ' loop through var rows (x)
        For y = 2 To UBound(var, 2) ' loop through var columns (y)
            ' z = 0 the first time we get to this line, z is the output array row number, 0 is the column number
            ' var(x,1) is the item from the var array
            oVar(z, 0) = var(x, 1)
            ' the next line writes to the second column of the output array (1)
            ' this is where the (y) comes into play
            ' it loops through the columns (y) of var (descriptions) and writes them to the rows (z) of oVar
            oVar(z, 1) = var(x, y)
            z = z + 1 ' increment z by one so the next record is put into the next row of oVar
        Next y ' next var column
    Next x ' next var row
    
    ' next we resize range A2 on sheet2 to be the same size as oVar and then write oVar to that range
    Sheet2.Range("A2").Resize(UBound(oVar) + 1, UBound(oVar, 2) + 1) = oVar
End Sub

Hope this helps

[snb]

First: remove 'Option Explicit'
Then run the unpivoting macro:

Sub M_snb()
  sn = Sheet1.Cells(1).CurrentRegion
  ReDim sp(UBound(sn) * (UBound(sn, 2) - 1), 1)
    
  For j = 4 To UBound(sp) - 1
    y = j \ (UBound(sn, 2) - 1) + 1
    sp(j - 4, 0) = sn(y, 1)
    sp(j - 4, 1) = sn(y, j Mod (UBound(sn, 2) - 1) + 2)
  Next
    
  Sheet1.Cells(1, 10).Resize(UBound(sp), 2) = sp
End Sub

[Aussiebear]

Actually I would recommend you do not remove Option Explicit. Option explicit forces you to dim your variables correctly. snb has the skills to define code without the use of Option Explicit but a review of the code he supplies generally fails.

[User_Renee]

Thanks for guiding me on this and the annotated version is helping me learn ahead as well. It's definitely more than I could ask for. I'll study up the VBA codes and learn from it.
Thank you again georgiboy for your time and you're helping me heaps on transitioning from a Python normie into Excel VBA much comfortably, it really means a lot.