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: Excel 97 - want to automatically move data

Discussion in 'Business Applications' started by slurpee55, Mar 14, 2005.

Thread Status:
Not open for further replies.
Advertisement
  1. slurpee55

    slurpee55 Thread Starter

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Hi all,

    I have a monthly report that has a certain column (always column BF) that contains the numbers 1-5, either singly or in any combination thereof. I have to copy this into 5 columns (BQ through BU) and I want those columns titled Q2 - 1, Q2 - 2, Q2 - 3, Q2 - 4 and Q2 - 5.
    I need all the 1s in Q2 - 1, all the 2s in Q2 - 2 and so on.
    Since the number of items in each cell varies, (there is no 1, , 3,4, , with blanks for non-existing numbers, rather one cell may have 2 and the one below it may have 1,3,5) is there a macro or a formula that would quickly copy every item into the right column?
    It isn't vital, I suppose, I can copy, sort and cut and paste, but I would like to save the time....

    Thanks in advance
     
  2. slurpee55

    slurpee55 Thread Starter

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Oh, and btw, it is possible (although rare) that someone in the file may not have any answer to this question, so it should probably be sorted first to prevent any macro from stopping when it hits an empty cell in column BF.
     
  3. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Please create, zip, and upload a sample file. I cannot figure out what you want. :)
     
  4. slurpee55

    slurpee55 Thread Starter

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Hi Dreamboat,

    Here is a file with some data in the first column and then 5 more columns showing what I want it to do.
    Thanks
     

    Attached Files:

  5. slurpee55

    slurpee55 Thread Starter

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Someone at VBSForum gave me this answer and it works nicely.
    With the data in column BF2 and down and the copied and split data to be in columns BQ through BU, this formula works:
    =IF(ISERR(FIND(RIGHT(BQ$1,1),$BF2)),"",RIGHT(BQ$1,1))
    Thanks for sending me there, Dreamboat! ;)
     
  6. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    VBSForum?
    LOL!!

    www.vbaexpress.com

    VBS is visual basic script, a different animal.
    Well, okay, maybe the same animal, but with different stripes.
     
  7. slurpee55

    slurpee55 Thread Starter

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    ah, dumbest thing is, I have typed that before...(blushing)
     
  8. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
  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/340952

  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