Coffinated
asked on
combine multiple cells in excel into a single cell
Experts,
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
...
P50=Bob
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
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
...
P50=Bob
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
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ButlerTechnology,
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Coffinated.xls