i currently working on a list which contains several data horizontally, i want to make the data vertically, like transpose copy and paste. however there are over 1000 rows, and the data is not always same length like this :
description
material 1
no
material 2
no
material 3
no
material 4
no
Car Maintenance
Lubricant
1
Tire
4
Paint Polish
1
Soap
1
Bicycle Maintenance
Chain Lube
1
Tire
2
blank
blank
blank
blank
and so on, i want to make it like this :
description
material
no
Car Maintenance
Lubricant
1
Blank
Tire
4
Blank
Paint Polish
1
Blank
Soap
1
Bicycle Maintenance
Chain Lube
1
blank
Tire
2
and so on. it's easy to make it by using transpose copy and paste, however, i think it must insert number of rows first, then trasnpose paste it. But with over 1000 rows, it will take times, maybe there are some VBA codes to make it easier
Gee, John, that looks awfully familiar. You beat me to it!
My very similar solution assumes there's only one table on the sheet, and re-formats it to display the new "data shape":
@abduldedysubhansyah - To simplify filtering, you might want to consider including the description on every row. The code could be modified to do so very easily.
Last edited by leelnich; 06-30-2023 at 08:42 AM.
Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").
It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.
- Follow this link to learn how to install Power Query in Excel 2010 / 2013.
- Follow this link for an introduction to Power Query functionality.
- Follow this link for a video which demonstrates how to use Power Query code provided.
Sub Demo1() Dim V, R&, W(), C%, L& With Sheets("Sheet2").ListObjects(1) If .ListColumns.Count > 3 And (.ListColumns.Count And 1) Then V = .DataBodyRange R = Application.CountA(V): If R = 0 Or R Mod 3 Then Beep: Exit Sub ReDim W(1 To R / 3, 1 To 3) For R = 1 To UBound(V) For C = 2 To UBound(V, 2) Step 2 If Not IsEmpty(V(R, C)) Then L = L + 1: W(L, 1) = V(R, 1): W(L, 2) = V(R, C): W(L, 3) = V(R, C + 1) Next C, R .Range.Columns(4).Resize(, .ListColumns.Count - 3).EntireColumn.Delete .Range.Range("B1:C1") = Array("Material", "No") If L Then .DataBodyRange.Resize(L) = W .Range.Columns.AutoFit End If End With End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Last edited by Marc L; 06-30-2023 at 09:09 AM.
Reason: optimization ...
Bookmarks