Transposing Data

[ls010s]

Hi

I need to transpose some data for which a normal transpose doesn't seem to work.

The file is available at https://docs.google.com/file/d/0BzsDpPI91t9FMTNRRjQyOFF0N0U/edit?usp=sharing

What I want is to transpose this data so that column F becomes a list of the month (as mentioned in row 5), column G becoming the net change value and column H becoming the year to date value.

So say if I have 10 rows of data every month, then cell f6:f16 would say Jan and g6:g16 will have the net change value and h6:h16 will show YTD value. For month 2 cell f17:f27 would say Feb and g17:g27 will have the net change value for Feb and h17:h27 will show YTD value for Feb.


Can you please help me with a macro that will do this for me? I am using Office 2007 and I would prefer if the results are created in a new worksheet and checked at the end by means of subtotal etc to ensure that the sum of result matches the sum of the original data.

Regards

Oh an FYI I have had assistance with something similar like this before however the data set was different and I am not sure how to edit this macro. The previous thread can be found at http://www.mrexcel.com/forum/excel-...-data-manipulation-transpose.html#post3466523

 

[patel45]

can you attach also desired result ?

 

[ls010s]

Hi

The desired result is now saved in https://docs.google.com/file/d/0BzsDpPI91t9FblFOY2UzS3ZKUEk/edit?usp=sharing

Please note I changed the account code in book 1 to ensure you understand how the result comes together. Therefore please use this file https://docs.google.com/file/d/0BzsDpPI91t9FRjVVaTNQUmpzaGs/edit?usp=sharing instead of the one mentioned in previous thread

Regards

 

[patel45]

Sub a()
Dim rng As Range, rng1 As Range
With Sheets(1)
LC = .UsedRange.Columns.Count
LR = .UsedRange.Rows.Count
drow = 6
Set rng = .Range("A6:E" & LR)
For c = 6 To LC Step 2
  Set rng1 = .Range(.Cells(6, c), .Cells(LR, c + 1))
  rng.Copy Sheets(2).Cells(drow, 1)
  .Cells(4, c).Copy Sheets(2).Cells(drow, 6)
  rng1.Copy Sheets(2).Cells(drow, 7)
  drow = drow + LR - 5
Next
End With
End Sub

 

[hiker95]

ls010s,

Thanks for the Private Message.

Will worksheet Sheet1:
1. only have 5 unique codes?
2. always contain 12 months of data?

 

[hiker95]

ls010s,

Thanks for the Private Message.

Will worksheet Sheet1:
1. only have 5 unique codes? I missed this part --> So say if I have 10 rows of data every month

2. All the unque Account Codes will always have the same number of codes?

3. always contain 12 months of data?

 

[ls010s]

Hi hiker95

Thanks for the response. Answers below:

1. The worksheet may have up to 3000 unique codes.

2. Yes, the code will always have 17 characters (including the -)

3. No, it can contain more than 12 months but if that can't be done I am happy with 12 months.

Regards

 

[ls010s]

Hi Patel45

Thank you for the response. Greatly appreciated. I am having some issues with this code.

- I needed the date to be in column F on every line instead of being at the top. So imagine if I have the dates on every line, I can then use a pivot to display this data and do some charts on it.

- When the data is transposed, the headings to be given on the first row only and not individually for every month. It would be ideal if this could be in row 1 and row 2 starts off with the data.

- When I run your macro, it transposes the data however after transposing P12 data, for another period (which doesn't exist in the file) it creates another data set (column A:E) however without any values.

Your assistance would be much appreciated.

Regards

 

[patel45]

try this

Sub a()
Dim rng As Range, rng1 As Range
With Sheets(1)
LC = .UsedRange.Columns.Count
LR = .UsedRange.Rows.Count
drow = 2
Set rng = .Range("A6:E" & LR)
For c = 6 To LC Step 2
  Set rng1 = .Range(.Cells(6, c), .Cells(LR, c + 1))
  rng.Copy Sheets(2).Cells(drow, 1)
  .Cells(4, c).Copy Sheets(2).Range("F" & drow & ":F" & drow + LR - 6)
  rng1.Copy Sheets(2).Cells(drow, 7)
  drow = drow + LR - 5
Next
End With
End Sub

I did not understand the last issue

 

[ls010s]

Hi Patel45

Thank you very much.

That works perfectly. I've sorted the last issue out and its great.

Thanks