Hi there,

I got a tricky (at least for me) transpose issue to solve. I hope the attached screenshot explains it sufficiently. But here in words as well:

I got a huge table with hundreds of drill core records (rows) and 30 data for each record (columns). To prepare these data for importing them into a special geo software, I need to transpose these data. The weird geo software requires, that 12 of the 30 data are listed in just one column, with just the core name in a parallel column. In other words: After a successful transpose, 12 selected data of core 1 would be followed by the same 12 selected data of core 2, etc.

I know what transpose can do, I also know arrays. But what I can't solve is, that on auto-fill down, the formula jumps 12 rows of course, since the second array cycle starts 12 rows beneath the first. And it should just increment by 1, since the second record is just 1 row beneath the first (my example screenshot shows this with just 3 rows, but I guess you see what I'm talking about).

I experiemented with OFFSET, ROW(), ADDRESS etc. and I'm sure the solution lies there somewhere. I just can't find it, sorry. Anyone can help?



Thanks a lot,
g

excel_transpose issue2.jpg

On a new worksheet insert:
=OFFSET(Sheet1!$A$1,INT((ROW()-1)/3),MOD(ROW()-1,3))
where 'Sheet1' is the name of your data worksheet. Copy down as far as needed.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Wow Paul, works great. I wish I could offer you in return to help you out when you have a question one day. I just have a feeling that's not likely to happen, at least not with respect to Excel. Anyway, thanks a lot, you're saving me a lot of time.

g

I just dabble in Excel - Word is my forte.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]