I've been really scratching my head but I can't figure this one out...

I have 3-4 worksheets (depending on the job) that I want to combine
into one. The data may exist on some sheets but not others. These can
get very large when combined (17 columns and over 10,000 rows).

What I need is a way that will combine the following values as an
example from this data on 3 rows, where each of these values is in it's
own cell:


12 17 4 john smith
3 4 7 john smith
45 4 2 john smith

to this, with all of the values on one row:

12 17 4 3 4 7 45 4 2 john smith


I may also have situations where I'll have this:

12 17 4 john smith
john smith
45 4 2 john smith

but I need this:

12 17 4 45 4 2 john smith


I have created macros that move the values and delete the now-empty
rows, but with over 10,000 rows, it's extremely time consuming...

Any help?