Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

If I have the following data:

123, thomas
123, gordon
123, smith
334, joey
334, nancy
3452, angela
3452, liza

I want a formula to produce the following results:

123, thomas, gordon, smith
334, joey, nancy
3452, angela, liza

Is there an easy and/or efficient way to do this with a formula? If not, how can I do this with VBA?

share|improve this question

1 Answer 1

No doubt there is a slick VBA solution, and some really smart person who visits this site will come through with one for you. However.... I came up with a solution that can get you the results you need if you are willing to use a helper column and then a final filter.

I set up your data beginning in the 2nd row. Note that I split your data at the comma delimiter into 2 columns. I added a "helper" column, a "string" column to do the desired concatenation, and a "FinalFilter" column as shown. enter image description here

Here is what the formulas look like (you might need to change your browser Zoom to see it):

enter image description here

And finally, just filter on the "FinalFilter" column = 1, and you have your desired results:

enter image description here

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.