98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Combine data from multiple rows and columns into one cell
Combine data from multiple rows and columns into one cell
Resolved · Urgent Priority · Version 2010
Wed 8 Mar 2017, 14:46Delegate Andrew said...
Andrew has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Excel Advanced - For Power Users course
Excel Dashboards for Business Intelligence course
Combine data from multiple rows and columns into one cell
I have the formula to combine data from multiple rows into a single cell in D2: (D1 is the header of this new column)
=IF(A2=A3,D1&", "&B2,B2)
However it doesn't work when there are more than 2 columns of data. I actually have 10 columns in my workbook, but if you can show me how its possible with 3 then that would help. The video link is at the bottom.
Col A Col B Col C Col D
Make Model Year Models (D1)
Ford GTS 2010
Ford GTS 2011
Ford Ghia 2011
Ford RS 2013
Audi A3 2009
Audi A5 2009
http://www.exceltactics.com/combine-data-multiple-rows-one-cell/
Wed 8 Mar 2017, 15:25Trainer Jens said...
RE: Combine data from multiple rows and columns into one cell
Hi Andrew,
Thank you for the forum question.
I have attached an example. Please if you cannot see the attachment wait a couple of minutes and refresh your browser.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
cars.xlsxWed 8 Mar 2017, 15:45Delegate Andrew said...
RE: Combine data from multiple rows and columns into one cell
Hi Jens,
Nothing has come through, I've even re-booted my pc from scratch.
Andrew
Wed 8 Mar 2017, 15:50Trainer Jens said...
RE: Combine data from multiple rows and columns into one cell
Hi Andrew,
The attachment can be found under my signature in my previous answer. If you still cannot see it, please press F5. If it is still not visible, please let me have your email address, then I can send it to you.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 8 Mar 2017, 16:18Delegate Andrew said...
RE: Combine data from multiple rows and columns into one cell
Hi Jens,
I'm struggling to get your formula to work in my worksheet as unfortunately I have 9 columns worth that I want all in one cell.
Do i need to use 'AND' in the formula?
Could you give me an example please?
Thanks
Wed 8 Mar 2017, 16:50Trainer Jens said...
RE: Combine data from multiple rows and columns into one cell
Hi Andrew,
I have done another example with 5 columns.
Please find attached file.
No you will not need an AND function. The AND function can be used if you have multiple criteria.
My answer is based on the video you linked to your question. If you just want to concatenate columns type:
=A1&", "&B1&", "&C1&", "&D1................
in the destination cell
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:ChartsSelect the data and press the function key F11 and the chart will be created on a separate worksheet. |
