Summarising data

EvansB2

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

EvansB2

Board Regular
Joined
Nov 25, 2008
Messages
206
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,206
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
206
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
264
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,077,662
Messages
5,335,561
Members
399,024
Latest member
rokcel389

Some videos you may like

This Week's Hot Topics

Top