Excel : how to turn a 1-column sheet into a 2-column sheet
Thread poster: Sylvain Leray

Sylvain Leray  Identity Verified
Local time: 22:06
Member (2003)
German to French
Jun 4, 2007

Excel 2002

Hi,

I have a huge Excel sheet ( ca. 53000 rows) which has only one column, in which I have A1 : source, A2 : target, A3 : source, A4 : target, etc... each term under each other.
Is there a simple way to obtain a 2-column sheet with Source in A and Target in B without copying/pasting each target cell into the B-column?

Many thanks in advance!
Sylvain


Direct link Reply with quote
 

Tony M  Identity Verified
France
Local time: 22:06
Member
French to English
+ ...
Bodge solution? Jun 4, 2007

I don't know if there is an official way of doing this, but a bodge workaround might be to copy colum A and paste it into column B one cell higher; all you would then need to do woul be to simply delete every other line — a chore, but perhaps you could do it as you go? It'd be marginally quicker (and a lot less fiddly!) than 53,000 cut-and-pastes!

Direct link Reply with quote
 

Sylvain Leray  Identity Verified
Local time: 22:06
Member (2003)
German to French
TOPIC STARTER
Not bad Jun 4, 2007

Thank you Tony !
Not a bad idea! I did copy and paste the column A into B, one row higher, so now I "only" have to delete all the uneven rows... well, by chance I am not in a hurry

Thanks!


Direct link Reply with quote
 

Claudia Krysztofiak  Identity Verified
Germany
Local time: 22:06
English to German
+ ...
Use a Word-Workaround Jun 4, 2007

You could save the one-column file as a tab-delimited csv or txt file and then open it in word.
There it should neatly display
A1
A2
A3
A4
without any tabs before or after, but with paragraph marks after each line.

Then you select the whole text and change it into a table, telling Word that it is a 2-column table. A List that long should take quite some time, but it should still be faster than doing it by hand.

Afterwards save it again as tab-delimited csv and open it in Excel.
You could also try a copy and paste from Word to Excel but I am not sure if your system can handle it.

Good luck!


Direct link Reply with quote
 

Endre Both  Identity Verified
Germany
Local time: 22:06
Member (2002)
English to German
Try automatic cell population Jun 4, 2007

Sylvain,

before you delete 26500 rows manually, you can try automatic cell population:
1. Insert a column to the left of your existing columns
2. Insert "x" in the first cell of this column.
3. Select the first two cells of this column.
4. "Grab" the bottom right corner of the selection (by the small black rectangle you find there) and drag it all the way down to the end of the 53000 rows.
5. The result should be "x" in every second row.
6. Select all columns and click on "Alphabetic sorting".
7. The rows that contain an "X" in the first column (presumably the ones which you need) should be on top, and the empty rows should be at the bottom -- you can now delete them.

If you need to preserve the original sequence of the cells, you need to take another step by populating (prior to Step 1 above) another row with a sequence of numbers:
a) Enter 1 and 2 in the first two cells.
b) Select the two cells, grab the bottom right corner and drag it all the way down.

After finishing Step 7 above, you can now sort all rows according to the remaining numbers (which should be all odd ones). Alternatively, you can adapt Step 6 by selecting Data/Sort rather than simply clicking on the A-Z icon. This allows you to sort by more than one column at the same time.

Good luck,
Endre


Direct link Reply with quote
 

gianfranco  Identity Verified
Brazil
Local time: 18:06
Member (2001)
English to Italian
+ ...
Suggestion Jun 4, 2007

You could try this procedure:

  • Put the letters A, B, A, B, A, B, etc.. in a column next to the Source and Target items. After the first few blocks of 2 letters, you can copy very large groups and complete this task very quickly.
    All the items that you want in the first column should have a letter A, the items for the second column a letter B next to them.

  • Sort the whole table by the column containing the letters A,B, and you get all items with A grouped together, on top, followed by the whole group having the letter B.

  • Copy the whole group B (target items) next to the gropu having the letter A (source items).

  • Remove the temporary column containing the letters

    That's all.
    Please note that it will work only if you always have both items, without gaps or missing items.

    bye
    Gianfranco


    [Edited at 2007-06-04 11:21]

    Direct link Reply with quote
     

  • Hynek Palatin  Identity Verified
    Czech Republic
    Local time: 22:06
    English to Czech
    + ...
    Another solution Jun 4, 2007

    Enter the following formula into B1:
    =OFFSET($A$1;2*(ROW(A1)-1);0)

    Enter the following formula into C1:
    =OFFSET($A$1;2*(ROW(A1)-1)+1;0)

    (You might need to use your localized versions of the OFFSET and ROW function names.)

    Copy both formulas down to the half of the column A. Now you have the source in column B and target in column C and you can copy them to a new sheet, for example (using Paste Special - Values).


    Direct link Reply with quote
     

    tectranslate ITS GmbH
    Local time: 22:06
    German
    + ...
    Endre's and Gianfranco's suggestions are good Jun 4, 2007

    I did something very similar recently using a much more complicated approach with a formula that yielded varying numbers from 0-4 depending on the line number (modulo calculation). I used that number as a "language ID", i.e. 0=German, 1=English, 2=Italian, etc. and was able to sort the list by languages.

    The suggestions here are simpler, but will work just as well.

    I second Endre's suggestion to insert an index column (containing a running number) prior to sorting so as to be able to return to the original sorting at a later time.

    I also second Gianfranco's note that the file needs to be uniform, i.e. with no gaps and no structural inconsistencies.

    B

    P.S.: I hadn't seen Hynek's solution yet when I wrote this - maybe that'll work as well.

    [Edited at 2007-06-04 11:48]


    Direct link Reply with quote
     

    Sylvain Leray  Identity Verified
    Local time: 22:06
    Member (2003)
    German to French
    TOPIC STARTER
    Many thanks Jun 5, 2007

    to all of you.

    Gianfranco's solution was the simpliest for me.
    You were all of great help!

    Sylvain


    Direct link Reply with quote
     


    To report site rules violations or get help, contact a site moderator:


    You can also contact site staff by submitting a support request »

    Excel : how to turn a 1-column sheet into a 2-column sheet

    Advanced search






    Protemos translation business management system
    Create your account in minutes, and start working! 3-month trial for agencies, and free for freelancers!

    The system lets you keep client/vendor database, with contacts and rates, manage projects and assign jobs to vendors, issue invoices, track payments, store and manage project files, generate business reports on turnover profit per client/manager etc.

    More info »
    WordFinder
    The words you want Anywhere, Anytime

    WordFinder is the market's fastest and easiest way of finding the right word, term, translation or synonym in one or more dictionaries. In our assortment you can choose among more than 120 dictionaries in 15 languages from leading publishers.

    More info »



    Forums
    • All of ProZ.com
    • Term search
    • Jobs
    • Forums
    • Multiple search