# How to Combine (Concatenate) Data from Multiple Rows into One Cell

## Building the Concatenation Helper Column

To combine the rows for each make of vehicle, we are going to build a few columns that help us stitch together the multiple rows, but first we need to make sure the list is sorted. All the vehicles of the same make need to be next to each other for this technique to work.

First, we select all the cells, and choose Sort from the Data menu tab.

We want to sort first by Make and then by Model as the screen shot above shows.

Now that we are sure that the list is in the proper order, we can build our helper columns.

In the next available column (column C in our example), title the first helper column Models. This is where our combined list of vehicle models will end up.

In the first row of data (C2 in this example), write the following formula:

`=IF(A2=A1,C1&", "&B2,B2)`

This IF function compares the value in the Make column of the current row to the Make cell in the previous row. If they are the same, it stitches together the contents of the previous Models cell with the new content from the current Model cell. If they are different, it starts a new list by adding the current row’s Model to the Models cell.

In other words, If the Make is the same, add this Model to the other Models in the list. If the Make is different, start a new list of Models.

Once the first formula is entered, select its cell and drag from the lower right corner down to the end of the worksheet. This will copy the formula down the sheet, changing the cell references inside the formula along the way.

The end result will be a series of lists of vehicle Models that grow with each row, until the Make changes, and a new list is started.

## Get the latest Excel tips and tricks by joining the newsletter!

Andrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it.

Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!

## 81 thoughts on “How to Combine (Concatenate) Data from Multiple Rows into One Cell”

Bang on !!! Super trick..Thanks…

2. Janine Comes says:

This helped article me so much! Thank-you for creating this site. Now that I’ve found it I know that I will be visiting quite often. — Janine

Thanks and it helped.

4. You have no idea how much time this just saved me. Hours upon hours as we calculate the duplicate registration for our client’s conference attendees.

Tricks like these help us clean up our data and provide the clearest picture for our clients. A HUGE thank you!

5. Thanks so much for sharing this. I was doing this manually for a list of streets and page numbers for a mapbook and this saved me loads of time. Great tutorial!

6. Srikanth says:

Thanks Andrew for your Clear article , saved me a lot of time with a huge excel file.

7. Pankaj says:

THanks a lot!!! you are super!!! Great logic used

8. yohan says:

simple and elegant solution; i found veryy very help…i hope to impress my boss ::

thanks!

9. Chris says:

Very clear and helpful. Thanks a lot!

10. diane says:

This is a solution I’m keeping. Very clever.

11. Harish says:

Hi,

This is brilliant. I have 2 more layers of complexity in the Model column in the situation I am facing.

Here, there is only one model for one cell/row. In mine, for say Row 2 – Column A – ACURA, Column B Model is say – ILX, MDX, RLX,
There may also be repeat Models as well for eg, Row 3 – Column A – ACURA – Column B – Model is MDX, RLX,

So when I concatenate, I have the value for ACURA as ILX, MDX, RLX, MDX, RLX. – How do I clean that up to just show ILX, MDX, RLX?

Thanks! I need to send this in by 8 AM EST which I am sure would not be possible but any help would be extremely appreciated!

– Harish

1. Hi Harish!

A couple years late for your deadline, but in case this helps other people, this is what I recommend:

Before you use this tutorial to concatenate your data into a comma-separated list, you need to break out your data into individual items. You’re basically going to have to reverse this concatenation process until you get your list down to:
Acura ILX
Acura MDX
Acura RLX
Acura MDX
Acura RLX

Other tutorials on the internet may help you with this problem, but I suggest starting with the Text-To-Columns feature of the Data tab in Excel.

Then, you can use the “Remove Duplicates” feature of the Data tab in Excel to eliminate the extra MDX and RLX lines in your data.

Finally, you can use this tutorial to get the non-duplicated list to group up again.

Good luck!
Andrew

12. mrWolf says:

Thank you! Saved me hours of trouble ðŸ™‚

13. Pratik says:

Thanks for the great time saving idea saved lot of hours

14. Anthony Bell says:

Awesome. I couldn’t get my head around this but your method makes so much sense. Thank you!

15. Legio says:

Thank you very much for this tip!! Really useful.

16. Jason says:

Thanks so much! This was exactly what I needed and the example made it very easy to understand!

17. smitha says:

Thanks for the clever trick.

18. Gail says:

Thank you SO MUCH! You totally rock!

19. Snehal says:

Thank you a ton!!!

20. Jennifer says:

OMG!! You are a lifesaver! I have worked all day manually merging values on a 13,000 row spreadsheet!!!!!!!!!!!!!!!!!!!!!! This literally took it down to under 1,200 in about 5 minutes. Thank you thank you thank you!

21. SOHAIL says:

Woowwwwww… thanx alot Andrew Roberts..
I was dying to know this “absolute brilliant formula” and u made it extremly easy.. thanx again
You save my life..

22. Hey great tutorial. Worked for me. Thanks a lot.

This is great! One follow up questions… What if you wanted to show an as of date either before or after the selection? I can’t keep it from wanting to repeat the date. Any solutions?

24. Alex Nguyen says:

Dude! You da real MVP! Company has been doing this manually for ages. Made me look like everyone’s Saviour.

25. Chris Wasemann says:

IS THERE A WAY TO APPLY A CONDITION THAT IF THERE IS NO VALUE IN THE ASSIGN CELLS DO NOTHING (=IF(K111=K110,O110&”, “&M111,””)???

26. spongman says:

this handles empty cells:
=IF(AND(B2=B1,D1″”,D2″”),E1&”, “&D2,””&D2)

1. Purplicious says:

Thanks, that is useful. Quicker than writing the formula to do it.

27. Purplicious says:

Thanks this is great. I have been trying to find a way to do this for ages.

I am cuurently trying to concate several dates and I can’t get the first date to format correctly. Subsequent dates do format.

I’m using =IF(A46=A45,BB45&”; “&TEXT(Y46,”dd/mm/yy”),Y46)

which gives me 41761; 29/04/14; 28/02/14

Any help would be much appreciated.

28. Ashit says:

Thanks a lot…this trick was really helpful and saved a lot of time

29. Sean Ford says:

This is great! How can I do this when I have multiple columns of data? For example, what if this example also had columns for color, year, and transmission?

30. David says:

Hi, Great article! It’s exactly what I’m looking for; however, do you have a quick tutorial on how to do the process backwards?

I was given a row with multiple model #’s, however want to split it up into individual rows. How can i go about this?

Thanks again!

1. You might look to the Text-to-Columns feature in the Data Tools section of the Data menu tab. This should let you break out the models into individual cells. Good luck!

31. Anil says:

This trick was very helpful and helped save time!

32. Vanessa says:

I added this formula to a massive file. Now, how do I delete all of the records that I don’t need any longer without manually doing it one by one?

33. Jeremy says:

Thank you~!!Saved my bacon with this ðŸ™‚

34. wiwiek says:

I’ve been wasting many hours to merge my data. your solution is so cool. you save my day.

35. taz says:

Hi.
How would i reverse this process. I get my data the other way around and need to sort it like the first page. Thanks in advance

36. Thanks for your sample formula. Really help me to collect data.. ðŸ™‚

37. Francesco Dotti says:

Thanks a lot,
Francesco

38. Excellent.
I was using the concatenate function differently that was consuming me more time.
Thanks so much for the information.
Regards,
Edward…

39. DHARMENDRA KUMAR LUCKNOW says:

THANKS SIR JI

40. danny says:

Thanks. this saved me a an hour. I needed to create a list of 150 sales reps and the states they cover. Added an additional if to compare the states and voila, a useful list.

41. VINOD JAISWAL says:

THANKS SIR JI

42. Uday says:

This is just beaaautiful!! Thank you so much!… Wonderfully explained.!

43. Gavin McMinn says:

Lifesaver. Thanks for this!

44. Research assistant says:

Thank you!

45. suresh chandra says:

so nice and simple post and resolved the issue
Thanks a lot

46. gopi says:

Awesome Bro, Its great logic, works perfectly…

47. sarala says:

Thanks ! worked for me ðŸ™‚

48. Anu says:

49. Melissa says:

Thank you!!

50. Kalyan says:

I have used it successfully. In addition to this, i have to extract first (left) 5 characters from the concatenated cell and put it in another cell.

thanks

51. Laura says:

This is awesome! Is there a formula for the reverse process? I have a list with values in one cell separated by commas and I need to convert it to each value is in a separate row.

52. Jez Greenwood says:

Well good, thank you!

53. Crystal says:

This was a GREAT video. It helped ALOT when we were combining 10,000 lines. I knew the combination needed an If function but could not develop the formula.

54. Sachin Rajput says:

Awesome Bro, Good logic, Thanks Lot.

55. michael says:

Good stuff. This helped me ðŸ™‚

56. Fayez ul haq says:

What I was searching for days..!! Superb guide. Many thanks..:)

57. This is great!
I had to combine students attendance and academic standings in order to use V-look up successfully. It’s always great to find helpful and clear tips for Excel. Thank you!!

58. SJ says:

Helped me for large data consolidation, thank you very much !!

59. Irene Patterson says:

Thank you so much! You don’t understand how much this helped me!

60. Thank you. I have used it for our Civic Association for a membership reminder. There are a few tables in the dbase (place,member,payment,owner etc) and more than one member with an email address for the same properties. This helped me to use only unique email addresses to send personalized letters to members with a list of ALL the properties included and not a email for every property. I did find a small program from a third party, but will have to pay more than R500 to use it 4 times a year.

61. ANIL AWASTHI says:

I HAVE THE DATA IN DIFFERENT COLUMNS LIKE A, B,C, D E, F, G etc but all the cells are not filled, I want that the data in different columns must be copied in a single column say for example H. How ??

62. JJ says:

Hello,

I’m using excel 2010. When I use your method, all rows except the last return 0. The last returns the first value. Any suggestions?

Thanks,

JJ

63. annie says:

Yay!

Every other solution I found required purchasing an add-in or creating a macro.

Thank you <3

64. Ashley Kuttler says:

This is genius, thanks!!

65. Sid says:

Thanks so much for this. Almost every other site provided really complex VBA code based solutions. This on the other hand was a really simple and elegant solution. Thanks again!

66. Anjana says:

You are the best !!! Saved a lot of my time ðŸ™‚

67. spg says:

thanks a lot man, much appreciate , works flawlessly

68. Nirmal says:

Brilliant and very helpful post. was thinking of doing this by taking to sql server then realized there could be soln in excel. This one helped a lot. Thank You so much..

69. Sandy says:

Excellent trick dude, but I have one question is there any data row limit in this formula. When i Tried using this in my data set it stuck 2043 rows. Please help.

70. Joe says:

Well played sir. Very much appreciated. You even expanded my knowledge of different ways to achieve concatenates ðŸ™‚

71. uday says:

Fantastic!!

Thanks a ton & I can’t thank you enough.

72. hemant raj says:

73. Meow says:

Thank you so much! This really helped me with my data analysis.

74. RD says:

Well done Thanks a lot !!!

75. Amee says:

Thanks so much for this tutorial!

This helped me consolidate thousands of lines of data for a vendor’s template without me going crazy or needing to pester coworkers over the holidays.

You rock!!

76. Chris says:

Oh man, this saved me a couple of hours work! Thanks for sharing and keep it up!