Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
need a formula to transpose sets of data from Sheet1 to Sheet2
What formula do I need to traspose sets of data from Sheet1 to Sheet2?
the data comes in sets of 8, sheet1 looks like this (from L3) L M 3 POCKET 1 4 DIE 1 5 CC-AUTO 12 6 CC-CALC 4 7 EB 3 8 CC-FLOW 6 9 EB-FLOW 1 10 Total Void 42 11 POCKET 2 12 DIE 1 13 CC-AUTO 23 14 CC-CALC 0 15 EB 2 16 CC-FLOW 12 17 EB-FLOW 2 18 Total Void 37 Sheet2 needs to look like this (from C7) C D E F G H I J 7 POCKET Die CC-AUTO CC-CALC EB CC-FLOW EB-FLOW Total Void 1 1 12 4 3 6 1 42 2 1 23 0 2 12 2 37 I want to look for a match the Headers on Sheet2 ("POCKET","Die","CC-AUTO","CC-CALC","EB","CC-FLOW","EB-FLOW","Total Void") in Sheet1, Column L3+ and get the value of Column M3+. This is what I have working so far.. =OFFSET('Sheet1'!$L3,MATCH(C7,'Sheet2'!$L:$L,0)*0, 1) How do I make this formula skip every 8 rows? Thanks, CJ |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
need a formula to transpose sets of data from Sheet1 to Sheet2
In C7
=OFFSET(Sheet1!$L$3,(COLUMN()-3)+(((ROW()-7)/2)*8),0) in C8 =OFFSET(Sheet1!$M$3,(COLUMN()-3)+(((ROW()-8)/2)*8),0) then just fill them both to the right over to column J. When you fill down the sheet, make sure you select both C7 and C8 before starting the fill down the sheet, that'll get Sheet1 row L11 into C9 and M11 into C10 "cj2k2k" wrote: What formula do I need to traspose sets of data from Sheet1 to Sheet2? the data comes in sets of 8, sheet1 looks like this (from L3) L M 3 POCKET 1 4 DIE 1 5 CC-AUTO 12 6 CC-CALC 4 7 EB 3 8 CC-FLOW 6 9 EB-FLOW 1 10 Total Void 42 11 POCKET 2 12 DIE 1 13 CC-AUTO 23 14 CC-CALC 0 15 EB 2 16 CC-FLOW 12 17 EB-FLOW 2 18 Total Void 37 Sheet2 needs to look like this (from C7) C D E F G H I J 7 POCKET Die CC-AUTO CC-CALC EB CC-FLOW EB-FLOW Total Void 1 1 12 4 3 6 1 42 2 1 23 0 2 12 2 37 I want to look for a match the Headers on Sheet2 ("POCKET","Die","CC-AUTO","CC-CALC","EB","CC-FLOW","EB-FLOW","Total Void") in Sheet1, Column L3+ and get the value of Column M3+. This is what I have working so far.. =OFFSET('Sheet1'!$L3,MATCH(C7,'Sheet2'!$L:$L,0)*0, 1) How do I make this formula skip every 8 rows? Thanks, CJ |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
need a formula to transpose sets of data from Sheet1 to Sheet2
JLatham,
You are a genius! I got it to work. I had to tweak the offset a little. =OFFSET(Sheet1!$L$3,(COLUMN()-3)+(((ROW()-8)/1)*8),1) Thanks a million! "JLatham" wrote: In C7 =OFFSET(Sheet1!$L$3,(COLUMN()-3)+(((ROW()-7)/2)*8),0) in C8 =OFFSET(Sheet1!$M$3,(COLUMN()-3)+(((ROW()-8)/2)*8),0) then just fill them both to the right over to column J. When you fill down the sheet, make sure you select both C7 and C8 before starting the fill down the sheet, that'll get Sheet1 row L11 into C9 and M11 into C10 "cj2k2k" wrote: What formula do I need to traspose sets of data from Sheet1 to Sheet2? the data comes in sets of 8, sheet1 looks like this (from L3) L M 3 POCKET 1 4 DIE 1 5 CC-AUTO 12 6 CC-CALC 4 7 EB 3 8 CC-FLOW 6 9 EB-FLOW 1 10 Total Void 42 11 POCKET 2 12 DIE 1 13 CC-AUTO 23 14 CC-CALC 0 15 EB 2 16 CC-FLOW 12 17 EB-FLOW 2 18 Total Void 37 Sheet2 needs to look like this (from C7) C D E F G H I J 7 POCKET Die CC-AUTO CC-CALC EB CC-FLOW EB-FLOW Total Void 1 1 12 4 3 6 1 42 2 1 23 0 2 12 2 37 I want to look for a match the Headers on Sheet2 ("POCKET","Die","CC-AUTO","CC-CALC","EB","CC-FLOW","EB-FLOW","Total Void") in Sheet1, Column L3+ and get the value of Column M3+. This is what I have working so far.. =OFFSET('Sheet1'!$L3,MATCH(C7,'Sheet2'!$L:$L,0)*0, 1) How do I make this formula skip every 8 rows? Thanks, CJ |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
need a formula to transpose sets of data from Sheet1 to Sheet2
You're welcome. I almost messed it up completely - didn't realize until a
little late in the game that it was grouped in repeating sets of 8 and that you'd probably want to continue not just across the sheet, but down and put each set of 8 on different row, and THEN working with 2 columns tossed yet another twist in it. You're lucky I was well into my 3rd cup of morning coffee when I went to work on that one! "cj2k2k" wrote: JLatham, You are a genius! I got it to work. I had to tweak the offset a little. =OFFSET(Sheet1!$L$3,(COLUMN()-3)+(((ROW()-8)/1)*8),1) Thanks a million! "JLatham" wrote: In C7 =OFFSET(Sheet1!$L$3,(COLUMN()-3)+(((ROW()-7)/2)*8),0) in C8 =OFFSET(Sheet1!$M$3,(COLUMN()-3)+(((ROW()-8)/2)*8),0) then just fill them both to the right over to column J. When you fill down the sheet, make sure you select both C7 and C8 before starting the fill down the sheet, that'll get Sheet1 row L11 into C9 and M11 into C10 "cj2k2k" wrote: What formula do I need to traspose sets of data from Sheet1 to Sheet2? the data comes in sets of 8, sheet1 looks like this (from L3) L M 3 POCKET 1 4 DIE 1 5 CC-AUTO 12 6 CC-CALC 4 7 EB 3 8 CC-FLOW 6 9 EB-FLOW 1 10 Total Void 42 11 POCKET 2 12 DIE 1 13 CC-AUTO 23 14 CC-CALC 0 15 EB 2 16 CC-FLOW 12 17 EB-FLOW 2 18 Total Void 37 Sheet2 needs to look like this (from C7) C D E F G H I J 7 POCKET Die CC-AUTO CC-CALC EB CC-FLOW EB-FLOW Total Void 1 1 12 4 3 6 1 42 2 1 23 0 2 12 2 37 I want to look for a match the Headers on Sheet2 ("POCKET","Die","CC-AUTO","CC-CALC","EB","CC-FLOW","EB-FLOW","Total Void") in Sheet1, Column L3+ and get the value of Column M3+. This is what I have working so far.. =OFFSET('Sheet1'!$L3,MATCH(C7,'Sheet2'!$L:$L,0)*0, 1) How do I make this formula skip every 8 rows? Thanks, CJ |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
need a formula to transpose sets of data from Sheet1 to Sheet2
I appreciate your help.
I don't understand how it works, but it works. If is not too much trouble, I'm interested in learning who it works. Could you break down the elements of the formula and what they do? "JLatham" wrote: You're welcome. I almost messed it up completely - didn't realize until a little late in the game that it was grouped in repeating sets of 8 and that you'd probably want to continue not just across the sheet, but down and put each set of 8 on different row, and THEN working with 2 columns tossed yet another twist in it. You're lucky I was well into my 3rd cup of morning coffee when I went to work on that one! "cj2k2k" wrote: JLatham, You are a genius! I got it to work. I had to tweak the offset a little. =OFFSET(Sheet1!$L$3,(COLUMN()-3)+(((ROW()-8)/1)*8),1) Thanks a million! "JLatham" wrote: In C7 =OFFSET(Sheet1!$L$3,(COLUMN()-3)+(((ROW()-7)/2)*8),0) in C8 =OFFSET(Sheet1!$M$3,(COLUMN()-3)+(((ROW()-8)/2)*8),0) then just fill them both to the right over to column J. When you fill down the sheet, make sure you select both C7 and C8 before starting the fill down the sheet, that'll get Sheet1 row L11 into C9 and M11 into C10 "cj2k2k" wrote: What formula do I need to traspose sets of data from Sheet1 to Sheet2? the data comes in sets of 8, sheet1 looks like this (from L3) L M 3 POCKET 1 4 DIE 1 5 CC-AUTO 12 6 CC-CALC 4 7 EB 3 8 CC-FLOW 6 9 EB-FLOW 1 10 Total Void 42 11 POCKET 2 12 DIE 1 13 CC-AUTO 23 14 CC-CALC 0 15 EB 2 16 CC-FLOW 12 17 EB-FLOW 2 18 Total Void 37 Sheet2 needs to look like this (from C7) C D E F G H I J 7 POCKET Die CC-AUTO CC-CALC EB CC-FLOW EB-FLOW Total Void 1 1 12 4 3 6 1 42 2 1 23 0 2 12 2 37 I want to look for a match the Headers on Sheet2 ("POCKET","Die","CC-AUTO","CC-CALC","EB","CC-FLOW","EB-FLOW","Total Void") in Sheet1, Column L3+ and get the value of Column M3+. This is what I have working so far.. =OFFSET('Sheet1'!$L3,MATCH(C7,'Sheet2'!$L:$L,0)*0, 1) How do I make this formula skip every 8 rows? Thanks, CJ |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
need a formula to transpose sets of data from Sheet1 to Sheet2
I'll try. It's a bit difficult to put into words, at least in short words.
We'll use the first formula I put up as an example: =OFFSET(Sheet1!$L$3,(COLUMN()-3)+(((ROW()-7)/2)*8),0) OFFSET takes 3 parameters (actually there are 2 more, but we don't need them) #1 - the reference address, so we tell it we want to start at Sheet1!$L$3 (and the $ symbols tells Excel not to change that reference point as we fill the formula to other columns or rows). #2 - a number of rows to offset from the reference an offset of 1 would point to the row below the reference, a value of 0 = same row, and negative values refer to rows above the reference point, and #3 - number of columns to offset: positive = columns to the right, zero = same column, and negative numbers = columns to the left of the reference. We always use zero in our formula because we want to remain in the same column. The real trick is figuring out a row offset that accounts for 2 things: #1 - the fact that this particular formula is only going to appear on every other row #2 - the other fact that your groups are 8 elements long. Two other things you need to know: ROW() and COLUMN() when used like they are without anything in the () returns the row/column number that they are in, where ever that happens to be. So look at the Row parameter of the formula: (COLUMN()-3)+(((ROW()-7)/2)*8) Since we're transposing things, we use the current column number our formula is in and subtract some value to get a zero in that first formula. Since you're putting this into cell C7 and column C is column #3, then 3-3 = 0. When you fill this formula over into column D that evaluates to 4-3 = 1. That pretty much takes care of the immediate relative position in a group, but we have to take into account where the group starts at in your original list, since a new group starts every 8 rows. That's where the (((ROW()-7/2)*8) comes in Remember this formula is in C7, and so ROW() = 7 in the first formula, and 7-7 = 0 and 0/2 is still 0 and 0*8 is again, still zero. So for first formula, it says NO row offset. Take both halves and add them together: in C7 it is 0+0=0, but in D7 it is 1+0 = 1 (which is same as OFFSET(L3,1,0) or same as referencing L4). This continues across to column J where COLUMN()-3 becomes 10-3=7 but second half is still 0, so it evaluates as OFFSET(L3,7,0) which is same as referencing L10. When the TWO formulas are filled down the sheet, then the $L$3 formula gets put into row 9. Let's look at how it computes at C9: COLUMN()-3 is still 3-3=0, but (((ROW()-7)/2)*8) becomes (((9-7)/2)*8 or ((2)/2)*8 or ((1))*8 = 8 which is same as writing OFFSET(L3,8,0) and that gives us an address of L11 - the start of your next group! So you see that the 3 in COLUMN()-3 came from the fact that your formula was palced into column #3 (column C). The 7 came from your choice of C7 as the starting point, the divide by 2 was caused by there being two formulas in the sequence, so the value of ROW()-7 is going to increase as 2, 4, 6, 8, etc, but we have to cut that in half to get an offset back into the original table without skipping rows in it. Finally, the 8 came from the size of the group. I hope that's a lucid explanation. "cj2k2k" wrote: I appreciate your help. I don't understand how it works, but it works. If is not too much trouble, I'm interested in learning who it works. Could you break down the elements of the formula and what they do? "JLatham" wrote: You're welcome. I almost messed it up completely - didn't realize until a little late in the game that it was grouped in repeating sets of 8 and that you'd probably want to continue not just across the sheet, but down and put each set of 8 on different row, and THEN working with 2 columns tossed yet another twist in it. You're lucky I was well into my 3rd cup of morning coffee when I went to work on that one! "cj2k2k" wrote: JLatham, You are a genius! I got it to work. I had to tweak the offset a little. =OFFSET(Sheet1!$L$3,(COLUMN()-3)+(((ROW()-8)/1)*8),1) Thanks a million! "JLatham" wrote: In C7 =OFFSET(Sheet1!$L$3,(COLUMN()-3)+(((ROW()-7)/2)*8),0) in C8 =OFFSET(Sheet1!$M$3,(COLUMN()-3)+(((ROW()-8)/2)*8),0) then just fill them both to the right over to column J. When you fill down the sheet, make sure you select both C7 and C8 before starting the fill down the sheet, that'll get Sheet1 row L11 into C9 and M11 into C10 "cj2k2k" wrote: What formula do I need to traspose sets of data from Sheet1 to Sheet2? the data comes in sets of 8, sheet1 looks like this (from L3) L M 3 POCKET 1 4 DIE 1 5 CC-AUTO 12 6 CC-CALC 4 7 EB 3 8 CC-FLOW 6 9 EB-FLOW 1 10 Total Void 42 11 POCKET 2 12 DIE 1 13 CC-AUTO 23 14 CC-CALC 0 15 EB 2 16 CC-FLOW 12 17 EB-FLOW 2 18 Total Void 37 Sheet2 needs to look like this (from C7) C D E F G H I J 7 POCKET Die CC-AUTO CC-CALC EB CC-FLOW EB-FLOW Total Void 1 1 12 4 3 6 1 42 2 1 23 0 2 12 2 37 I want to look for a match the Headers on Sheet2 ("POCKET","Die","CC-AUTO","CC-CALC","EB","CC-FLOW","EB-FLOW","Total Void") in Sheet1, Column L3+ and get the value of Column M3+. This is what I have working so far.. =OFFSET('Sheet1'!$L3,MATCH(C7,'Sheet2'!$L:$L,0)*0, 1) How do I make this formula skip every 8 rows? Thanks, CJ |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
need a formula to transpose sets of data from Sheet1 to Sheet2
By the way, if you'd just wanted the headers at the top of the columns and
nothing but the values (out of column M) below it you could have used this at C7 (and fill across to J7) =OFFSET(Sheet1!$L$3,(COLUMN()-3),0) then in C8 (and initially filled right to J8) you could have put this: =OFFSET(Sheet1!$M$3,(COLUMN()-3)+(((ROW()-8))*8),0) Then you could fill the entire group on row 8 on down the sheet. I gather from your change in /2 to /1 that this is probably what you ended up wanting. "cj2k2k" wrote: I appreciate your help. I don't understand how it works, but it works. If is not too much trouble, I'm interested in learning who it works. Could you break down the elements of the formula and what they do? "JLatham" wrote: You're welcome. I almost messed it up completely - didn't realize until a little late in the game that it was grouped in repeating sets of 8 and that you'd probably want to continue not just across the sheet, but down and put each set of 8 on different row, and THEN working with 2 columns tossed yet another twist in it. You're lucky I was well into my 3rd cup of morning coffee when I went to work on that one! "cj2k2k" wrote: JLatham, You are a genius! I got it to work. I had to tweak the offset a little. =OFFSET(Sheet1!$L$3,(COLUMN()-3)+(((ROW()-8)/1)*8),1) Thanks a million! "JLatham" wrote: In C7 =OFFSET(Sheet1!$L$3,(COLUMN()-3)+(((ROW()-7)/2)*8),0) in C8 =OFFSET(Sheet1!$M$3,(COLUMN()-3)+(((ROW()-8)/2)*8),0) then just fill them both to the right over to column J. When you fill down the sheet, make sure you select both C7 and C8 before starting the fill down the sheet, that'll get Sheet1 row L11 into C9 and M11 into C10 "cj2k2k" wrote: What formula do I need to traspose sets of data from Sheet1 to Sheet2? the data comes in sets of 8, sheet1 looks like this (from L3) L M 3 POCKET 1 4 DIE 1 5 CC-AUTO 12 6 CC-CALC 4 7 EB 3 8 CC-FLOW 6 9 EB-FLOW 1 10 Total Void 42 11 POCKET 2 12 DIE 1 13 CC-AUTO 23 14 CC-CALC 0 15 EB 2 16 CC-FLOW 12 17 EB-FLOW 2 18 Total Void 37 Sheet2 needs to look like this (from C7) C D E F G H I J 7 POCKET Die CC-AUTO CC-CALC EB CC-FLOW EB-FLOW Total Void 1 1 12 4 3 6 1 42 2 1 23 0 2 12 2 37 I want to look for a match the Headers on Sheet2 ("POCKET","Die","CC-AUTO","CC-CALC","EB","CC-FLOW","EB-FLOW","Total Void") in Sheet1, Column L3+ and get the value of Column M3+. This is what I have working so far.. =OFFSET('Sheet1'!$L3,MATCH(C7,'Sheet2'!$L:$L,0)*0, 1) How do I make this formula skip every 8 rows? Thanks, CJ |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
need a formula to transpose sets of data from Sheet1 to Sheet2
JLatham,
That was a great explanation indeed! I really appreciate the time you took to put it together. Now I understand how it works. You demonstrate great coacing and writing skills. Have you considered being an author? If not, you should! Thanks again, CJ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function on Sheet1 based on data on Sheet2 | Excel Worksheet Functions | |||
copying data from sheet1 to sheet2 | Excel Worksheet Functions | |||
How to import data from Sheet1 to Sheet2 in the order I want | Excel Discussion (Misc queries) | |||
How To Retrieve Data from Sheet2 into Sheet1 | Excel Worksheet Functions | |||
Looking for comparable data records between Sheet1 and Sheet2 | Excel Discussion (Misc queries) |