Removing weekends and holidays from trading price data set

[ghexer]

I have the table below - I would like to create a new table that references this table but ignores days with zeros (those are non trading days) So in essence it would look exactly like the table below but 4/14/2017 - 4/16/2017 (for example) would be deleted and the third date would be 4/13/2017. Obviously i want this to be automated so deleting the rows by hand aint gonna cut it. Any ideas??

Date	Company A	S&P 500
4/18/2017	77.39	20523.281
4/17/2017	77.08	20636.922
4/16/2017	0	0
4/15/2017	0	0
4/14/2017	0	0
4/13/2017	75.4	20453.25
4/12/2017	77.31	20591.86
4/11/2017	79.18	20651.3
4/10/2017	77.76	20658.02
4/9/2017	0	0
4/8/2017	0	0
4/7/2017	78.39	20656.1
4/6/2017	77.87	20662.95
4/5/2017	76.41	20648.15
4/4/2017	77.36	20689.24
4/3/2017	77.19	20650.21
4/2/2017	0	0
4/1/2017	0	0
3/31/2017	78.81	20663.22
3/30/2017	78.24	20728.49
3/29/2017	77.6	20659.32
3/28/2017	78.6	20701.5
3/27/2017	77.7	20550.98
3/26/2017	0	0
3/25/2017	0	0
3/24/2017	78.35	20596.72

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[Eric W]

Welcome to the forum.

Easiest way is to just filter the table. Select one of the columns with the 0s, from the Home tab click Sort & Filter > Filter. Then click the down arrow at the top of the column and uncheck the 0. You can then copy those columns and paste them to another sheet, and you'll only get the visible rows.

Other than that, you could create an array formula that copies over the rows you want to another location. But if there are enough, that will slow down your sheet.

Another option would be to write a macro to perform the function in an "on-demand" manner, but it wouldn't save you much over the filter method.


Hope this helps!