VBA To Concatenate Data

[jo15765]

If this can be done using an excel formula, I'm good with that as well, but I think...VBA is the easiest way. I have a spreadsheet with about 2,000 rows in it that houses data in columns A & B that looks like this

Please Login or Register  to view this content.

And the format I need the data in is. just like this

Please Login or Register  to view this content.

How would I do this?


EDIT --- not sure why my formatting gets all jacked up when I post this. Essentially how I have the data has name in column A and multiple stores in Column B. So one employee could have 2 rows with 2 different stores listed. I need the data for each employee to have ONE row.

[karedog]

Maybe :

Please Login or Register  to view this content.

[jo15765]

Yes that is exactly what I needed! Thank you!

[YasserKhalil]

Thanks a lot Mr. Karedog for awesome code
As for this part

Please Login or Register  to view this content.

Instead of looping to remove the rows inside the array. Isn't it easy to use Redim Preserve to slice those rows after p variable .. and if possible can you show us this part?

[karedog]

@jo15765, You are welcome, thanks for marking the thread as solved.

@Yasser,
Thanks for rep.points.
Redim Preserve can only redim the last dimension of an array, in case of 2D array, you can only Redim Preserve to add or delete more columns, but not rows.
And beside, if only we can do this (using transpose, redim preserved, then re-transpose, which won't worked for large array), the result array would be smaller than original range, which means you still have to write more code to clean the "redundant area" at bottom. Hope it's clear.

[YasserKhalil]

Thanks a lot my friend. Yes now it is clear.
Thank you very much