Excel: Amalgamating data to one column.

  John B 14:55 05 Jan 2007

I have a sheet with 100 rows and several columns of data. Column A contains names. Column B has data in some of the cells, column C also has data but not in the same rows as the data in column B. Column D also has data in some of the cells (but not in the rows where column B and C have data.

The data consists of a numbers and letters, e.g. 25Q9/AB and are always in this format.

I would like to amalgamate the data from columns B C D into column E

Is there a way to achieve this please? I have tried using filters and copy/paste to column E but no joy. (Excel 2003).

Thanks in anticipation


  VoG II 14:58 05 Jan 2007

In E1 enter


and copy the formula down.

  John B 15:02 05 Jan 2007

What a rapid response! Problem solved. I've not used & in a formula before.

Thanks again


  daba 19:42 06 Jan 2007

from what you have said, VoG's solution only works and gives results because the "data" you refer to will only exist in column B, or C, or D, in any given row.

in other worksheets, you may wish to "amalgamate" (the proper word is "concatenate") your data from multiple columns using a formula like VoGs, but the data in the cells will be just concatenated "as is".

For example, if you had :-

A1="Albert Squire"




then =B1&C1&D1 would give "27/01/1963PISCESVegan"

you could embellish your output by concatenating other text and spaces:-

try =A1&", born on "&B1&", is birthsign "&C1&". He is also a "&D1&"."

if I typed it right, it should give:-

"Albert Squire, born on 27/01/1963, is birthsign PISCES. He is also a Vegan."

You can concatenate cell values, any text in quotes, other formulas, etc., for example you could calculate Alberts age instead of his bithrdate and put that in the resulting cell.


  GroupFC 19:51 06 Jan 2007

Book marked (for that very helpful info. from daba - thank you)!

  VoG II 19:54 06 Jan 2007

"from what you have said, VoG's solution only works and gives results because the "data" you refer to will only exist in column B, or C, or D, in any given row."

Exactly - that's why such a simple solution was possible. Using & (or CONCATENATE) will just insert a NUL where the cell is empty.

  daba 20:14 06 Jan 2007

I wasn't criticising your helpful solution, I wouldn't dream of it :lol: In fact I'd already seen that John B was delighted with the results.

I was simply giving John B, and anyone else who reads the thread, an insight into what is possible.

John B had indicated he had not used, or thought of using & before. I'm sure that with the extra info it will widen his horizons and allow him to explore Excel's capabilities further.

No offense intended....


  VoG II 20:18 06 Jan 2007

Sorry, daba, I didn't think that you were criticising - I was just trying to explain why the simple & solution worked.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

Halloween Photoshop & Illustrator tutorials: 20 step-by-step guides to creating spook-tacular…

iPhone X news: Release date, price, new features & specs

Comment créer, modifier et réinitialiser un compte Apple ?