Importing row data into multiple columns

Beddy Boy

New member
Joined
Feb 1, 2017
Messages
48
Reaction score
0
Points
0
Excel Version(s)
Excel 365
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!
 
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.
 
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
 

Attachments

  • example.xlsx
    9.4 KB · Views: 11
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.
101478975FINESOFT5180011
101478977FINESOFT515009


Hope somebody can help?

Many thanks
 
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.
 
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....
 
just supply a file with both sheets…
 
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
 

Attachments

  • UPLOAD (3).zip
    4.7 MB · Views: 19
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.
 
Thank you. Will give it a try and let you know how I go...
 
Thanks very much. I have put this into original file successfully. I appreciate all your work and advice.

Thank you!!
 
Back
Top