1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: In Xcel, I Need to combine multiple columns containing multiple rows into one column

Discussion in 'Business Applications' started by Alabama Ron, Jun 15, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. Alabama Ron

    Alabama Ron Thread Starter

    Joined:
    Jun 15, 2006
    Messages:
    2
    I sure hope someone can provide, or direct me to the formula to accomplish the following in Xcel 2000.
    I have a singe sheet consisting of 5,123 rows of data in 13 columns. A person has been maintaining this and we are about to convert it to cvs and install a text file in a CGI\Perl program to run on a web site. For some unknown reason the person who started and has been maintaining the file has set it up so that the first 3 columns contain data that should be all in one column. Here is an example: a ham radio call, we will say is: WW6XXX. In the first column (A), row 7, he has entered the prefix: "WW", in the second column, (B), row 7 he has entered the call district "6" and in the third column (C), row 7, he has entered the suffix "WWW".

    I have inserted a blank column "D". I can combine, for example A7,B7 and C7 with the simple formula =A1&B1&C1 placed in D7. No problem.

    Now the problem, and I'm sure you all ready can see it. I need to do this with all 5,123 rows. It certainly SEEMS, logically, that I should be able to accomplish this but durned if I've been able to do it. I have Googled, I have Jeeved it, you name it, I've tried it. I have found some "close calls" that almost answer it, but not the real answer.

    Obviously I can do this 5,123 times, manually, but I'd rather find a simpler answer, if there is one.
    Blessings to anyone who can provide me with the answer. This by the way, is for a non-business, non-profit, amateur radio related web site. I have no problem converting the Xcel file to the necessary text. (Fortunately he didn't use any commas in it!)!
    Anyone have an idea?
    Thanks much!
    Alabama Ron
     
  2. kjh4eyes

    kjh4eyes

    Joined:
    Jun 15, 2006
    Messages:
    22
    Don't know if this will work or not. You are much more advanced than I am at this but can you copy the formula from the one cell that you changed and paste that formula into the other cells all at one time? For example, after you put A1&B1&C1 formula into D1 and you need to do the same through the other 5122 columns you should be able to copy the D1 cell and then highlight the remaining cells in row D until you get to # 5123. Don't know if it will work for you but it makes sense that it should.
     
  3. kiwiguy

    kiwiguy

    Joined:
    Aug 17, 2003
    Messages:
    17,584
    Copying as above will work, so will grabbing the "handle" on the highlighted cell with the formula (the black square bottom right of the highlighted cell) and dragging it down to the bottom row needed.

    The formula will auto-adjust as it's copied, A1 will become A2 etc.
     
  4. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    The best and safest way to copy the formula to so many cells is as follows.

    Step 1
    Select the cell that contains the formula

    Step 2
    Hold down the Ctrl key on your keyboard and press the letter C on your keyboard once and then release the Ctrl key (THis will copy the formula into the clipboard).

    Step 3
    Press the F5 function key on your keyboard (Go To)
    Where it says reference in the lower part of the dialogue box - type the last cell reference e.g. D5123

    Step 4
    Hold down the shift key on your keyboard and click on the OK button in the dialogue box - then release the shift key (you have now selected all the cells that you want to copy the formula to)

    Step 5
    Hold down the Ctrl Key on your keyboard and press the letter V on your keyboard and then release the Ctrl key. (This will paste the formula into all the selected cells)

    The formula will now have been copid to all the cells that you wanted.

    Step 6
    Press the Esc key on your keyboard to stop the dotted lines going round the cells. Hey presto job done.
     
  5. Yorkshire Guy

    Yorkshire Guy

    Joined:
    Dec 9, 2003
    Messages:
    563
    Hi Ron,

    This is quicker,

    Select the cell with you new formula in it e.g. D1,
    then hover your cursor overthe bottom right of that cell and the cursor will become a '+', now double-left-click.

    This will copy your formula down through D5123 i.e all the cells in column D that have something in their respective rows in column C; it will stop when an empty column C row is found.

    lol
    Hew
     
  6. Alabama Ron

    Alabama Ron Thread Starter

    Joined:
    Jun 15, 2006
    Messages:
    2
    Good Gosh! I feel foolish! Y'all (or should I say "All y'all"?) make it so simple. Guess I was trying to make it more difficult than it is. I surely thank each of you! What a great group. Hope I can be of some help in other areas. Although I use Xcel to some extent, I have never had the need to do what needed to be done in this case. It is done. Thanks again to all of you!!!!

    Hard to believe I got that many responses in so short a time!
    Take care, all.:) (y)
     
  7. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    we will see you again next week, with your next learning curve question :)
     
  8. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Don't forget to mark your thread as Solved by going to Thread Tools | Mark Solved | Perform Action. :)
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/475772

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice