Forum Discussion
Organize my Data using Excel
Hi, I have a problem on how to organized my data.
I want my data to become more clean.
I think it should have like a way on how I'm going to do it with excel.
This is the problem.
I wanted the company A until E move to a new column besides the product name. So, I do it manual, it will take time.
So, is there anyone knows how to do it by using formula or something else, which I am not yet discover. Thank you so much!!!!
- Is the first screen cap from a pivot table? It looks like 'Compact' report layout. You can disable the 'nesting' by switching to Tabular report layout.
- Yes it is. I already did that. And thank you!!! It does work the way I wanted.
An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
The data layout in the screenshot and in the attached file is for illustration. You can as well place the green result table in another worksheet.Run this macro:
Sub Rearrange() Dim r As Long Dim s As Long Dim m As Long Dim c As String Application.ScreenUpdating = False m = Range("A" & Rows.Count).End(xlUp).Row ReDim v(1 To m, 1 To 2) v(1, 1) = "Company" v(1, 2) = "Category" s = 1 For r = 2 To m If Range("A" & r).IndentLevel = 0 Then c = Range("A" & r).Value Else s = s + 1 v(s, 1) = c v(s, 2) = Range("A" & r).Value End If Next r Range("A1:B" & m).Value = v Range("A1:A" & m).IndentLevel = 0 End Sub
