How do I list all values in 3 different columns in a new column?

Question

I have three seperate columns with values in an Excel Sheet. Now I want to create a new column that concatenates all values from these columns. When I add new values to one of these three columns, I want the summarized column to update automatically. How do I do this?

Table1

ColumnA  ColumnB  ColumnC
VALUE1   VALUE4   VALUE7
VALUE2   VALUE5   VALUE8
VALUE3   VALUE6   VALUE9
         VALUE10

Generated Result:

Table2

VALUE1
VALUE2
VALUE3
VALUE4
VALUE5
VALUE6
VALUE10
VALUE7
VALUE8
VALUE9

Answer 1

Enter the following Event Macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim inpt As Range, i As Long, K As Long, N As Long
    Dim j As Long

    Set inpt = Range("A:C")
    If Intersect(Target, inpt) Is Nothing Then Exit Sub
    Application.EnableEvents = False
        Range("D:D").Clear
        K = 1
        For i = 1 To 3
            N = Cells(Rows.Count, i).End(xlUp).Row
            For j = 1 To N
                Cells(K, 4).Value = Cells(j, i).Value
                K = K + 1
            Next j
        Next i
    Application.EnableEvents = True
End Sub

It will monitor changes to columns A through C and update column D accordingly.

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

Answer 2

I’ve got an answer that doesn’t require coding.

• I’ll assume that you want to put the combined (concatenated) list in Column D of the same sheet.  If you want it elsewhere, that’s a trivial change.
• My solution requires two “helper columns”; for example, Columns E and F on the same sheet.  If you want them elsewhere, that’s a trivial change.
• I assume that you will not be putting blanks in the first three columns (i.e., in the data, before the last value), and that you want those columns simply concatenated, and not sorted or deduped.  If I misunderstand, that’s a major change.
• I assume that every column has at least one value; i.e., that A1, B1 and C1 are non-blank.  If that assumption is not valid, this solution can (probably) be adapted for that.

---

Solution:

• Enter =IF(E1<0, "", OFFSET($A$1, F1, E1)) into cell D1.
• Drag/fill cell D1 down into D2.
• Enter 0 into cells E1 and F1.
• Enter =IF(E1<0, E1, IF(OFFSET($A$1, F1+1, E1)<>"", E1, IF(E1<2, E1+1, -1))) into cell E2.
• Enter =IF(E1<0, 0, IF(OFFSET($A$1, F1+1, E1)<>"", F1+1, 0)) into cell F2.
• Select cells D2:F2 and drag/fill down as far as you expect the combined list ever to be.  (Of course you can always extend it later.)

Columns E and F contain the (0-based) column and row number of the value in Column D.  For example, 0,0 is A1, 0,2 is A3, 1,3 is B4, etc.  A negative value in Column E indicates that you are beyond the end of the data.  The formula in Column D evaluates to blank if the E value is negative; otherwise, it uses the OFFSET() function to retrieve the value indexed by the E and F numbers.

The formulas in Columns E and F check whether the previous E value is negative, and, if it is, set the next row to -1,0 — so, once you’re past the end of the list, you’re past it, and you don’t start tying yourself into knots.  Otherwise, if there is a next value in the current column, we advance to that value (E1,F1+1).  Otherwise, we go to the top of the next column — except, if the current column is #2 (C), we advance to column #-1, because we’re at the end.

      

You can now add and delete values in Columns A, B and C, and the list in Column D will update automatically.  Once you get this working, you can hide the helper columns.