+ Reply to Thread
Results 1 to 17 of 17

Bring scattered data to correct columns

  1. #1
    Registered User
    Join Date
    04-15-2010
    Location
    New Haven, Connecticut
    MS-Off Ver
    Excel 2003
    Posts
    7

    Bring scattered data to correct columns

    Okay I have a weird problem I have bunch of data in a worksheet with a header. I need the data to fill the next corresponding row in the column without having to drag each row indivdually. Is there anyway to do that?
    Last edited by vadaniels; 04-15-2010 at 01:07 PM.

  2. #2
    Valued Forum Contributor mdbct's Avatar
    Join Date
    11-11-2005
    Location
    CT
    MS-Off Ver
    2003 & 2007
    Posts
    848

    Re: Bring scattered data to correct columns

    Please supply more details. A sample of what you are working with and what you want would be helpful as well.

  3. #3
    Registered User
    Join Date
    04-15-2010
    Location
    New Haven, Connecticut
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Bring scattered data to correct columns

    Thanks for the reply I'm attaching a sample of what the spreadsheet looks like now and a sample of what I need it to look like.
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor mdbct's Avatar
    Join Date
    11-11-2005
    Location
    CT
    MS-Off Ver
    2003 & 2007
    Posts
    848

    Re: Bring scattered data to correct columns

    Your original data is all over the place. Is there any logic as to the placement of the data?

  5. #5
    Registered User
    Join Date
    04-15-2010
    Location
    New Haven, Connecticut
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Bring scattered data to correct columns

    Not originally, no thats why I set up the header. I just wanted to know if there was anyway to put these information in order underneath the header with out have to drag each bit of information to its proper header.

  6. #6
    Valued Forum Contributor mdbct's Avatar
    Join Date
    11-11-2005
    Location
    CT
    MS-Off Ver
    2003 & 2007
    Posts
    848

    Re: Bring scattered data to correct columns

    Without knowing the logic behind the placement of the data, there is no way of determining what goes where.
    Can you post the "After" for your Sample1 book? Currently, it looks like the data in row 2 does not belong with the rest of the data. Put the before and after in the same book.

  7. #7
    Registered User
    Join Date
    04-15-2010
    Location
    New Haven, Connecticut
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Bring scattered data to correct columns

    Here is a New sample thanks again for at least taking a look I appreciate it
    Attached Files Attached Files

  8. #8
    Valued Forum Contributor mdbct's Avatar
    Join Date
    11-11-2005
    Location
    CT
    MS-Off Ver
    2003 & 2007
    Posts
    848

    Re: Bring scattered data to correct columns

    You an use this macro to reorganize the data.

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    04-15-2010
    Location
    New Haven, Connecticut
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Bring scattered data to correct columns

    For some reason it isnt working:
    I copied the marcos
    I hit alt+F11 to bring up the VBE
    I hit insert module and then pasted the marcros into the module
    I then backed of that and went to the excel spreadsheet and hit alt+f8 and select shuffle data and then I hit run but it doesnt do anything. Did I do something wrong?

  10. #10
    Valued Forum Contributor mdbct's Avatar
    Join Date
    11-11-2005
    Location
    CT
    MS-Off Ver
    2003 & 2007
    Posts
    848

    Re: Bring scattered data to correct columns

    the macro is set up to look for the first row of data on row two. Is the first row of data on row 2?

  11. #11
    Registered User
    Join Date
    04-15-2010
    Location
    New Haven, Connecticut
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Bring scattered data to correct columns

    ahh that might be it


    You are a Fracking genius my friend thank you so much.
    Last edited by vadaniels; 04-15-2010 at 03:09 PM. Reason: Update

  12. #12
    Valued Forum Contributor mdbct's Avatar
    Join Date
    11-11-2005
    Location
    CT
    MS-Off Ver
    2003 & 2007
    Posts
    848

    Re: Bring scattered data to correct columns

    change the two i=2 lines to i = first row under the header. Assuming of course that the data is how you presented it in the "New sample" book

  13. #13
    Registered User
    Join Date
    04-15-2010
    Location
    New Haven, Connecticut
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Bring scattered data to correct columns

    thanks again

  14. #14
    Registered User
    Join Date
    03-14-2012
    Location
    France
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Bring scattered data to correct columns

    Quote Originally Posted by vadaniels View Post
    Thanks for the reply I'm attaching a sample of what the spreadsheet looks like now and a sample of what I need it to look like.
    Hello, I know this post is quite old but if you often need to merge columns from distinct files or to merge similar lines, I suggest you have a look at podbox.com. It's an alpha release for the moment but moving fast to meet the users demand... It's free and will remain free. Please let me know if you want me to create your account to test it.

  15. #15
    Registered User
    Join Date
    03-31-2012
    Location
    Abuja, Nigeria
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Bring scattered data to correct columns

    I'm having a similar challenge on formatting cells/columns/rows. i have a worksheet and the last row contains transaction descriptions.(extracted from a PDF bank statement). After converting the PDF to excel the last column had its rows scattered or separated how do i merge or trim them without having to do it one at a time cos its like 45pages
    find attached an extract of the whole document.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    12-22-2020
    Location
    Southern CT
    MS-Off Ver
    2007
    Posts
    4

    Re: Bring scattered data to correct columns

    Greetings! This thread seems to ask the sort of question I have, but I was unable to adjust the sample macro provided above.

    In the attached sample table, I wish to shuffle the values (1-12) to their respective columns (B-M). The full table has 2,400 rows with 4,600 values.

    Thank you for any guidance.
    Attached Files Attached Files

  17. #17
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,333

    Re: Bring scattered data to correct columns

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1