Text in row odd, text in row even

  • Thread starter Thread starter an
  • Start date Start date
A

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
 
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
 
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)
 
Thanks for your replay, but I obtained the next error:
"You've entered too few arguments for this function"
an
 
Hi
I missed a bracket (see Bob's correction):
=OFFSET('sheet1'!$A$1,(ROW()-1)*2+MOD(COLUMN()-1,2),0)
 
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
 
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)




.
Click to expand...
 
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




.
Click to expand...
 
=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
 
Hi Andy
nice idea but this will work only if all entries in the source column
are unique entries
 
=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
 
Back
Top