Link transposed data from one sheet to another for DB purposes

[Tina]

I have a spreadsheet which contains a series of data that spreads accross
many columns and four fows. This data repeats itself over and over every four
rows down. In the example below, each course is offered in four rooms, the
following columns represent days 1-5 and the hours logged in each room for
the specific class.

Sheet1 contains:
Course Room 1 2 3 4 5
Transition Red 8 8 8 8 8
Transition Blue 2 2 2 5 5
Transition Green 0 0 0 0 0
Transition Yellow 1 2 2 2 2
STAR Red 8 8 8 8 2
STAR Blue 2 2 1 1 1
STAR Green 4 4 4 4 4
STAR Yellow 8 8 8 8 8

What I need to do, is keep this spreadsheet view for easy to data enter
purposes but link it to another spreadsheet that converts it into standard
database format. See example below:

Sheet2 contains:
Course Room Day Hrs
Transition Red 1 8
Transition Red 2 8
Transition Red 3 8
Transition Red 4 8
Transition Red 5 8
Transition Blue 1 2
Transition Blue 2 2
Transition Blue 3 2
Transition Blue 4 5
Transition Blue 5 5
etc ...

Is there a simple formula I can enter into each column that I can easily
drag/copy down the rows to continue to select the repeating series of 4 rows
and 7 columns? I'd like to have the formula so that I can continue to drag it
down to link to any new data entered at the end of the table that the data is
entered in.

Can you help me?

 

[Carlo]

That's how i would do it

Sheet1 = sourcedata
Sheet2 = Data for import in database

on Sheet2!A2 put following formula:
=IF(INDIRECT("Sheet1!A" &
(ROW()-2-MOD(ROW()-2,5))/5+2)="","",INDIRECT("Sheet1!A" &
(ROW()-2-MOD(ROW()-2,5))/5+2))

on Sheet2!B2:
=IF(INDIRECT("Sheet1!A" &
(ROW()-2-MOD(ROW()-2,5))/5+2)="","",INDIRECT("Sheet1!B" &
(ROW()-2-MOD(ROW()-2,5))/5+2))

on Sheet2!C2:
=IF(INDIRECT("Sheet1!A"&(ROW()-2-MOD(ROW()-2,5))/5+2)="","",INDIRECT("sheet1!"&ADDRESS((ROW()-2-MOD(ROW()-2,5))/5+2,MOD(ROW()-2,5)+3)))

Those formulas can now be copied down until row 65536 and works.

If you want to start on a different row then 2 you have to change some
numbers.
(Some 2 have to be changed in to another rownumber, but not every 2!)
Just ask if you need help.

Carlo

 

[Carlo]

Sorry, just saw that i forgot your Day Column!!

Put the formula from C2 that i told you in the post before, into D2 and in
C2 put:
=MOD(ROW()-2,5)+1

the same goes here with the 2nd row!!

Carlo

 

[Max]

Another play which should deliver it ..

Assuming source data in Sheet1 is within cols A to G as posted, from row1 down

In Sheet2,

In A2:
=OFFSET(Sheet1!$A$2,INT((ROWS($1:1)-1)/5),)

In B2:
=OFFSET(Sheet1!$B$2,INT((ROWS($1:1)-1)/5),)

In C2:
=OFFSET(Sheet1!$C$1,,MOD(ROWS($1:1)-1,5))

In D2:
=OFFSET(Sheet1!$C$2,INT((ROWS($1:1)-1)/5),MOD(ROWS($1:1)-1,5))

Select A22, copy down as far as required.

 

[Tina]

That works absolutely BEAUTIFUL! Can you tell me, based on the formula you
provided, if I have 80 days, rather than 5, where do I tweak the formula?

Thanks!
Tina

 

[Max]

Just change the "5" within the formulas to "80" if you have 80 days instead
of 5

 

[Tina]

THANK YOU! You don't know how many hours you just saved me!!! Now I just
need to figure out what exactly this formula means and how it's working.

 

[Max]

welcome, Tina.

Tinker with these in 2 empty cols to see
the series / pattern generated by the core INT and MOD parts
(which supply the required returns for the row/col params in OFFSET)

In any cell: =INT((ROWS($1:1)-1)/5)
copied down will produce an incrementing series of:
5 zeros, then 5 ones, then 5 twos, and so on

Similarly, in any cell: =MOD(ROWS($1:1)-1,5)
copied down will yield a repeating continuous series of 5 numbers: 0,1,2,3,4

 

[YMTEO]

Hi Max,

I saw your advice to Tina.
I had a slightly more complicated worsheet than Tina and was trying to use
your advise. But it doesn't work that well.
Maybe I do not understand the formular clearly.
Wondering whether can you help me in that?

Source Data:-
Customer Country1 Name Data Aug-07 Sep-07 Oct-07
Cust A Malaysia Apple Sales 7,253 2,115
Cust A Malaysia Apple Volume 480 140
Cust A Malaysia Apple SASP 15.110 15.110
Cust A Malaysia Orange Sales 3,050 10,750 11,150
Cust A Malaysia Orange Volume 15,250 53,750 55,750
Cust A Malaysia Orange SASP 0.200 0.200 0.200
Cust B US Pear Sales 2,369
Cust B US Pear Volume 360
Cust B US Pear SASP 6.580
Cust B US Orange Sales 6,075 9,113 13,669
Cust B US Orange Volume 900 1,350 2,025
Cust B US Orange SASP 6.750 6.750 6.750
Cust C China Pear Sales 570 570 760
Cust C China Pear Volume 30 30 35
Cust C China Pear SASP 19.000 19.000 21.714
Cust D US Cherry Sales 5,832 4,860
Cust D US Cherry Volume 129,600 108,000
Cust D US Cherry SASP 0.045 0.045
Cust D US Orange Sales 720 24,000 18,000
Cust D US Orange Volume 4 120 90
Cust D US Orange SASP 200.000 200.000 200.000


Need to transpose to data to col into:-
Customer, cty, name date, Sales, vol and SASP

Customer Country1 Name Date Sales Volume SASP
Cust A Malaysia Apple Aug-07 7252.8 480 15.11
Cust A Malaysia Apple Oct-07 2115.4 140 15.11
Cust A Malaysia Orange Aug-07 3,050 15,250 0.200
Cust A Malaysia Orange Sep-07 10,750 53,750 0.200
Cust A Malaysia Orange Oct-07 11,150 55,750 0.200
etc
etc

Can you kindly help me?

Thank

 

[Max]

Data is in sheet: Source data

In sheet: final,
In A3: ='source data'!A2
Copy A3 to C3

In D3:
=OFFSET('source data'!$E$1,,MOD(ROWS($1:1)-1,3),)

In E3:
=OFFSET('source data'!$E$2,INT((ROWS($1:1)-1)/3)*3,MOD(ROWS($1:1)-1,3),)

In F3:
=OFFSET('source data'!$E$3,INT((ROWS($1:1)-1)/3)*3,MOD(ROWS($1:1)-1,3),)

In G3:
=OFFSET('source data'!$E$4,INT((ROWS($1:1)-1)/3)*3,MOD(ROWS($1:1)-1,3),)

Select A3:G3, copy down as far as required

 

[YMTEO]

Hi Max,

Thanks.
Last question, if the data is now from Jan - Dec, rather than Aug - Oct
Must I change the number from "3" to "12"?

YM Teo

 

[YMTEO]

Max,

Just to let you know, I had already figured out how to extend from 3 months
data to 12 months data.

THANKS FOR ALL YOUR GREAT HELP!!!
YM Teo

 

[Max]

Yes, the top cell OFFSETs in D3:G3 would now be:
=OFFSET('source data'!$E$1,,MOD(ROWS($1:1)-1,12),)
=OFFSET('source data'!$E$2,INT((ROWS($1:1)-1)/12)*12,MOD(ROWS($1:1)-1,12),)
=OFFSET('source data'!$E$3,INT((ROWS($1:1)-1)/12)*12,MOD(ROWS($1:1)-1,12),)
=OFFSET('source data'!$E$4,INT((ROWS($1:1)-1)/12)*12,MOD(ROWS($1:1)-1,12),)
--
Max
Singapore
http://savefile.com/projects/236895
Files:114, Sub: 53, Downloads: 15,500
xdemechanik

 

[Max]

Good to hear that. You're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
Files:114, Sub: 53, Downloads: 15,500
xdemechanik

 

[YMTEO]

Hello Max,

I am working on some tables for forecasting.

Currently, to send an input table out to the sales man, I have to convert a
pivot table (with data) to regular table with formulas.
I have to copy the pivot and paste special values and manually add back my
formulas, ex =SUM. So that the sales man can overwrite the existing amount in
the table and not effecting the original source data.

Is there a more efficent way of doing this?
Currently the pivot tables has many sub total, and you can imagine how many
times I need to insert the formular into the regular table.

 

[Max]

Suggest you put in any new queries as fresh, brand new postings,
with its own relevant subject line.
That's the correct to use these newsgroups.

Maybe you could try posting in .programming for your new query
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:15,700 Files:353 Subscribers:53
xdemechanik
---

 

[YMTEO]

will do. I will post it a on a new brank subject line under programming