Forum Discussion
Solved
Need Help with turning columns into rows
I have a table of workers and for each a set of columns that indicate information that needs to be moved to a linear format.
I changed the workers to fruits to make it easier to understand, but the top chart is a representation of the data set that is pulled from the system and the bottom (in red) is how I need to display it for calculations.
My data set is much larger than this, but what is the best solution to achieve this?
Sub worker() Dim i As Long Dim j As Long Dim m As Long Dim k As Long Dim l As Long Dim n As Long Range(Cells(15, 1), Cells(1048576, 5)).Clear i = Range("A" & Rows.Count).End(xlUp).Row m = 15 For j = 2 To i Range(Cells(m, 1), Cells(m + 2, 1)).Value = Cells(j, 1).Value Range(Cells(m, 2), Cells(m, 5)).Value = Range(Cells(j, 2), Cells(j, 5)).Value Range(Cells(m + 1, 2), Cells(m + 1, 5)).Value = Range(Cells(j, 6), Cells(j, 9)).Value Range(Cells(m + 2, 2), Cells(m + 2, 5)).Value = Range(Cells(j, 10), Cells(j, 13)).Value m = m + 3 Next j l = Range("A" & Rows.Count).End(xlUp).Row For n = 15 To l If IsEmpty(Cells(n, 2)) And Not (IsEmpty(Cells(n, 1))) Then Range(Cells(n, 1), Cells(n, 5)).Delete n = n - 1 Else End If Next n End SubMaybe with these lines of code. In the attached file you can click the button in cell F10 to run the macro. If the table with original data is large (e.g. 1000 rows) the result table can be in columns P to T for example.
Tami_E Offering you another PQ solution, inserted into the file provided by OliverScheurich earlier. The only requirement here is that you name the columns consistently for each group (Date, State,% and Location). In this example PQ connects to a named range, myData. Make sure you edit the range when the table expands and press Refresh All on the Data ribbon.
Sub worker() Dim i As Long Dim j As Long Dim m As Long Dim k As Long Dim l As Long Dim n As Long Range(Cells(15, 1), Cells(1048576, 5)).Clear i = Range("A" & Rows.Count).End(xlUp).Row m = 15 For j = 2 To i Range(Cells(m, 1), Cells(m + 2, 1)).Value = Cells(j, 1).Value Range(Cells(m, 2), Cells(m, 5)).Value = Range(Cells(j, 2), Cells(j, 5)).Value Range(Cells(m + 1, 2), Cells(m + 1, 5)).Value = Range(Cells(j, 6), Cells(j, 9)).Value Range(Cells(m + 2, 2), Cells(m + 2, 5)).Value = Range(Cells(j, 10), Cells(j, 13)).Value m = m + 3 Next j l = Range("A" & Rows.Count).End(xlUp).Row For n = 15 To l If IsEmpty(Cells(n, 2)) And Not (IsEmpty(Cells(n, 1))) Then Range(Cells(n, 1), Cells(n, 5)).Delete n = n - 1 Else End If Next n End SubMaybe with these lines of code. In the attached file you can click the button in cell F10 to run the macro. If the table with original data is large (e.g. 1000 rows) the result table can be in columns P to T for example.
- This works great but I thought I could build onto the code (my data set is much larger than this example).....i've been trying the past few days and can't seem to figure it out. Can someone please help me with two things....one, i need the results of the macro to go to a different worksheet. Also, i need to add columns to capture future changes. (this has change 1, change 2, i'll need to go up to change 6...,.and i thought i could easily just add to this, but i'm stumped!!
Tami_E Perhaps you should forget about VBA and go for PowerQuery instead.
Tami_E This is a complicated one... are you familiar with Power Query?
What I did was
1) use Get Data/From Table Range
2) duplicate the entire table twice
3) select the worker, and then Ctrl + Select the individual columns pertaining to that section of the original range (original, first change, second change) and then right click to "remove other columns"
4) Ensured that each column had the same name and data type as the first (e.g. changing the name from Effective Date First Change to Start Date to match the original)
5) Appended all three queries.
Hoping others can come up with an easier solution.
