Hi,

I have an Excel 2007 file with text data in 2 columns like this:
X1 A
X2 B
X3 C
X4 D
X5 E
X6 F
etc.

For some purposes I have to save the date in another file
in the following way:
X1 B
X2 A
X3 D
X4 C
X5 F
X6 E
etc.

The file is big so it would be difficult to do it manually.
How to do it automatically?

Please help me.

Regards
Eve

Hi Eve,

Am Tue, 9 Oct 2012 18:58:28 +0200 schrieb Eve:

I have an Excel 2007 file with text data in 2 columns like this:
X1 A
X2 B
X3 C
X4 D
X5 E
X6 F
etc.

For some purposes I have to save the date in another file
in the following way:
X1 B
X2 A
X3 D
X4 C
X5 F
X6 E
etc.

we need some more information.
Is the character always "X"? Does the following number always have 1
digit? Is there only 1 character in column 2?
For your example try following formula for the character in column 2:
=IF(ISODD(RIGHT(A1,1)),CHAR(RIGHT(A1,1)+65),CHAR(R IGHT(A1,1)+63))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Eve,

Assuming your data is located in columns A and B, you could use a formula to perform the task. Then just copy and paste as values to the new book. The formula (for cell C2) is:

=IF(MOD(ROW(),2)=0,B1,B3)

If your data begins in cell A1, the following is how your sheet would be laid out:

X1 A =B2
X2 B =IF(MOD(ROW(),2)=0,B1,B3)
X3 C =IF(MOD(ROW(),2)=0,B2,B4)
X4 D =IF(MOD(ROW(),2)=0,B3,B5)
X5 E =IF(MOD(ROW(),2)=0,B4,B6)
X6 F =IF(MOD(ROW(),2)=0,B5,B7)
etc.

Hope this helps,

Ben

Hi Ben,

Assuming your data is located in columns A and B, you could use a formula
to perform the task. Then just copy and paste as values to the new book.
The formula (for cell C2) is:

=IF(MOD(ROW(),2)=0,B1,B3)

If your data begins in cell A1, the following is how your sheet would be
laid out:

X1 A =B2
X2 B =IF(MOD(ROW(),2)=0,B1,B3)
X3 C =IF(MOD(ROW(),2)=0,B2,B4)
X4 D =IF(MOD(ROW(),2)=0,B3,B5)
X5 E =IF(MOD(ROW(),2)=0,B4,B6)
X6 F =IF(MOD(ROW(),2)=0,B5,B7)
etc.

That works!. However I had to change it into:
=IF(MOD(ROW();2)=0;B1;B3)
It is probably connected with version of Excel.

Thank you very much Ben.
Thanks to you Claus too for your answer.

Regards,
Eve

Eve,

I'm glad to hear that it works. Thanks for the feedback.

Ben