combine multiple cells in excel into a single cell


I need to perform some work with large worksheets. Most of them have over 1700 rows and multiple columns spanning from A to IZ. The problem is that the last set of column with text need to be combined for example

P1= Joe
P2= Marry

All data needs to be combined into a single cell for example:
P55=Joe, Marry, ..., Bob

Cell numbers are approximate they vary on all worksheets.

Can someone recommend a way of doing it fairly easy? concatenate function would require tedious work.

I am using Excel 2010.

Thank you
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CharlesSenior Software EngineerCommented:
Hi ,

Find the attached sample xls, This will do XLS Multiple no.of cells data into single cell in both row and column wise,

Don't confuse with the formula, Its very simple as below

Put one = @ the cell ehere you want concatenated data and place your cursor on ther cell 1 , system will load that cell reference by default it may be a1 or some numeric since you are using office 2010, then add &","& this used to place a comma inbetween 2 different cells data, Please procced
The only alternative to doing 1700 rows individually is to let code do it for you.
In order to enable code to be written you need to define two items in a generic way.
1. The cells to collate data from, and
2. The cell to write data to.
A "generic way" would be a description like "the column next to the last column which has a caption in row1" or "the column with the caption 'soandso'. You can generate conditional generic descriptions, like, "if the sheet has 15 columns (or 'this' name) use this way, else use that way". Code is very adapt at following even the most intricate descriptions precisely, but it will fail if it meets with conditions which the instructions didn't anticipate.
Public Sub BigConcatenate()
Dim theResults As String
Dim X As Integer

ActiveCell.Offset(1, 0).Activate

  theResults = ""
  X = 0
    theResults = theResults & ActiveCell.Offset(0, X).Value & ", "
    X = X + 1
  Loop Until IsEmpty(ActiveCell.Offset(0, X))
  theResults = Left(theResults, Len(theResults) - 2)
  ActiveCell.Offset(0, X).Value = theResults
  ActiveCell.Offset(1, 0).Activate
Loop Until IsEmpty(ActiveCell)
End Sub

Open in new window

The above macro should do what you want.  It will concatenate the values from every column in a row with a comma and put the results in the first blank column.  It doesn't care if the data set has 10 or 100 columns and/row.

It does assume that the data set has headers and the active cell is the First header.  It takes the value of the cell, so if the cell changes the results do not change.

CoffinatedAuthor Commented:

I like your macro, it worked on some of the cells. It does not work on cells with leading white space. for example a cell has a value "  Bob" it'll skip all cells in the row.

Otherwise it works good. Would you be able to either change it loop through 3000 rows or so? It could help to concatenate data regardless of the first character.

Thank you
Interesting -- I put white space in my data set "   A" and the macro completed.  I wonder what is different in our data sets.  Can you mock up a sheet that reflects your data?  I did notice that if a cell is empty in the middle of the data set that the macro will fail.

Does your data set have headings?  I can re-write the inner loop not to rely on a blank cell to determine if it is complete.

Does the data have blank cells?

Is the data contiguous in terms of rows?  The outer loop should be able to handle any number of rows.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.