Excel Data Manipulation - Transpose

[ls010s]

HiI need to transpose some data as follows, for which a normal transpose doesn't seem to work.I have cost centre in column A, Account Code in column B, Description in column C, and then column D to O has values phased by month (Jan-Dec) with headings in row 1.What I want is to transpose this so that column D becomes a list of the month and column e becomes the value for that specific month and dimension. So say if I have 10 rows of data every month, then cell d2:d12 would say Jan and e2:e12 will have the value, then d13:d23 would say Feb and e13:e23 will have feb values and so on.At the moment I am using copy and paste to obtain the data from colum a to c and copy it down 12 times, and then copy all values to column e by means of copy and paste and manually input the dates in column d, again by means of copying and pasting.Much appreciated.

 

[hiker95]

ls010s,

Welcome to the MrExcel forum.

What version of Excel are you using?

Can you post a screenshot of the raw data worksheet, and, post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[ls010s]

Hihiker95

I am using Excel 2007.

ls010s,

Welcome to the MrExcel forum.

What version of Excel are you using?

Can you post a screenshot of the raw data worksheet, and, post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[ls010s]

Hi Hiker 95

I have uploaded the file and below is the link to access it. This shows where I am and where I would like to get.

https://drive.google.com/folderview?id=0BzsDpPI91t9FVkl4UDQwZjBNa0E&usp=sharing

Thanks in advance

ls010s,

Welcome to the MrExcel forum.

What version of Excel are you using?

Can you post a screenshot of the raw data worksheet, and, post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[hiker95]

ls010s,

Please do not quote entire replies from your helper. When quoting follow these guidelines:
1. Quote ONLY if it is needed to add clarity or context for your reply. If so, then
2. Quote ONLY the specific part of the post that is relevant - - not the entire post.

This will keep thread clutter to a minimum and make the discussion easier to follow.


Thanks for the workbook.

Would you like results to be in a new worksheet Results?

 

[ls010s]

Hi hiker95

I would prefer if it was in a new worksheet however I am happy if it remains in the same worksheet.

Thanks

 

[hiker95]

ls010s,

Sample raw data in worksheet Sheet1:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	CC	Nominal	Description	Jan-13	Feb-13	Mar-13	Apr-13	May-13	Jun-13	Jul-13	Aug-13	Sep-13	Oct-13	Nov-13	Dec-13
2	101	A1256	Drinks	100	100	100	150	150	150	100	100	100	100	100	150
3	101	A1033	Salary	1500	1600	1700	1800	1900	2000	2100	2200	2300	2400	2500	2600
4	101	A1095	Tax	125	125	125	125	125	125	125	125	125	125	125	125
5	101	A2350	Telecom	55	55	55	55	55	55	55	55	55	55	55	55
6	101	A2005	IT	65	65	65	65	65	65	65	65	65	65	65	65
7	101	A2004	Internet	35	35	35	35	35	35	35	35	35	35	35	35
8	101	A3050	Equipment	95	95	95	95	95	95	95	95	95	95	95	95
9	101	A3215	Facilities	105	105	105	105	105	105	105	105	105	105	105	105
10	101	A4010	Motor Runnings	30	30	30	30	30	30	30	30	30	30	30	30
11	101	A1560	Bank Charges	17	17	17	17	17	17	17	17	17	17	17	17
12
Sheet1

After the macro in a new worksheet Results (not all rows are shown for brevity):

Excel 2007
	A	B	C	D	E
1	CC	Nominal	Description	Period	Amount
2	101	A1256	Drinks	Jan-13	100
3	101	A1033	Salary	Jan-13	1500
4	101	A1095	Tax	Jan-13	125
5	101	A2350	Telecom	Jan-13	55
6	101	A2005	IT	Jan-13	65
7	101	A2004	Internet	Jan-13	35
8	101	A3050	Equipment	Jan-13	95
9	101	A3215	Facilities	Jan-13	105
10	101	A4010	Motor Runnings	Jan-13	30
11	101	A1560	Bank Charges	Jan-13	17
12	101	A1256	Drinks	Feb-13	100
13	101	A1033	Salary	Feb-13	1600
14	101	A1095	Tax	Feb-13	125
15	101	A2350	Telecom	Feb-13	55
16	101	A2005	IT	Feb-13	65
17	101	A2004	Internet	Feb-13	35
18	101	A3050	Equipment	Feb-13	95
19	101	A3215	Facilities	Feb-13	105
20	101	A4010	Motor Runnings	Feb-13	30
21	101	A1560	Bank Charges	Feb-13	17
Results

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 05/08/2013
' http://www.mrexcel.com/forum/excel-questions/701493-excel-data-manipulation-transpose.html
Dim a As Variant, b As Variant
Dim lr As Long, lc As Long, i As Long, ii As Long, c As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  a = .Range(.Cells(1, 1), .Cells(lr, lc))
  ReDim b(1 To (UBound(a, 1) + 1) * (UBound(a, 2) - 3), 1 To 5)
End With
ii = ii + 1
b(ii, 1) = "CC": b(ii, 2) = "Nominal": b(ii, 3) = "Description"
b(ii, 4) = "Period": b(ii, 5) = "Amount"
For c = 4 To lc Step 1
  For i = 2 To UBound(a, 1)
    ii = ii + 1
    b(ii, 1) = a(i, 1)
    b(ii, 2) = a(i, 2)
    b(ii, 3) = a(i, 3)
    b(ii, 4) = a(1, c)
    b(ii, 5) = a(i, c)
  Next i
Next c
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add().Name = "Results"
With Sheets("Results")
  .UsedRange.ClearContents
  .Cells(1, 1).Resize(UBound(b, 1), UBound(b, 2)) = b
  .Cells(1, 1).Resize(, 5).Font.Bold = True
  .Range("D2:D" & UBound(b, 1)).NumberFormat = "mmm-yy"
  .Columns.AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.

 

[ls010s]

Hi hiker95

Thanks a bunch for this info. It was great!

 

[ls010s]

Hi hiker95

Thanks a bunch for this info. It was great!

 

[hiker95]

ls010s,

Thanks for the feedback.

You are very welcome. Glad I could help.