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/
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.xlsxRE: 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
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
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
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
