How to exchange data?

[Eve]

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

 

[Claus Busch]

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(RIGHT(A1,1)+63))


Regards
Claus Busch

 

[Ben McClave]

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

 

[Eve]

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

 

[Ben McClave]

Eve,

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

Ben