need a formula to transpose sets of data from Sheet1 to Sheet2

C

cj2k2k

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
 
J

JLatham

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
 
C

cj2k2k

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!
 
J

JLatham

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!
 
C

cj2k2k

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?
 
J

JLatham

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.
 
J

JLatham

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.
 
C

cj2k2k

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top