Converting Data From Grids to Columns

M

Maanrefai

New Member
Joined
Jan 13, 2015
Messages
2
I apologize in advance if the title is not very clear
Basically i need a VBA code that transforms Data from below shown "Table Given" to "Table Result"

Thus for example, since there are 3 doors which have sales of mentioned items
The items are copy pasted above each other 3 times, and the name of the door is pasted next to each sales

Please note that the number of doors might increase and the item number might increase

Thank you in advanced :-)

Table Given

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl67, width: 64, align: center"]Item[/TD]
[TD="class: xl67, width: 64, align: center"]Door A[/TD]
[TD="class: xl67, width: 64, align: center"]Door B[/TD]
[TD="class: xl67, width: 64, align: center"]Door C[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 1[/TD]
[TD="class: xl67, align: center"]3[/TD]
[TD="class: xl67, align: center"]2[/TD]
[TD="class: xl67, align: center"]2[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 2[/TD]
[TD="class: xl67, align: center"]2[/TD]
[TD="class: xl67, align: center"]3[/TD]
[TD="class: xl67, align: center"]2[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 3[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]4[/TD]
[TD="class: xl67, align: center"]3[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 4[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]4[/TD]
[TD="class: xl67, align: center"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 5[/TD]
[TD="class: xl67, align: center"]3[/TD]
[TD="class: xl67, align: center"]4[/TD]
[TD="class: xl67, align: center"]3[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 6[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 7[/TD]
[TD="class: xl67, align: center"]2[/TD]
[TD="class: xl67, align: center"]4[/TD]
[TD="class: xl67, align: center"]2[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 8[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]4[/TD]
[/TR]
</tbody>[/TABLE]

Table Result

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width: 48pt; text-align: center;"> </colgroup><tbody>[TR]
[TD="class: xl67, width: 64, align: center"]Item[/TD]
[TD="class: xl67, width: 64, align: center"]Sales[/TD]
[TD="class: xl67, width: 64, align: center"]Door A[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 1[/TD]
[TD="class: xl67, align: center"]3[/TD]
[TD="class: xl67, align: center"]Door A[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 2[/TD]
[TD="class: xl67, align: center"]2[/TD]
[TD="class: xl67, align: center"]Door A[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 3[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]Door A[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 4[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]Door A[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 5[/TD]
[TD="class: xl67, align: center"]3[/TD]
[TD="class: xl67, align: center"]Door A[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 6[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]Door A[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 7[/TD]
[TD="class: xl67, align: center"]2[/TD]
[TD="class: xl67, align: center"]Door A[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 8[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]Door A[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 1[/TD]
[TD="class: xl67, align: center"]2[/TD]
[TD="class: xl67, align: center"]Door B[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 2[/TD]
[TD="class: xl67, align: center"]3[/TD]
[TD="class: xl67, align: center"]Door B[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 3[/TD]
[TD="class: xl67, align: center"]4[/TD]
[TD="class: xl67, align: center"]Door B[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 4[/TD]
[TD="class: xl67, align: center"]4[/TD]
[TD="class: xl67, align: center"]Door B[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 5[/TD]
[TD="class: xl67, align: center"]4[/TD]
[TD="class: xl67, align: center"]Door B[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 6[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]Door B[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 7[/TD]
[TD="class: xl67, align: center"]4[/TD]
[TD="class: xl67, align: center"]Door B[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 8[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]Door B[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 1[/TD]
[TD="class: xl67, align: center"]2[/TD]
[TD="class: xl67, align: center"]Door C[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 2[/TD]
[TD="class: xl67, align: center"]2[/TD]
[TD="class: xl67, align: center"]Door C[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 3[/TD]
[TD="class: xl67, align: center"]3[/TD]
[TD="class: xl67, align: center"]Door C[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 4[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]Door C[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 5[/TD]
[TD="class: xl67, align: center"]3[/TD]
[TD="class: xl67, align: center"]Door C[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 6[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]Door C[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 7[/TD]
[TD="class: xl67, align: center"]2[/TD]
[TD="class: xl67, align: center"]Door C[/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"]Item 8[/TD]
[TD="class: xl67, align: center"]4[/TD]
[TD="class: xl67, align: center"]Door C[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
This can be done without VBA by doing a Reverse Pivot Table as follows:

1. Press the following keys sequentially: Alt D P (don't hold the keys down, rather just press and release each key sequentially)
2. On the Pivot Table Wizard box that pops up, check "Multiple Consolidation Ranges" , then click Next
3. On the next box that pops up, check "I will create the page fields" , then click Next
4. On the next box that pops up, enter the Range containing your data , then click Finish
5. On the Pivot Table that now appears, double click the Grand Total/Grand Total amount (in this case it's 55) and your desired table result will appear.
 
Upvote 0
Here is a macro that will do what you asked for...

Code: 
Sub GridToColumns()
  Dim X As Long, LastRow As Long, LastCol As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
  Application.ScreenUpdating = False
  Range("A2:A" & LastRow).Copy Range("A2:A" & 1 + (LastRow - 1) * (LastCol - 1))
  For X = 3 To LastCol
    Cells(2, X).Resize(LastRow - 1).Copy Cells(Rows.Count, "B").End(xlUp).Offset(1).Resize(LastRow - 1)
  Next
  For X = 2 To LastCol
    Cells(2 + (X - 2) * 8, 3).Resize(LastRow - 1).Value = Cells(1, X).Value
    Cells(2 + (X - 2) * 8, 3).Resize(LastRow - 1).HorizontalAlignment = xlCenter
  Next
  Columns("D").Resize(, LastCol - 2).Clear
  Range("B1").Resize(, 2) = Array("Sales", "Door")
  Application.ScreenUpdating = True
End Sub
 
Upvote 0
Rick Rothstein
Thank you so much, it is working perfectly :-)
Result is exactly what i needed

Your help is highly appreciated



RonB1111
Thanks
However my doors are 121 Point of sales, thus it will take lots of time in a pivot table to drag
Thanks for your help anyway

Regards
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Concatenate Numerous rows based off cells value
Replies
4
Views
281
hagia_sofia
hagia_sofia
R
  • Question Question
Macro to add multiple identical rows of data from a workbook before or after each change in Column A
Replies
0
Views
187
rickadams
R
P
  • Question Question
Process and Indentation
Replies
1
Views
191
Fuji
F
SaraWitch
  • Solved
Auto calculate based on multiple drop down options
Replies
7
Views
176
SaraWitch
SaraWitch
G
  • Question Question
Help me with this!
Replies
1
Views
344
bobaol
B

Forum statistics

Threads
1,223,240
Messages
6,170,951
Members
452,368
Latest member
jayp2104

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top