Extract row data on to new sheet, only if certain cell value is greater or less than 0

[ssmith2015]

Hello all,

Not even sure if this is possible, but I'm turning to the experts for advise.

I have sheet 1. Example table showing below (actual table has 2000+ lines). On sheet 2, I would like to extract data from sheet 1, but only select data, depending on if cell in column 0 is greater than or less than 0 (any figure except 0). Column O is a sum formula of C:N.

I would want the account #, as well as the amounts for Jan to Dec.

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J
[/TD]
[TD="align: center"]K
[/TD]
[TD="align: center"]L
[/TD]
[TD="align: center"]M
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]O
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]Account[/TD]
[TD="align: center"]Total 2014[/TD]
[TD="align: center"]Jan[/TD]
[TD="align: center"]Feb[/TD]
[TD="align: center"]Mar[/TD]
[TD="align: center"]Apr[/TD]
[TD="align: center"]May[/TD]
[TD="align: center"]Jun[/TD]
[TD="align: center"]Jul[/TD]
[TD="align: center"]Aug[/TD]
[TD="align: center"]Sep[/TD]
[TD="align: center"]Oct[/TD]
[TD="align: center"]Nov[/TD]
[TD="align: center"]Dec[/TD]
[TD="align: center"]Total 2015[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]expense 1[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"][/TD]
[TD="align: center"]20
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]140[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]expense 2[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]expense 3[/TD]
[TD="align: center"]120[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]120[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]expense 4[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]80[/TD]
[TD="align: center"]80[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"]expense 5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]

So as per the table above, this is what I'm hoping to see on sheet 2.

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]I
[/TD]
[TD="align: center"]J
[/TD]
[TD="align: center"]K
[/TD]
[TD="align: center"]L
[/TD]
[TD="align: center"]M
[/TD]
[TD="align: center"]N
[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]expense 1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]expense 3[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]expense 4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]80[/TD]
[/TR]
</tbody>[/TABLE]

Is this at all possible? Appreciate any insight you can give. Thank you!!!

 

[DocAElstein]

Hello all,

Not even sure if this is possible, but I'm turning to the experts for advise.

Hi,
. Welcome to the Board!
. I am no expert just a Beginner.
. But this one is so easy even I can do it!!

I assume Sheet 1 from you should look like this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Account	Total 2014	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total 2015
2	expense 1	100		20		20		20	20	20			20	20	140
3	expense 2	50													0
4	expense 3	120	10	10	10	10	10	10	10	10	10	10	10	10	120
5	expense 4	80												80	80
6	expense 5	5													0
Sheet1

And your sheet 2 finally should look like this ?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	1	expense 1		20		20		20	20	20			20	20
2	2	expense 3	10	10	10	10	10	10	10	10	10	10	10	10
3	3	expense 4												80
Sheet2

Here is the code


<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN> <SPAN style="color:#007F00">' This line is not essential but a good idea. It means you must define your variables</SPAN><br><SPAN style="color:#00007F">Sub</SPAN> Forssmith2015() <SPAN style="color:#007F00">'This is the main Procedure (Called a Sub()!)</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN> <SPAN style="color:#007F00">' Define the type of variable you need in the code</SPAN><br>**<SPAN style="color:#00007F">For</SPAN> j = 2 <SPAN style="color:#00007F">To</SPAN> 6 <SPAN style="color:#00007F">Step</SPAN> 1 <SPAN style="color:#007F00">' Go through the lines of interest. (you will need 2000+ instead of 6 here)</SPAN><br>****<SPAN style="color:#00007F">If</SPAN> ThisWorkbook.Worksheets("Sheet1").Cells(j, 15) <> "0" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">'If your condition is met then:</SPAN><br>****<SPAN style="color:#00007F">Let</SPAN> y = y + 1 <SPAN style="color:#007F00">' y is the number in column 1 of Sheet</SPAN><br>******<SPAN style="color:#00007F">For</SPAN> i = 3 <SPAN style="color:#00007F">To</SPAN> 14 <SPAN style="color:#00007F">Step</SPAN> 1 <SPAN style="color:#007F00">' Go through columns 3 to 14 (C to N)</SPAN><br>******ThisWorkbook.Worksheets("sheet2").Cells(y, i).Value = ThisWorkbook.Worksheets("sheet1").Cells(j, i).Value <SPAN style="color:#007F00">' put in Sheet 2 the appropriate value from sheet1</SPAN><br>******<SPAN style="color:#00007F">Next</SPAN> i<br>******ThisWorkbook.Worksheets("sheet2").Cells(y, 1).Value = y <SPAN style="color:#007F00">' put the y value in row 1</SPAN><br>******ThisWorkbook.Worksheets("sheet2").Cells(y, 2).Value = ThisWorkbook.Worksheets("sheet1").Cells(j, 1).Value <SPAN style="color:#007F00">' Put the Expense number in Row 1</SPAN><br>****<SPAN style="color:#00007F">Else</SPAN> <SPAN style="color:#007F00">' If your condition is not met...</SPAN><br>****<SPAN style="color:#007F00">'**then do nothing!</SPAN><br>****<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>**<SPAN style="color:#00007F">Next</SPAN> j<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN> <SPAN style="color:#007F00">'Forssmith2015()</SPAN></FONT>

or simplified

Sub Forssmith2015()  For j = 2 To 6 Step 1
    If Worksheets("Sheet1").Cells(j, 15) <> "0" Then
    Let y = y + 1
      For i = 3 To 14 Step 1
      Worksheets("sheet2").Cells(y, i).Value = Worksheets("sheet1").Cells(j, i).Value
      Next i
      Worksheets("sheet2").Cells(y, 1).Value = y
      Worksheets("sheet2").Cells(y, 2).Value = Worksheets("sheet1").Cells(j, 1).Value
    Else
    End If
  Next j
End Sub

Here is the complete Excel File for you in XL2003 and XL2007
FileSnack | Easy file sharing
FileSnack | Easy file sharing

I tried them out they work!

Hope that helps
Alan
Bavaria

P.s.
If you’re a real beginner and need more help, let me know. (It may be late when I reply as it is almost Bed-Time here!!

 

[DocAElstein]

Hi, Code again without the **** which should not be there! That comes from an errror in the editor in this Forum

<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN> <SPAN style="color:#007F00">' This line is not essential but a good idea. It means you must define your variables</SPAN><br><SPAN style="color:#00007F">Sub</SPAN> Forssmith2015() <SPAN style="color:#007F00">'This is the main Procedure (Called a Sub()!)</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN> <SPAN style="color:#007F00">' Define the type of variable you need in the code</SPAN><br>  <SPAN style="color:#00007F">For</SPAN> j = 2 <SPAN style="color:#00007F">To</SPAN> 6 <SPAN style="color:#00007F">Step</SPAN> 1 <SPAN style="color:#007F00">' Go through the lines of interest. (you will need 2000+ instead of 6 here)</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> ThisWorkbook.Worksheets("Sheet1").Cells(j, 15) <> "0" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">'If your condition is met then:</SPAN><br>    <SPAN style="color:#00007F">Let</SPAN> y = y + 1 <SPAN style="color:#007F00">' y is the number in column 1 of Sheet</SPAN><br>      <SPAN style="color:#00007F">For</SPAN> i = 3 <SPAN style="color:#00007F">To</SPAN> 14 <SPAN style="color:#00007F">Step</SPAN> 1 <SPAN style="color:#007F00">' Go through columns 3 to 14 (C to N)</SPAN><br>      ThisWorkbook.Worksheets("sheet2").Cells(y, i).Value = ThisWorkbook.Worksheets("sheet1").Cells(j, i).Value <SPAN style="color:#007F00">' put in Sheet 2 the appropriate value from sheet1</SPAN><br>      <SPAN style="color:#00007F">Next</SPAN> i<br>      ThisWorkbook.Worksheets("sheet2").Cells(y, 1).Value = y <SPAN style="color:#007F00">' put the y value in row 1</SPAN><br>      ThisWorkbook.Worksheets("sheet2").Cells(y, 2).Value = ThisWorkbook.Worksheets("sheet1").Cells(j, 1).Value <SPAN style="color:#007F00">' Put the Expense number in Row 1</SPAN><br>    <SPAN style="color:#00007F">Else</SPAN> <SPAN style="color:#007F00">' If your condition is not met...</SPAN><br>    <SPAN style="color:#007F00">'  then do nothing!</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>  <SPAN style="color:#00007F">Next</SPAN> j<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN> <SPAN style="color:#007F00">'Forssmith2015()</SPAN></FONT>

 

[DocAElstein]

And just to get it right again for a beginner.. with the correct code indents!


<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN> <SPAN style="color:#007F00">' This line is not essential but a good idea. It means you must define your variables</SPAN><br><SPAN style="color:#00007F">Sub</SPAN> Forssmith2015() <SPAN style="color:#007F00">'This is the main Procedure (Called a Sub()!)</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN> <SPAN style="color:#007F00">' Define the type of variable you need in the code</SPAN><br>  <SPAN style="color:#00007F">For</SPAN> j = 2 <SPAN style="color:#00007F">To</SPAN> 6 <SPAN style="color:#00007F">Step</SPAN> 1 <SPAN style="color:#007F00">' Go through the lines of interest. (you will need 2000+ instead of 6 here)</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> ThisWorkbook.Worksheets("Sheet1").Cells(j, 15) <> "0" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">'If your condition is met then:</SPAN><br>    <SPAN style="color:#00007F">Let</SPAN> y = y + 1 <SPAN style="color:#007F00">' y is the number in column 1 of Sheet</SPAN><br>      <SPAN style="color:#00007F">For</SPAN> i = 3 <SPAN style="color:#00007F">To</SPAN> 14 <SPAN style="color:#00007F">Step</SPAN> 1 <SPAN style="color:#007F00">' Go through columns 3 to 14 (C to N)</SPAN><br>      ThisWorkbook.Worksheets("sheet2").Cells(y, i).Value = ThisWorkbook.Worksheets("sheet1").Cells(j, i).Value <SPAN style="color:#007F00">' put in Sheet 2 the appropriate value from sheet1</SPAN><br>      <SPAN style="color:#00007F">Next</SPAN> i<br>    ThisWorkbook.Worksheets("sheet2").Cells(y, 1).Value = y <SPAN style="color:#007F00">' put the y value in row 1</SPAN><br>    ThisWorkbook.Worksheets("sheet2").Cells(y, 2).Value = ThisWorkbook.Worksheets("sheet1").Cells(j, 1).Value <SPAN style="color:#007F00">' Put the Expense number in Row 1</SPAN><br>    <SPAN style="color:#00007F">Else</SPAN> <SPAN style="color:#007F00">' If your condition is not met...</SPAN><br>    <SPAN style="color:#007F00">'  then do nothing!</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>  <SPAN style="color:#00007F">Next</SPAN> j<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN> <SPAN style="color:#007F00">'Forssmith2015()</SPAN></FONT>

 

[ssmith2015]

Wow thank you so so much!!!


I'm home now and didn't bring my laptop with me but I'll try first thing tomorrow morning when I get to the office and let you know. I greatly appreciate your help. Hope you have a nice evening.

 

[DocAElstein]

Wow thank you so so much!!!


I'm home now and didn't bring my laptop with me but I'll try first thing tomorrow morning when I get to the office and let you know. I greatly appreciate your help. Hope you have a nice evening.

Your welcome
Good Night / Gute Nacht

 

[DocAElstein]

P.s. Small correction in bit of the comments... ' Put the Expense number in Row 1 should read ' Put the Expense number in Row 2

 

[hiker95]

ssmith2015,

Welcome to the MrExcel forum.

Sample worksheet:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Account	Total 2014	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total 2015
2	expense 1	100		20		20		20	20	20			20	20	140
3	expense 2	50													0
4	expense 3	120	10	10	10	10	10	10	10	10	10	10	10	10	120
5	expense 4	80												80	80
6	expense 5	5													0
7
Sheet1

Cell Formulas
Range	Formula
O2	=SUM(C2:N2)
O3	=SUM(C3:N3)
O4	=SUM(C4:N4)
O5	=SUM(C5:N5)
O6	=SUM(C6:N6)

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3
4
Sheet2

After the macro (that uses the rangeobject, and, an array in memory) in worksheet Sheet2:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	1	expense 1		20		20		20	20	20			20	20
2	2	expense 3	10	10	10	10	10	10	10	10	10	10	10	10
3	3	expense 4												80
4
Sheet2

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, 08/06/2014, ME797106
Dim w1 As Worksheet, w2 As Worksheet
Dim o As Variant, j As Long
Dim d As Range
Dim lr As Long, n As Long, c As Long
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")
Set w2 = Sheets("Sheet2")
With w1
  .Activate
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  n = Application.CountIf(.Range("O2:O" & lr), "<>0")
  ReDim o(1 To n, 1 To 14)
  For Each d In .Range("O2:O" & lr)
    If d.Value <> 0 Then
      j = j + 1
      o(j, 1) = j
      o(j, 2) = d.Offset(, -14).Value
      For c = 3 To 14
        o(j, c) = .Cells(d.Row, c).Value
      Next c
    End If
  Next d
End With
With w2
  .UsedRange.ClearContents
  .Cells(1, 1).Resize(n, 14).Value = o
  .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.

 

[DocAElstein]

ssmith2015,

Welcome to the MrExcel forum.

Sample worksheet:

Excel 2007

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]Account[/TD]
[TD="bgcolor: #FFFFFF"]Total 2014[/TD]
[TD="bgcolor: #FFFFFF"]Jan[/TD]
[TD="bgcolor: #FFFFFF"]Feb[/TD]
[TD="bgcolor: #FFFFFF"]Mar[/TD]
[TD="bgcolor: #FFFFFF"]Apr[/TD]
[TD="bgcolor: #FFFFFF"]May[/TD]
[TD="bgcolor: #FFFFFF"]Jun[/TD]
[TD="bgcolor: #FFFFFF"]Jul[/TD]
[TD="bgcolor: #FFFFFF"]Aug[/TD]
[TD="bgcolor: #FFFFFF"]Sep[/TD]
[TD="bgcolor: #FFFFFF"]Oct[/TD]
[TD="bgcolor: #FFFFFF"]Nov[/TD]
[TD="bgcolor: #FFFFFF"]Dec[/TD]
[TD="bgcolor: #FFFFFF"]Total 2015[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]expense 1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]100[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]20[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]20[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]20[/TD]
[TD="bgcolor: #FFFFFF, align: right"]20[/TD]
[TD="bgcolor: #FFFFFF, align: right"]20[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]20[/TD]
[TD="bgcolor: #FFFFFF, align: right"]20[/TD]
[TD="bgcolor: #FFFFFF, align: right"]140[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]expense 2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]50[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]expense 3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]120[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]120[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF"]expense 4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]80[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]80[/TD]
[TD="bgcolor: #FFFFFF, align: right"]80[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFFFF"]expense 5[/TD]
[TD="bgcolor: #FFFFFF, align: right"]5[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]0[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]O2[/TH]
[TD="align: left"]=SUM(C2:N2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]O3[/TH]
[TD="align: left"]=SUM(C3:N3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]O4[/TH]
[TD="align: left"]=SUM(C4:N4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]O5[/TH]
[TD="align: left"]=SUM(C5:N5)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]O6[/TH]
[TD="align: left"]=SUM(C6:N6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Excel 2007

	A	B	C	D	E	F	G	H	I	J	K	L	M	N

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

After the macro (that uses the rangeobject, and, an array in memory) in worksheet Sheet2:

Excel 2007

	A	B	C	D	E	F	G	H	I	J	K	L	M	N

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF"]expense 1[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]20[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]20[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]20[/TD]
[TD="bgcolor: #FFFFFF, align: right"]20[/TD]
[TD="bgcolor: #FFFFFF, align: right"]20[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]20[/TD]
[TD="bgcolor: #FFFFFF, align: right"]20[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="bgcolor: #FFFFFF"]expense 3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]3[/TD]
[TD="bgcolor: #FFFFFF"]expense 4[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]80[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

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, 08/06/2014, ME797106
Dim w1 As Worksheet, w2 As Worksheet
Dim o As Variant, j As Long
Dim d As Range
Dim lr As Long, n As Long, c As Long
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")
Set w2 = Sheets("Sheet2")
With w1
  .Activate
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  n = Application.CountIf(.Range("O2:O" & lr), "<>0")
  ReDim o(1 To n, 1 To 14)
  For Each d In .Range("O2:O" & lr)
    If d.Value <> 0 Then
      j = j + 1
      o(j, 1) = j
      o(j, 2) = d.Offset(, -14).Value
      For c = 3 To 14
        o(j, c) = .Cells(d.Row, c).Value
      Next c
    End If
  Next d
End With
With w2
  .UsedRange.ClearContents
  .Cells(1, 1).Resize(n, 14).Value = o
  .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.

I bow to an expert
ray:
Alan

 

[hiker95]

ssmith2015,

With the same screenshots as my reply #8:

The following macro is faster because it uses two arrays in memory.

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).

Sub ReorgData_V2()
' hiker95, 08/06/2014, ME797106
Dim w1 As Worksheet, w2 As Worksheet
Dim a As Variant, i As Long
Dim o As Variant, j As Long
Dim lr As Long, n As Long, c As Long
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")
Set w2 = Sheets("Sheet2")
With w1
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  a = .Range("A1:O" & lr).Value
  n = Application.CountIf(.Range("O2:O" & lr), "<>0")
  ReDim o(1 To n, 1 To 14)
End With
For i = 2 To lr
  If a(i, 15) <> 0 Then
    j = j + 1
    o(j, 1) = j
    o(j, 2) = a(i, 1)
    For c = 3 To 14
      o(j, c) = a(i, c)
    Next c
  End If
Next i
With w2
  .UsedRange.ClearContents
  .Cells(1, 1).Resize(n, 14).Value = o
  .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_V2 macro.