Convert Columns Data to Rows Data

[elbarauljr]

Hi everyone!

I have a spreadsheet to convert the (attached picture) in green to rows in yellow. Is there possible formula or code to create the remining data from rows 7 thru 24 to columns L thru V) ?

Here is steps example for manual input (to convert green row 2 into yellow rows 2, 3 and 4) :

1. Transpose not empty amounts in row 2 (columns D thru J) into column V
2. Combine column C2 value with row 1 values were amount is not empty and input in column P
3. Repeat column B2 value in column S for the not empty amounts in row 2

Workbook don't have specific name, just the opened and active spreadsheet.

Your help is greatly appreciated!

 

[alansidman]

Use Power Query (Get and Transform Data found on the Data Tab) to unpivot your data. You have posted a picture which I cannot manipulate. If you wish to see exact application, then reload your data using XL2BB. See my signature for instructions.

 

[DanteAmor]

Try this:

Sub Convert_Columns_To_Rows()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, n As Long
  
  a = Range("A1:J" & Range("A" & Rows.Count).End(3).Row).Value
  n = WorksheetFunction.Count(Range("D:J"))
  ReDim b(1 To n, 1 To 11)
  
  For i = 2 To UBound(a, 1)
    For j = 4 To UBound(a, 2)
      If a(i, j) <> "" Then
        k = k + 1
        b(k, 1) = a(i, 1)
        b(k, 5) = a(i, 3) + a(1, j)
        b(k, 8) = a(i, 2)
        b(k, 11) = a(i, j)
      End If
    Next
  Next
  
  Range("L2").Resize(k, UBound(b, 2)).Value = b
End Sub

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Convert Columns Data to Rows Data
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[elbarauljr]

Peter_SSs, you are correct. My computer only allowed me to attached a picture to this site, instead of the actual file. I thought a picture was not enough to convey the need. I requested the post to be deleted from the other site. Thanks

 

[Peter_SSs]

As I mentioned above, it is acceptable in this forum to cross-post, we just want links to the other posts so helpers can check if the question has been answered there if they want to.

In relation to providing sample data/results in this forum:
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[elbarauljr]

Peter_SSs, Thank you, I will try the XL2BB Add-in the next time as the actual sample is definitely better than a picture.

DanteAmor, I tried your code and performed the needed actions. It even allow to add more data in columns A thru J to be converted into individual rows on columns L thru V. Thank you so much for the quick response !