Summarising data

EvansB2

Board Regular
Joined
Nov 25, 2008
Messages
210
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

ABCDEFGH
1ProjectItemYear 11ProjectItemYear
2A1.1£5002A1.1£500
3B1.1£03C1.2£200
4C1.2£2004B1.3£400
5B1.3£4005C1.1£100
6B1.1£06A1.4£700
7C1.1£1007
8A1.4£700
9A1.2£0
TABLE 1TABLE 2
<colgroup><col width="29" style="width: 22pt; mso-width-source: userset; mso-width-alt: 1060;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;" span="2"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="64" style="width: 48pt;"> <col width="31" style="width: 23pt; mso-width-source: userset; mso-width-alt: 1133;"> <col width="64" style="width: 48pt;" span="3"> <tbody> </tbody>
 

hatstand

Well-known Member
Joined
Mar 17, 2005
Messages
729
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
 

EvansB2

Board Regular
Joined
Nov 25, 2008
Messages
210
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
 

hatstand

Well-known Member
Joined
Mar 17, 2005
Messages
729
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.
 

EvansB2

Board Regular
Joined
Nov 25, 2008
Messages
210
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
 

Flyboy65

New Member
Joined
Sep 26, 2012
Messages
13
Office Version
365
Platform
Windows
Ben, have you considered using Power Query?
 

EvansB2

Board Regular
Joined
Nov 25, 2008
Messages
210
I mean, I have excel 2016, would it be available as an add-in?

Ben
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,780
With your example Ctrl+T, filter by 0
or do the same in PowerQuery (PowerQuery is built-in into 2016 - Get&Transform)
 
Last edited:

EvansB2

Board Regular
Joined
Nov 25, 2008
Messages
210
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
 

JugglerJAF

Active Member
Joined
Feb 17, 2002
Messages
265
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.
 

Forum statistics

Threads
1,081,841
Messages
5,361,638
Members
400,642
Latest member
tekster23

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top