Migrate Data from Multiple Columns to one Column

[Phishdawg]

I have a spreadsheet with existing data that has multiple columns collecting data that could better be collected in a single column with a dropdown.

I need to create source data that has all of these answers in single column.

Example:
Single    Married   Divorced   Widow   Other
  (E)            (F)              (G)         (H)         (I)

One column with 'Type_Relationship' would allow for all the above in one column.

In the existing Excel spreadsheet, with all the columns above, I am trying to create a 'Type_Relationship' column in which I can write a formula that will move all the answers to the new column.

In the existing data that is only content in one column. I created a'Type_Relationship' columns at J (behind all the other data), and I triend ' -
     =E2 & F2 & G2 & H2 & I2

The data from the other columns did not populate only the formula as I wrote it. What am I doing wrong?

[#2]

@Phishdawg 

If you want to combine the values from multiple columns into one column using a formula, you can use the CONCATENATE or the ampersand (&) operator in Excel. Here's the correct formula to achieve that:

In cell J2 (assuming your existing data starts from row 2), enter the following formula:

=CONCATENATE(E2,F2,G2,H2,I2)

or

=E2&F2&G2&H2&I2

This formula will concatenate the values from cells E2, F2, G2, H2, and I2 into cell J2. Make sure the cells you are referring to in the formula are correct.

Drag the formula down to apply it to the rest of the cells in column J. This will combine the values from the corresponding cells in columns E, F, G, H, and I into the new column J.

If the result in column J shows the formula itself rather than the combined values, it might be because the cells in columns E, F, G, H, or I have blank cells. In that case, you can modify the formula to handle blank cells using the IF function.

Here is an example:

=IF(E2<>"",E2,"")&IF(F2<>"",F2,"")&IF(G2<>"",G2,"")&IF(H2<>"",H2,"")&IF(I2<>"",I2,"")

This modified formula will only concatenate the non-blank values from the corresponding columns.

Please note that using a formula to combine the values is a one-time action, and any changes in the original data will not be automatically reflected in the concatenated column.

[#3]

@Phishdawg 

Depends on your Excel version you may use

=TEXTJOIN(", ", 1, E2:I2)

and drag it down