just how do i do this???

Felafel

New Member
Joined
Sep 2, 2002
Messages
11
Here we go....I'm really having trouble finding the words for this one...

I have a number of rows that have data corresponding to a day. For example Monday 21/08/02 $1000, Tuesday 22/08/02 $2000...etc etc

What i want to do is to have a value for each week and not see all the crap that comes in between.

At the moment the rows are being transferred from another spreadsheet, what i want to happen is when they come through i want them to be sorted into weeks starting from the beginning of the current financial year.

So instead of having 5 rows for all the data from Monday to Friday I just have one row for the week beginning (eg) 24/04/02.

This requires sorting the stuff into the date ranges and adding up all the other values.....and i just have no idea where to begin.....

Does this make sense????

Please Help!!!
 
In keeping with the idea that if there is already functionality built in to excel to accomplish a task it would be daft to try to recreate it from scratch in code:

a) if your boss wants summary data but does not want a pivot table (which is built to do exactly that) then they're an **** (IMHO!). Easiest solution might be to convince them otherwise.
b) As MS Query can also be used to return summary data, why not take this opportunity to learn about it! Other than the initial set up, it's fairly straightforward.
c) If you really want to pursue a VBA solution, you'll have to post back with more info about how you're data's set up...

Pick an option

Paddy
 
Upvote 0

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
a) Don't think i haven't tried
b) if you can give me a quick run down of what it's about i'll consider it....but will it allow me to do what i want without having to update and stuff...i'll be leaving this place soon....
c) I can't get colo's thing to work...it won't paste to clipboard even though the thing gets generated in html...but i can give you a verbal rundown if you like!

so what do you think

thanks and btw....what's IMHO?
 
Upvote 0
"In My Humble Opinion"

Outline of steps to using MS Query to return data from an excel file:

Assumptions:

a) the file to be queried is called test.xls
b) the data range in the file is on sheet1!a1:d100
c) the results are going into a file called query.xls

First set - set up your data range
1) Open test.xls
2) Select the range a1:d100
3) Go to insert | name | define & give the range a name
4) Save & close test.xls
5) Open query.xls
6) Go to Data | Get External Data | Create new query
7) Click new data source. Give the source a name(e.g.test_query, select the Microsoft excel driver, click connect, select the appropriate excel version for text.xls, click select workbook & browse to test.xls. Click OK. This should have added test_query to the list of available data sources.

Second step - create the basic query
:cool: Select 'test_query' from the list of data sources & click OK
9) expand the list of available columns & select the ones you need, click next
10) select filter options, if needed, click OK
11) select sort order, if needed, click OK
12) Save query
13) Select 'View data or edit query in Microsoft Query', click finish. This should open MS Query proper.

Third step - tweek query as necessary
14) (for example) select one of the data columns, click the 'summation' button until you get the summary function you're after.
15) Click the 'return data' button
16) select a destination for the data in query.xls
17) Right click in the data area, select 'Data Range Properties'. Select the appropriate properties for your query (eg 'Refresh data on file open')


That's kind of it. Within the MS Query editor, there are all sorts of other options available, incluing access to an sql editor for more elaborate stuff...For example, if you had SQL skills, you could generate the 'summary by week' in the sql by creating a 'pivot' query on the date field. Alternatively, you could add the new 'year&weeknum' field (as described earlier) to the source data file, then use that in the query.

Last thing: to ensure that your table range in test.xls expands to cope with new data, you will need to make it dynamic. search this site for 'dynamic named range' for how to do this.

I know it looks a little involved, but each of the above steps are little more than mouse clicks, anbd once it's set up you don't have to worry about it ever again...

Paddy
 
Upvote 0
Hi there
You could try something like this:

Enter your "Monday 1/9/02 $1025.50" type data in column A starting A2.
Use Data / Text to Columns to split your data into columns A, B & C (Day,Date,Amount)
In D2 put this formula =WEEKDAY(B2,1)


In E2 put this formula =IF(D2<D1,"W/C Sunday "&TEXT(B2-D2+1,"d/mm/yy"),"")


in F2 put this formula =IF(E2<>"",SUM(C2:OFFSET(C2,(MATCH((B2-D2+8),B3:B14,0)-1),0)),"")
Scroll all formulas down

You can macro record this to automate

regards
Derek
This message was edited by Derek on 2002-09-17 21:00
 
Upvote 0
Hi
The system seems determined not to post the formula for E2 correctly (the formula for F2 looks okay)

I will try the formula for E2 again here:
=IF(D2<D1,"W/C Sunday "&TEXT(B2-D2+1,"d/mm/yy"),"")

And incase it doesn't post again, here it is in longhand:
equals if bracket D2 less-than D1 comma open-quotes W/C Sunday space close-quotes apersand Text bracket B2-D2+1 comma open-quotes d/mm/yy close-quotes close bracket comma double-quotes close bracket.

fingers crossed
Derek
 
Upvote 0
In E2 put this formula =IF(D2 less-than-sign D1,"W/C Sunday "&TEXT(B2-D2+1,"d/mm/yy"),"")
 
Upvote 0
Derek - the board's misinterpreting your< sign as the beginning of an html tag - turn off html on the post & it should be fine.

It's also still there if you press edit:

=IF(D2<D1,"W/C Sunday "&TEXT(B2-D2+1,"d/mm/yy"),"")

Paddy
This message was edited by PaddyD on 2002-09-17 21:17
 
Upvote 0
Okay the formula for E2 has now posted correctly but you will have to replace my "less-than-sign" with the symbol on your keyboard.
Hope this makes sense

regards
Derek
 
Upvote 0
Thanks Paddy, I was getting quite frustrated for a while. I'll give it a try.
regards
Derek

Enter your "Monday 1/9/02 $1025.50" type data in column A starting A2.
Use Data / Text to Columns to split your data into columns A, B & C (Day,Date,Amount)
In D2 put this formula =WEEKDAY(B2,1)

In E2 put this formula =IF(D2< D1,"W/C Sunday "&TEXT(B2-D2+1,"d/mm/yy"),"")

in F2 put this formula =IF(E2<>"",SUM(C2:OFFSET(C2,(MATCH((B2-D2+8),B3:B14,0)-1),0)),"")

Scroll all formulas down

You can macro record this to automate

PS You will need to sort your original data into date order first of course.
This message was edited by Derek on 2002-09-17 21:23
 
Upvote 0
Hi again
On reviewing my post, I realise my test dates ran Sun to Sat and the formulas won't cope if dates are missing. So I have revised it as follows

Enter your data in Column A from A2
Data to Columns (A,B,C)
Sort data into date order, then

Type a large number in D1 (say 88)
Formula in D2 is =WEEKDAY(B2,1)
Formula in E2 is =IF(F2<>"",ROW(),"")
Formula in F2 is =IF(D2<D1,"W/C Sunday "&TEXT(B2-D2+1,"d/mm/yy"),"")
Formula in G2 is =IF(F3="","",SUM(C3:OFFSET(C3,MATCH(MIN(E4:E2000),E4:E200,0)-1,0)))
Scroll formulas down
Note: to get the sum for the final week you need to add a large number in Column E on the row immediately below your final row of data.

If you delete a row/s of data you will need to scroll all formulas
down again from the top.

This now seems to work okay for me, regardless of whether all days of
the week are represented. Test it first.

regards
Derek
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

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