Formula Please

[Steved]

Hello from Steved

Firstly I import Data into three Columns
Col A = Start of the month to end of month (217 Rows)
Col B = Day ie =TEXT(a1 "ddd)
Col C = Data ie 34779
Each Day has 7 cities of data

Col E Sun,Col F Mon,Col G Tue,Col H Wed,Col I Thu,Col J Fri
Col K Sat as row headings.

ok I can use Vlookup Function for the first City and then
use Offset Fuction for the following 6 cities for each day.
I want to split the month into 4 weeks their lies my
situation, how do I right a Formula for this or is it
possible to be done in a Pivot Table if so how.
Thankyou.

 

[Guest]

It would help to see an example of some of your data. Could you perhaps attach a fragment from your worksheet?

 

[Steved]

Below is an example
What I have done is cut and pasted Tue Col C to Col G
Now I need A formula for this.
A B C D E F G
1-Jun Tue 39197 Sun Mon Tue
1-Jun Tue 35526 39197
1-Jun Tue 7356 35526
1-Jun Tue 24065 7356
1-Jun Tue 20703 24065
1-Jun Tue 4684 20703
1-Jun Tue 19233 4684
2-Jun Wed 38862 19233
2-Jun Wed 35646
2-Jun Wed 7350
2-Jun Wed 25333
2-Jun Wed 20379 Sun Mon Tue
2-Jun Wed 4613
2-Jun Wed 19513
3-Jun Thu 38769
3-Jun Thu 35102
3-Jun Thu 7647
3-Jun Thu 25856
3-Jun Thu 20478
3-Jun Thu 4644
3-Jun Thu 19393
4-Jun Fri 38847
4-Jun Fri 33414 Sun Mon Tue
4-Jun Fri 6833

-----Original Message-----
It would help to see an example of some of your data.

Could you perhaps attach a fragment from your worksheet?

 

[Guest]

I see.

You need a couple of helper columns then you can create a pivot table quite easily.

1. Insert new columns D and E.

2. In column D enter the names of the cities, or create a formula to enter them, or number them, but find a way of distinguishing them otherwise the pivot table will combine them together.

3. In E1, enter the following formula and fill down:

=WEEK(A1)

4. Insert a new Row 1 and enter headings for Columns A to E - e.g.: A1 - "Date"; B1 - "Day"; C1 -"Results"; D1 - "City"; E1 - "Week"

5. Create a pivot table using data from columns A to E.
- Row headings Week and City
- Column heading Day
- Data Results
- Modify subtotals, formats etc as required.


Hope this helps

 

[Steved]

Thankyou Vaughan.

-----Original Message-----
I see.

You need a couple of helper columns then you can create a pivot table quite easily.

1. Insert new columns D and E.

2. In column D enter the names of the cities, or create a

formula to enter them, or number them, but find a way of
distinguishing them otherwise the pivot table will combine
them together.

3. In E1, enter the following formula and fill down:

=WEEK(A1)

4. Insert a new Row 1 and enter headings for Columns A to

E - e.g.: A1 - "Date"; B1 - "Day"; C1 -"Results"; D1 -
"City"; E1 - "Week"

 

[Steved]

Thankyou Vaughan.

-----Original Message-----
I see.

You need a couple of helper columns then you can create a pivot table quite easily.

1. Insert new columns D and E.

2. In column D enter the names of the cities, or create a

formula to enter them, or number them, but find a way of
distinguishing them otherwise the pivot table will combine
them together.

3. In E1, enter the following formula and fill down:

=WEEK(A1)

4. Insert a new Row 1 and enter headings for Columns A to

E - e.g.: A1 - "Date"; B1 - "Day"; C1 -"Results"; D1 -
"City"; E1 - "Week"