Create a shorter list of dates

[edeaston]

Hi,

I have downloaded a set of data showing the number of hits on my website
which has data by day. I want to create a subset of this data which excludes
the weekend, while at the same time retaining the original data.

My data is in the following format:
Day ; Date ; Number of hits (where ; represents a new column)

So the data I paste on Worksheet 1
Friday ; 1/1/2009 ; 25
Saturday ' 2/1/2009 ; 5
Sunday ' 3/1/2009 ; 4
Monday ; 4/1/2009 ; 50

Would appear on worksheet 2 as
Friday ; 1/1/2009 ; 25
Monday ; 4/1/2009 ; 50

I know I could copy the data, filter it and remove the rows with Saturday
and Sunday in but I would quite like to just paste my data in the original
worksheet and see the updates appear on another worksheet

Any ideas? I think a combination of AND and MIN should do it but cant quite
solve it

 

[Stefi]

I know I could copy the data, filter it and remove the rows with Saturday

and Sunday in but I would quite like to just paste my data in the original
worksheet and see the updates appear on another worksheet

Do once these steps while recording them as a macro! Having assigned a hot
key to your new macro later you can repeat the action by a single keystroke.


Regards,
Stefi

„edeaston†ezt írta:

 

[edeaston1983]

Hi Stefi,

Thanks for the suggestion but I would really like to not get macro's
involved as this workbook will be used by people who are not great with Excel
and I think a macro might make their heads explode!

Any ideas on a formula based approach?

Thanks

Ed

 

[Stefi]

You cannot delete data (rows) by formulae! Maybe you can hide them by filters
if your data structure allows it. E.g. if dates are in column A then enter in
an empty helper column, say B, this formula:
=WEEKDAY(A2,2)

you can apply custom Autofilter on column B with <6 as condition.

Regards,
Stefi

If you have always two name element separated by one space then, the name
being in A1:
=MID(A1,SEARCH(" ",A1)+1,256)&" "&LEFT(A1,SEARCH(" ",A1)-1)
Adjust cell reference and fill it down as necessary!

Regards,
Stefi



„edeaston1983†ezt írta:

 

[edeaston1983]

Hi Stefi,

I think I need to be a little clearer in what I am after. Firstly I am not
trying to delete any data - I am looking to have two sets of data, one which
is the original (on worksheet 1) and one which references that data (on
worksheet 2) but does not display the rows which are weekends.
Secondly, my data is all in seperate columns so we dont need to worry about
seperating named elelements - but thanks for the formula, that looks very
handy!

The way I have tried to get the reduced list so far is as follows:
The first day is definitely Monday 2nd February 2009 - this will not change
For the next row I have tried to set up a formula which looks for the
minimum date in Worksheet 1 date column which is greater than the date in the
cell above AND display it if the day in the corresponding day is not Saturday
or Sunday

Worksheet 1:
Date (A) ; Day (B); Number (C) -- where ; indicates a column change
1) 2/2/2009 ; Mon ; 1
2) 3/2/2009 ; Tue ; 2
3) 4/2/2009 ; Wed ; 4
4) 5/2/2009 ; Thu ; 8
5) 6/2/2009 ; Fri ; 16
6) 7/2/2009 ; Sat ; 32
7) 8/2/2009 ; Sun ; 64
8) 9/2/2009 ; Mon ; 128

On worksheet 2:
Date (A) ; Day (B); Number (C) -- where ; indicates a column change
1) 2/2/2009 ; Mon ; 1-- all linked to worksheet 1
Here I am trying to say select the minimum date from W1!A:A that is greater
than D1 AND where the corresponding day cell in worksheet 1. In theory when
copied down this should appear as

Date (A) ; Day (B); Number (C)
1) 2/2/2009 ; Mon ; 1
2) 3/2/2009 ; Tue ; 2
3) 4/2/2009 ; Wed ; 4
4) 5/2/2009 ; Thu ; 8
5) 6/2/2009 ; Fri ; 16
6) 9/2/2009 ; Mon ; 128

Does that make it clearer? Its quite tough to communicate it using just this
form!

Thanks in advance

Ed

 

[Jarek Kujawa]

man, you SURELY meant sth. like this one (data/weekdays starting in A1
in Sheet1):

in Sheet2 insert

=OFFSET(Sheet1!A1,2*ROUNDUP((ROW()-1)/5,0),2)

you'll need to give me 5 stars if this formula proves correct, pal

;-)))

pls pay attention however that I did not test it for days with no
"hits" - do relevant days show up in the list in such cases?

HIH

 

[Jarek Kujawa]

of course I meant

"in A1 in Sheet2 insert"

sorry

 

[Stefi]

Hi Jarek,

You deserved only 4 stars because there is a little bug in your formula and
you forget to mention to fill the formula to the right and down! The right
one is

=OFFSET(Sheet1!A1,2*ROUNDUP((ROW()-1)/5,0),0)

In the meanwhile I came to a similar solution:
In A2 in Sheet2:

=INDIRECT(ADDRESS(ROW()+INT((ROW()-2)/5)*2,COLUMN(),4,1,"Sheet1"))
and fill it to the right and down!

But yours is nicer because it's shorter.

Regards,
Stefi


„Jarek Kujawa†ezt írta:

 

[edeaston1983]

After a little bit of tweaking and changing I managed to get it to work with
my dataset so here you go:

*****

Nice work, thats a handy formula to have.

Thanks for your help

Ed

 

[Jarek Kujawa]

5 + 4 = 9
I did not expect more than 5
thks Stefii, anyway
;-)))

 

[Jarek Kujawa]

welcome
would you mind clicking on some of the stars above to evaluate my
post?
;-)

thks

 

[Jarek Kujawa]

Steffi
yr formula is better of course
thks for your attention
;-)