Text in row odd, text in row even

[an]

Hello!

I have:

A
1 Txt1
2 Txt2
3 Txt3
4 Txt4
5 Txt5
6 Txt6

I need:
A B
1 Txt1 Txt2
2 Txt3 Txt4
3 Txt5 Txt6

Yhanks in advance.
an

 

[Frank Kabel]

Hi
if your source data is in sheet1 enter the following in A1 on a
different sheet
=OFFSET('sheet1'!$A$1,ROW()-1)*2+MOD(COLUMN()-1,2),0)
and copy down/to the right

 

[Bob Phillips]

Hi Frank,

I think you mean

=OFFSET(Sheet1!$A$1,(ROW()-1)*2+MOD(COLUMN()-1,2),0)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Bob
thanks for the correction

 

[an]

Thanks for your replay, but I obtained the next error:
"You've entered too few arguments for this function"
an

 

[Frank Kabel]

Hi
I missed a bracket (see Bob's correction):
=OFFSET('sheet1'!$A$1,(ROW()-1)*2+MOD(COLUMN()-1,2),0)

 

[Nikos Yannacopoulos]

An,

I'll assume your data starts in cell A1.
Insert a blank row on top, so the data now starts in A2.
Use whatever column headings in A1 to C1 (like A, B, C or whatever, just as
long as the cells are not empty).
Copy your data from A3 down (that is everything but the first cell) and
paste in B2. Now each cell in column A has the next piece of data next to it
in column B.
In C2 type in the formula: =MOD(ROW(A2),2) and copy down to the end of the
data in column A.
Select columns A through C and do an autofilter on C (that's why we need the
headers), selecting the 1's (that's every other row!).
Delete the selected rows.
You can now remove the formulae in column C and the column headers, and
you're done.

HTH,
Nikos

 

[an]

Exactly.
Thanks for both!
an

-----Original Message-----
Hi Frank,

I think you mean

=OFFSET(Sheet1!$A$1,(ROW()-1)*2+MOD(COLUMN()-1,2),0)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)




.

 

[an]

Many thanks, Nikos.
an

-----Original Message-----
An,

I'll assume your data starts in cell A1.
Insert a blank row on top, so the data now starts in A2.
Use whatever column headings in A1 to C1 (like A, B, C or whatever, just as
long as the cells are not empty).
Copy your data from A3 down (that is everything but the first cell) and
paste in B2. Now each cell in column A has the next piece of data next to it
in column B.
In C2 type in the formula: =MOD(ROW(A2),2) and copy down to the end of the
data in column A.
Select columns A through C and do an autofilter on C (that's why we need the
headers), selecting the 1's (that's every other row!).
Delete the selected rows.
You can now remove the formulae in column C and the column headers, and
you're done.

HTH,
Nikos




.

 

[Andy Brown]

=OFFSET('sheet1'!$A$1,(ROW()-1)*2+MOD(COLUMN()-1,2),0)

LOL! (Hi Frank).

If you'd prefer not to give yourself a headache with maths, enter "Txt1" in
B1 & "Txt2" in C1.

Enter =OFFSET($A$1,MATCH(B1,$A:$A,0)+1,0) in B2. Drag it across to C2, drag
B2:C2 down.

Rgds,
Andy

 

[Frank Kabel]

Hi Andy
nice idea but this will work only if all entries in the source column
are unique entries

 

[Andy Brown]

=OFFSET('sheet1'!$A$1,(ROW()-1)*2+MOD(COLUMN()-1,2),0)

Seems to work if the "extract table" is in odd/even columns (e.g. E:F) but
mirrors it if not. Hmm ...

Rgds,
Andy