Share via

Summarizing data into another sheet in excel

Anonymous
2014-04-14T17:56:00+00:00

Hello all,

I have a spreadsheet (sheet1) with multiple columns that feeds off of a master sheet. As a result Sheet1 has "0" and blank cells that don't need to go into Sheet2, the summary sheet. How do I or what formula (probably an arrey one) do I use in the destination sheet (Sheet2) to populate the rows with complete values (meaning the cells with data only)?

Thank you in advance.

Sher

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Ashish Mathur 101.9K Reputation points Volunteer Moderator
2014-04-15T14:59:40+00:00

Hi,

Try this

  1. In sheet2, type the selective headings (of the 27 columns) from cell A1 to the right, say till T1.  When typing the headings, please ensure that the spelling of the headings in range A1:T1 (assumption) of sheet2 is the same as the spelling of the heading in range A1:Z1 (assumption) of sheet1
  2. In cell A2 of sheet2, enter this array formula (Ctrl+Shift+Enter) and then copy down/right

=IFERROR(INDEX(Sheet1!$A$2:$Z$24,SMALL(IF(Sheet1!$C$2:$C$24>0,IF(Sheet1!$E$2:$E$24>0,ROW(Sheet1!$A$2:$A$24)-ROW(Sheet1!$A$1))),ROW(1:1)),MATCH(A$1,$A$1:$Z$1,0)),"")

Change the $A$2:$Z$24 and $A$1:$Z$1 to your actual range.

Hope this helps.

Was this answer helpful?

0 comments

11 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2014-04-14T22:21:09+00:00

    Hi

    This macro assume that column E is empty or it has a 0.

    Try it on a sample sheet.

    Sub CopyValue()

    Dim sh2 As Worksheet, finalrow As Long, i As Long, lastrow As Long

    Set sh2 = Sheets("Sheet2")

    finalrow = Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To finalrow

    If Not IsEmpty(Cells(i, 5).Value) And Cells(i, 5).Value <> 0 Then

    lastrow = sh2.Cells(Cells.Rows.Count, 1).End(xlUp).Row

    Cells(i, 1).Resize(1, 5).Copy Destination:=sh2.Cells(lastrow + 1, 1)

    End If

    Next i

    End Sub

    In your sample, you have a $ sign and a dash, that's not empty or zero

    Was this answer helpful?

    0 comments
  2. Anonymous
    2014-04-14T21:59:09+00:00

    Anybody with any ideas...?

    Was this answer helpful?

    0 comments
  3. Anonymous
    2014-04-14T19:15:28+00:00

    JR,

    Than you for responding. Please see below examples of the origin Sheet 1 and destination Sheet 2. In sheet 1 record IDs 105 through 108 the Lease Cost and Extended Total were zeros, or empty. Those lines should not go into Sheet 2 (as shown below). The only lines that should go is with values and must be in line with each line, no shifting up and down. Thank you again for helping me with this.

    Sheet 1

    RECID DAR NUMBER LEASE COST <br><br>MONTHLY QUANTITY EXTENDED <br><br>TOTAL
    101 BBR00000037239 $        3,625.00 4.9 $    17,638.36
    102 BBR00000030246 $        3,481.00 4.9 $    16,937.69
    103 BBR00000030247 $        4,260.00 4.9 $    20,728.11
    104 BBR00000037240 $        4,032.00 4.9 $    19,618.72
    105 BBR000016037 $                     - 0.0 $                   -
    106 BBR00000037243 $                     - 0.0 $                   -
    107 BBR000016476 $                     - 0.0 $                   -
    108 BBR000016035 $                     - 0.0 $                   -
    109 BBR000016340 $        5,797.00 3.7 $    21,726.84
    110 BBR00000035276 $        5,406.00 3.7 $    20,261.39
    111 BBR000016115 $        3,481.00 4.9 $    16,937.69
    112 BBR00000037816 $     46,925.00 12.0 $  563,100.00
    113 BBR00000030197 $        3,600.00 12.0 $    43,200.00
    114 BBR00000030196 $        3,600.00 12.0 $    43,200.00
    115 BBR00000030198 $        3,600.00 12.0 $    43,200.00
    116 BBR00000030158 $        3,600.00 12.0 $    43,200.00
    117 BBR00000037491 $        2,875.00 12.0 $    34,500.00
    118 BBR00000037502 $        2,875.00 12.0 $    34,500.00
    119 BBR00000037195 $        2,875.00 12.0 $    34,500.00
    120 BBR000016493 $        2,875.00 12.0 $    34,500.00
    121 BBR00000037187 $        2,875.00 12.0 $    34,500.00
    122 BBR00000030202 $        3,420.00 12.0 $    41,040.00
    123 BBR00000030223 $        3,420.00 12.0 $    41,040.00

    Sheet 2

    RECID DAR NUMBER LEASE COST <br><br>MONTHLY QUANTITY EXTENDED <br><br>TOTAL
    101 BBR00000037239 $          3,625.00 4.9 $        17,638.36
    102 BBR00000030246 $          3,481.00 4.9 $        16,937.69
    103 BBR00000030247 $          4,260.00 4.9 $        20,728.11
    104 BBR00000037240 $          4,032.00 4.9 $        19,618.72
    109 BBR000016340 $          5,797.00 3.7 $        21,726.84
    110 BBR00000035276 $          5,406.00 3.7 $        20,261.39
    111 BBR000016115 $          3,481.00 4.9 $        16,937.69
    112 BBR00000037816 $        46,925.00 12.0 $      563,100.00
    113 BBR00000030197 $          3,600.00 12.0 $        43,200.00
    114 BBR00000030196 $          3,600.00 12.0 $        43,200.00
    115 BBR00000030198 $          3,600.00 12.0 $        43,200.00
    116 BBR00000030158 $          3,600.00 12.0 $        43,200.00
    117 BBR00000037491 $          2,875.00 12.0 $        34,500.00
    118 BBR00000037502 $          2,875.00 12.0 $        34,500.00
    119 BBR00000037195 $          2,875.00 12.0 $        34,500.00
    120 BBR000016493 $          2,875.00 12.0 $        34,500.00
    121 BBR00000037187 $          2,875.00 12.0 $        34,500.00
    122 BBR00000030202 $          3,420.00 12.0 $        41,040.00
    123 BBR00000030223 $          3,420.00 12.0 $        41,040.00

    Was this answer helpful?

    0 comments
  4. Anonymous
    2014-04-14T18:05:21+00:00

    Hi,

    Can you post some pictures how sheet 1 is looking and how you want to have sheet 2?

    Was this answer helpful?

    0 comments