Summarising data

EvansB2

Board Regular
Joined
Nov 25, 2008
Messages
245
Office Version
  1. 365
Platform
  1. Windows
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>
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
I mean, I have excel 2016, would it be available as an add-in?

Ben
 
Upvote 0
With your example Ctrl+T, filter by 0
or do the same in PowerQuery (PowerQuery is built-in into 2016 - Get&Transform)
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top