Showing Data in a different way

[LisaUK]

Hi

Is there a way to rearrange data in certain way on a spreadsheet.
Mick was kind enough to help me arrange and group information together which was great.
But what I am looking to do is the following:

I currently have the following data setup



Company JAN 11 FEB 11 MAR 11

ABC 12000 10000 50000
123 20000 57000 1000
456
DEF
XXX
ZZZ


SO I have sales figures by month for each company.

What I need is to have the following



Company Sale Month







so the same data but split into 3 columns,

Any ideas ?

Thanks
Lisa




<table style="width: 858px; height: 211px;" border="0" cellpadding="0" cellspacing="0"><col style="mso-width-source:userset;mso-width-alt:14957;width:307pt" width="409"> <col style="width:48pt" span="7" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;width:307pt" height="20" width="409">
</td><td class="xl65" style="border-left:none;width:48pt" width="64">
</td><td class="xl65" style="border-left:none;width:48pt" width="64">
</td><td class="xl65" style="border-left:none;width:48pt" width="64">
</td><td class="xl65" style="border-left:none;width:48pt" width="64">
</td><td class="xl65" style="border-left:none;width:48pt" width="64">
</td><td class="xl65" style="border-left:none;width:48pt" width="64">
</td><td class="xl65" style="border-left:none;width:48pt" width="64">
</td></tr><tr style="height:15.0pt" height="20"><td class="xl66" style="height:15.0pt;border-top:none" height="20">
</td><td class="xl64" style="border-top:none;border-left:none">
</td><td class="xl64" style="border-top:none;border-left:none">
</td><td class="xl64" style="border-top:none;border-left:none">
</td><td class="xl64" style="border-top:none;border-left:none">
</td><td class="xl64" style="border-top:none;border-left:none">
</td><td class="xl64" style="border-top:none;border-left:none">
</td><td class="xl64" style="border-top:none;border-left:none">
</td></tr><tr style="height:15.0pt" height="20"><td class="xl66" style="height:15.0pt;border-top:none" height="20">
</td><td class="xl67" style="border-top:none;border-left:none">
</td><td class="xl67" style="border-top:none;border-left:none">
</td><td class="xl67" style="border-top:none;border-left:none">
</td><td class="xl67" style="border-top:none;border-left:none">
</td><td class="xl67" style="border-top:none;border-left:none">
</td><td class="xl67" style="border-top:none;border-left:none">
</td><td class="xl67" style="border-top:none;border-left:none">
</td></tr><tr style="height:15.0pt" height="20"><td class="xl68" style="height:15.0pt;border-top:none" height="20">
</td><td class="xl67" style="border-top:none;border-left:none">
</td><td class="xl67" style="border-top:none;border-left:none">
</td><td class="xl67" style="border-top:none;border-left:none">
</td><td class="xl67" style="border-top:none;border-left:none">
</td><td class="xl67" style="border-top:none;border-left:none">
</td><td class="xl67" style="border-top:none;border-left:none">
</td><td class="xl67" style="border-top:none;border-left:none">
</td></tr><tr style="height:15.0pt" height="20"><td class="xl68" style="height:15.0pt;border-top:none" height="20">
</td><td class="xl67" style="border-top:none;border-left:none">
</td><td class="xl67" style="border-top:none;border-left:none">
</td><td class="xl67" style="border-top:none;border-left:none">
</td><td class="xl67" style="border-top:none;border-left:none">
</td><td class="xl67" style="border-top:none;border-left:none">
</td><td class="xl67" style="border-top:none;border-left:none">
</td><td class="xl67" style="border-top:none;border-left:none">
</td></tr><tr style="height:15.0pt" height="20"><td class="xl68" style="height:15.0pt;border-top:none" height="20">
</td><td class="xl67" style="border-top:none;border-left:none">
</td><td class="xl67" style="border-top:none;border-left:none">
</td><td class="xl67" style="border-top:none;border-left:none">
</td><td class="xl67" style="border-top:none;border-left:none">
</td><td class="xl67" style="border-top:none;border-left:none">
</td><td class="xl67" style="border-top:none;border-left:none">
</td><td class="xl67" style="border-top:none;border-left:none">
</td></tr></tbody></table>


Company
ABC 1200
DEF
123
456
789

 

[Andrew Poulsom]

Look here:

http://spreadsheetpage.com/index.php/tip/creating_a_database_table_from_a_summary_table/

 

[Peter_SSs]

Assuming your data starts in cell A1 (with no completely blank rows or columns within the data - wasn't sure about your sample in this regard) then this may be of use.

Test in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Rearrange_Data()<br>    <SPAN style="color:#00007F">Dim</SPAN> aOrig, aFinal<br>    <SPAN style="color:#00007F">Dim</SPAN> R <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, C <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, k <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> Range("A1").CurrentRegion<br>        aOrig = .Value<br>        R = <SPAN style="color:#00007F">UBound</SPAN>(aOrig, 1)<br>        C = <SPAN style="color:#00007F">UBound</SPAN>(aOrig, 2)<br>        <SPAN style="color:#00007F">ReDim</SPAN> aFinal(1 <SPAN style="color:#00007F">To</SPAN> (R - 1) * (C - 1) + 1, 1 <SPAN style="color:#00007F">To</SPAN> 3)<br>        aFinal(1, 1) = "Company"<br>        aFinal(1, 2) = "Sale"<br>        aFinal(1, 3) = "Month"<br>        k = 1<br>        <SPAN style="color:#00007F">For</SPAN> i = 2 <SPAN style="color:#00007F">To</SPAN> R<br>            <SPAN style="color:#00007F">For</SPAN> j = 2 <SPAN style="color:#00007F">To</SPAN> C<br>                k = k + 1<br>                aFinal(k, 1) = aOrig(i, 1)<br>                aFinal(k, 2) = aOrig(i, j)<br>                aFinal(k, 3) = aOrig(1, j)<br>            <SPAN style="color:#00007F">Next</SPAN> j<br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        .Offset(, .Columns.Count + 2).Resize((R - 1) * (C - 1) + 1, 3) _<br>            .Value = aFinal<br>        .Offset(, .Columns.Count + 4).Resize((R - 1) * (C - 1) + 1, 1) _<br>            .NumberFormat = .Cells(1, 2).NumberFormat<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>


The code above converted the data in A1:D4 to the data shown in G1:I10.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Company	Jan-11	Feb-11	Mar-11			Company	Sale	Month
2	ABC	12000	10000	50000			ABC	12000	Jan-11
3	123	20000	57000	1000			ABC	10000	Feb-11
4	DEF	200		5000			ABC	50000	Mar-11
5							123	20000	Jan-11
6							123	57000	Feb-11
7							123	1000	Mar-11
8							DEF	200	Jan-11
9							DEF		Feb-11
10							DEF	5000	Mar-11
11
Rearrange Data (2)

 

[LisaUK]

Hi Andrew

Thank you for the link , works a treat!

Lisa

 

[LisaUK]

Peter, thank you for the help, really appreciate it.

Lisa

 

[LisaUK]

Hi Again

I am back on this one!
I used the following to reverse the data which worked fine, but now I have more than three columns and I want to know how I can amend it for 6 or any other number output.

Sub ReversePivotTable()
' Before running this, make sure you have a summary table with column headers.
' The output table will have three columns.
Dim SummaryTable As Range, OutputRange As Range
Dim OutRow As Long
Dim r As Long, c As Long

On Error Resume Next
Set SummaryTable = ActiveCell.CurrentRegion
If SummaryTable.Count = 1 Or SummaryTable.Rows.Count < 3 Then
MsgBox "Select a cell within the summary table.", vbCritical
Exit Sub
End If
SummaryTable.Select
Set OutputRange = Application.InputBox(prompt:="Select a cell for the 3-column output", Type:=8)
' Convert the range
OutRow = 2
Application.ScreenUpdating = False
OutputRange.Range("A1:C3") = Array("Column1", "Column2", "Column3")
For r = 2 To SummaryTable.Rows.Count
For c = 2 To SummaryTable.Columns.Count
OutputRange.Cells(OutRow, 1) = SummaryTable.Cells(r, 1)
OutputRange.Cells(OutRow, 2) = SummaryTable.Cells(1, c)
OutputRange.Cells(OutRow, 3) = SummaryTable.Cells(r, c)
OutputRange.Cells(OutRow, 3).NumberFormat = SummaryTable.Cells(r, c).NumberFormat
OutRow = OutRow + 1
Next c
Next r
End Sub



Any help would be great!

Thank you

Lisa

 

[Andrew Poulsom]

The Pivot Table approach in the link I gave you should cater for multiple columns.

 

[LisaUK]

I have tried using the method on the site and can only get 3 columns to show
When I get the 4th its not showing the correct way.
Any ideas for a quick fix?


Thanks
Lisa

 

[LisaUK]

It seems like I just cannot group my forth column no matter what I try.
I did try to change some of the code but same thing with that as well, I just get a duplicate 4th column

 

[Andrew Poulsom]

Did you try the manual approach with the pivot table? If it didn't work post a sample of your data.