Combining Horizontal and vertical fields to be all vertical

[JW4610]

If I have 20 projects listed vertically (A1-A20) and all of their monthly performance results for the last year listed horizontally (B2 - M20)
i.e. Project A3 results are B3-M3

Via a macro or otherwise, I need to get all of the results listed vertically i.e. if no months are 0:
the first project will be repeated in A1-A12, the months in B1 - B12 and result in C1 - C12
Second project will be repeated in A13-A24, the months in B13 - B24 and result in C13 - C24


Ideally, it will skip any rows of project, month and result where the result is 0 as in that month it has been completed or not started

I'm not a confident macro user but am getting there
Thanks in advance!

 

[hiker95]

JW4610,

Welcome to the MrExcel forum.

Can we see what your raw data looks like, and, what the results should look like?

I would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

See reply #2 at the next link, if you want to show small screenshots.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729

 

[JW4610]

sure, This is an example of what it needs to go from (1) and to (2)

Excel Workbook
	A	B	C	D	E
1	*	Jan-15	Feb-15	Mar-15	Apr-15
2	Project 1	151443	216630	224172	167693
3	Project 2	210793	194883	217224	181795
4	Project 3	0	0	0	175293
Sheet4

Excel Workbook
	A	B	C
7	Project 1	Jan-15	151443
8	Project 1	Feb-15	216630
9	Project 1	Mar-15	224172
10	Project 1	Apr-15	167693
11	Project 2	Jan-15	210793
12	Project 3	Feb-15	194883
13	Project 4	Mar-15	217224
14	Project 5	Apr-15	181795
15	Project 3	Apr-15	175293
Sheet4

 

[hiker95]

JW4610,

It would appear that your worksheet description in reply #1, does not match your screenshot in your reply #3?

1. Can we see a screenshot of your raw data, Jan-15 thru Dec-15?

2. And, can we see a screenshot of what the results should look like?

3. Will the results begin in the third row, below the last used row of your raw data?

 

[JW4610]

Hi Hiker 95,

The screenshot in #3 is a sample that represents what I described in post 1 - I did however move the project descriptions down 1 row to allow for headings. The first screenshot I've posted is an example of what my data looks like and the second is what I need it to look like.

I did this manually, if possible, I need a formula or Macro to do this on a larger scale.

Thanks

 

[hiker95]

JW4610,

Here is a macro solution for you to consider, that uses two arrays in memory, and, will adjust to the number of raw data rows, and, columns.

The results will be written beginning in the row that is three rows below the last used row of your raw data.

Sample raw data, and, results:

Excel 2007
	A	B	C	D	E	F
1		Jan-15	Feb-15	Mar-15	Apr-15
2	Project 1	151443	216630	224172	167693
3	Project 2	210793	194883	217224	181795
4	Project 3	0	0	0	175293
5
6
7	Project 1	Jan-15	151443
8	Project 1	Feb-15	216630
9	Project 1	Mar-15	224172
10	Project 1	Apr-15	167693
11	Project 2	Jan-15	210793
12	Project 2	Feb-15	194883
13	Project 2	Mar-15	217224
14	Project 2	Apr-15	181795
15	Project 3	Apr-15	175293
16
Sheet4

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
2. Open your NEW 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
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub ReorgData()
' hiker95, 03/223/2016, ME929793
Dim a As Variant, i As Long, lr As Long, lc As Long, c As Long
Dim o As Variant, j As Long
Application.ScreenUpdating = False
With Sheets("Sheet4")
  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 o(1 To (UBound(a, 2) - 1) * (UBound(a, 1) - 1), 1 To 3)
  For i = 2 To UBound(a, 1)
    For c = 2 To UBound(a, 2)
      If a(i, c) <> 0 Then
        j = j + 1: o(j, 1) = a(i, 1): o(j, 2) = a(1, c): o(j, 3) = a(i, c)
      End If
    Next c
  Next i
  .Cells(lr + 3, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
  .Cells(lr + 3, 2).Resize(UBound(o, 1)).NumberFormat = "mmm-yy"
  .Columns(1).Resize(, UBound(o, 2)).AutoFit
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, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgData macro.

 

[JW4610]

Amazing, thank you so much!

 

[hiker95]

JW4610,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.