Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Importing row data into multiple columns

  1. #1

    Importing row data into multiple columns



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I wonder if anyone can help me? I have multple rows of data that have to be inputted into another similar worksheet. It would take me years to simply copy and paste. I would like a quicker way if anyone can help??

    One sheet contains :
    101478951 FINE GOOD 1000
    101478953 SOFT GOOD 1200

    ETC. ETC. for thousands of entries.

    Then on a separate sheet I have

    101478951 SMITH L
    101478951 JONES L
    101478951 GARY W
    101478953 JOHN L
    101478953 WAYNE L

    ETC. ETC.

    Now what i need to do is somehow import the data from the first sheet into the second so it looks something like:

    101478951 FINE GOOD 1000 SMITH L
    101478951 FINE GOOD 1000 JONES L
    101478951 FINE GOOD 1000 GARY W
    101478953 SOFT GOOD 1200 JOHN L
    101478953 SOFT GOOD 1200 WAYNE L

    Any help would be greatly appreciated!

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,156
    Articles
    0
    How many cells are occupied by the likes of:
    101478951 FINE GOOD 1000
    ?
    It would be better to supply a small file rather than paste text as you have, since it will answer many such trivial questions at a stroke.

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,156
    Articles
    0
    Duplicate of post in the Formulas section:
    https://www.excelguru.ca/forums/show...ltiple-columns
    I realise you may want a macro and/or a Formulas solution but you should make people aware of such posts.
    (By the way, if you've cross-posted this question at other sites too, you're required to provide links to those threads too.)

  4. #4
    OK sorry, i am a newbie and didn't realise. Yes I'm not sure whether it was a formula or VBA programming required.

    The file is around 18,000 rows and so I have cut down to upload.

    Thanks
    Attached Files Attached Files

  5. #5
    Another way to think about it which would be even better is the number I have entered in the last column is the number of times the values need to be repeated. For example, the first line "101478975" requires this line 11 times (ie copy & pasted 10 times below it), the second entry "101478977" then requires 9 entries direrctly below the first line etc. etc.
    101478975 FINE SOFT5 1800 11
    101478977 FINE SOFT5 1500 9


    Hope somebody can help?

    Many thanks

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,156
    Articles
    0
    Quote Originally Posted by Beddy Boy View Post
    Then on a separate sheet I have

    101478951 SMITH L
    101478951 JONES L
    101478951 GARY W
    101478953 JOHN L
    101478953 WAYNE L

    ETC. ETC.
    There's only one sheet in the file you attached!
    Don't worry about the number of times things need to be repeated, we can work that out easily enough - stick with just the raw data.

  7. #7
    Thanks for the reply.

    I figured that if I can get this data correct in this sheet I can then simply copy & paste into the next....

  8. #8
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,156
    Articles
    0
    just supply a file with both sheets…

  9. #9
    Thanks for the help.

    I have uploaded a file with the 2 sheets. This is however not the full file as the original is too large...

    Please let me know what you think.

    Many thanks
    Attached Files Attached Files

  10. #10
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,156
    Articles
    0
    In Sheet1 of your attached file there are extraneous data in cells L82304:M116898; I deleted all rows below row 34595 before doing the following.
    I put this formula in cell L1:
    =VLOOKUP($D1,Sheet2!$A$1:$E$17629,2,FALSE)

    This formula in cell M1:
    =VLOOKUP($D1,Sheet2!$A$1:$E$17629,3,FALSE)

    This in cell N1:
    =VLOOKUP($D1,Sheet2!$A$1:$E$17629,4,FALSE)

    This in cell O1:
    =VLOOKUP($D1,Sheet2!$A$1:$E$17629,5,FALSE)

    (The blue numbers are the columns you're getting the information from.)

    I then selected cells L1:O1 and double-clicked the small black square at the bottom right of the selected cells to copy the formulae down. I then waited half a minute or so for the sheet to recalculate. Then I copy, paste-special, values, the whole new values area to convert all those new formulae to plain values (to save time recalculating).

    Of course you don't have to bring in all the columns, and you can place the formulae in whatever columns you want as long as they refer to the right ranges.

    That's a formula solution for you.

    The above process can be automated by running the following macro when Sheet1 is the active sheet (it's tailor-made for your attached file and is extremely unlikely to work satisfactorily on any other file):
    Code:
    Sub blah()
        Range("L1:L34595").FormulaR1C1 = "=VLOOKUP(RC4,Sheet2!R1C1:R17629C5,2,FALSE)"
        Range("M1:M34595").FormulaR1C1 = "=VLOOKUP(RC4,Sheet2!R1C1:R17629C5,3,FALSE)"
        Range("N1:N34595").FormulaR1C1 = "=VLOOKUP(RC4,Sheet2!R1C1:R17629C5,4,FALSE)"
        Range("O1:O34595").FormulaR1C1 = "=VLOOKUP(RC4,Sheet2!R1C1:R17629C5,5,FALSE)"
        'Range("L1:O34595").Value = Range("L1:O34595").Value 'take the apostrophe off the beginning of this line tyo enable it; it converts the formulae to plain values. I left it in so you can see the formulae.
    End Sub
    It took about 30 seconds to run with 99.99% of that time being the sheet calculating itself.

Page 1 of 2 1 2 LastLast

Posting Permissions

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