|
Link transposed data from one sheet to another for DB purposes
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? |
Link transposed data from one sheet to another for DB purposes
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 "Tina" wrote: 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? |
Link transposed data from one sheet to another for DB purposes
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 "Tina" wrote: 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? |
Link transposed data from one sheet to another for DB purposes
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 A2:D2, copy down as far as required. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tina" wrote: 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? |
Link transposed data from one sheet to another for DB purposes
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 "Carlo" wrote: 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 "Tina" wrote: 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? |
Link transposed data from one sheet to another for DB purposes
Just change the "5" within the formulas to "80" if you have 80 days instead
of 5 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Link transposed data from one sheet to another for DB purposes
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" wrote: Just change the "5" within the formulas to "80" if you have 80 days instead of 5 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Link transposed data from one sheet to another for DB purposes
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tina" wrote in message ... 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. |
Link transposed data from one sheet to another for DB purposes
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" wrote: 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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tina" wrote in message ... 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. |
Link transposed data from one sheet to another for DB purposes
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "YMTEO" wrote: 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 |
Link transposed data from one sheet to another for DB purposes
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 "Max" wrote: 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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "YMTEO" wrote: 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 |
Link transposed data from one sheet to another for DB purposes
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" wrote: 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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "YMTEO" wrote: 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 |
Link transposed data from one sheet to another for DB purposes
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 --- "YMTEO" wrote in message ... 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 |
Link transposed data from one sheet to another for DB purposes
Good to hear that. You're welcome.
-- Max Singapore http://savefile.com/projects/236895 Files:114, Sub: 53, Downloads: 15,500 xdemechanik --- "YMTEO" wrote in message ... 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 |
Changing Pivot Table to Formular Table
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. |
Changing Pivot Table to Formular Table
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 --- |
Changing Pivot Table to Formular Table
will do. I will post it a on a new brank subject line under programming
"Max" wrote: 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 --- |
| All times are GMT +1. The time now is 07:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com