Grouping & Moving Data

[JRM]

I have a spreadsheet with data in the following format on sheet1:
Col A col B
1 pos
2 pos
3 pos
4 neg
5 pos
6 neg
7 pos
8 neg
9 pos
etc etc - possibly upto 1000+ rows
Each group of 3 are results of different tests on the same sample.
(In reality there are currently12 different results for each sample)

I need to convert this on to sheet 2 to read as follows:
A B C D etc
1 pos pos pos
2 neg pos neg
3 pos neg pos
etc etc

Any ideas how to do this ?
Thanks
Joh

 

[Anders S]

John,

With the data beginning in A1, enter the following formula in B1, C1 and D1
=OFFSET($A$1,(ROW()-1)*3+COLUMN()-2,0)
Fill down as required. To get rid of the formulas, copy columns B, C and D, then Edit>Paste Special>Values.

HTH
Anders Silven

 

[JRM]

Anders
It didn't work at first until I removed the '3D' at the beginning o
the formula - what is it supposed to do?
Having found out the function exists I looked it up on help, playe
around a bit and now think I understand how it works.
Many thanks
Joh

 

[Anders S]

John,

There is no '3D' anywhere in my post so I don't know where it came from. Here's the formula again:
=OFFSET($A$1,(ROW()-1)*3+COLUMN()-2,0)

Good to see that you got it working anyway.

Best regards
Anders Silven

 

[DNF Karran]

I think the "3D" in the following is the problem:

=*3D*OFFSET($A$1,(ROW()-1)*3+COLUMN()-2,0

 

[Anders S]

This is funny...

When I look at my message with the formula in www.excelforum.com the formula indeed reads "=3DOFFSET...".
However, when I read the message in Outlook Express, connected directly to news.microsoft.com (as I always do), the formula correctly reads "=OFFSET...". I also copied the formula in OE and pasted it back into Excel without problem.

So, the message and the formula is OK on the news server, the "3D" is inserted somehow in excelforum or on the way there.

Also, my message earlier today is listed 6 (!) times in excelforum, I only posted it once, which can be verified at news.microsoft.com.

Regards,
Anders Silven

 

[JRM]

Anders
Don't know where all the "D3"s are coming from but the formula withou
them works great and I now fully understand what its doing.
Mmany thanks
Joh