Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Summarising data
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2008
    Location
    London
    Posts
    204
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Summarising data

    Hi there,

    I have copied samples in the form of two data tables and in essence, I would like Table 2, columns F to H, to return data from Table 1, only when the value in column C is greater than zero. Typically, Table 1 will run to thousands of rows and so it would be good to only summarise relevant data.

    Any help would be much appreciated.

    Ben

    A B C D E F G H
    1 Project Item Year 1 1 Project Item Year
    2 A 1.1 500 2 A 1.1 500
    3 B 1.1 0 3 C 1.2 200
    4 C 1.2 200 4 B 1.3 400
    5 B 1.3 400 5 C 1.1 100
    6 B 1.1 0 6 A 1.4 700
    7 C 1.1 100 7
    8 A 1.4 700
    9 A 1.2 0
    TABLE 1 TABLE 2

  2. #2
    Board Regular
    Join Date
    Mar 2005
    Location
    UK, Newcastle
    Posts
    729
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Summarising data

    Hi ,

    One way would be to use the code below. Hope this helps

    Sub ShrinkList()
    Application.ScreenUpdating = False

    If Not ActiveSheet.AutoFilterMode Then 'Check for filter
    Range("A1:C1").AutoFilter
    Else:
    End If

    Range("$F$2:$H$" & Range("F1").End(xlDown).Row).ClearContents 'Clear existing List

    ActiveSheet.Range("$A$1:$C$" & Range("A1").End(xlDown).Row).AutoFilter Field:=3, Criteria1:=">0" 'Filter for anything above zero in column c
    Range("A2:C8").Copy Destination:=Range("F2") '
    ActiveSheet.Range("$A$1:$C$9").AutoFilter Field:=3

    Application.ScreenUpdating = True
    End Sub

  3. #3
    Board Regular
    Join Date
    Nov 2008
    Location
    London
    Posts
    204
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Summarising data

    Thanks for the reply. I was hoping somewhat that I could get away with not writing code on the basis it will go to other users. Would a VLOOKUP or INDEX MATCH not work?

    Regards

    Ben

  4. #4
    Board Regular
    Join Date
    Mar 2005
    Location
    UK, Newcastle
    Posts
    729
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Summarising data

    You could use a formula if you want or you could set a range to call the code if you added a new entry. Personally the code would be a better option, but if a formula suits you better I'll try knocking one up for you.

  5. #5
    Board Regular
    Join Date
    Nov 2008
    Location
    London
    Posts
    204
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Summarising data

    I would like to take up your offer and look at a formula. This will assist me in the future, when typically I will have to make some adjustments, be it to the range or adding columns.

    Much appreciatd,

    Ben

  6. #6
    New Member
    Join Date
    Sep 2012
    Location
    Canada
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Summarising data

    Ben, have you considered using Power Query?

  7. #7
    Board Regular
    Join Date
    Nov 2008
    Location
    London
    Posts
    204
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Summarising data

    I mean, I have excel 2016, would it be available as an add-in?

    Ben

  8. #8
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,805
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Summarising data

    With your example Ctrl+T, filter by 0
    or do the same in PowerQuery (PowerQuery is built-in into 2016 - Get&Transform)
    Last edited by sandy666; Oct 5th, 2018 at 11:18 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  9. #9
    Board Regular
    Join Date
    Nov 2008
    Location
    London
    Posts
    204
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Summarising data

    I have had a go in the Get & Transform area and it looks like the Ctrl+T doesn't allow you to pick up data in another worksheet, only the one you are in?

    Ben

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    North Yorkshire, UK
    Posts
    263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Summarising data

    Copy the data from sheet1 to sheet2, filter to show values of zero, then delete those rows (delete entire row, don't just clear the cell contents).

    That will give you the truncated list.
    It's curtains for your Dr. Horrible ... lacy, gently wafting curtains.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •