0

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
  • How can we answer this? I have no idea how you want to summarise the values... by occurrence? By those with a value greater or less than something? What type of new value would you want to add? – Dave Dec 2 '16 at 12:28
  • I just want a dull list of the values in the three columns. Nothing fancy. My problem is how it recongizes when I add a value to the first column so all the values from the second column move down so I don't have to change or add any formula. – Benedikt Buchert Dec 2 '16 at 12:34
  • I think you want to use INDIRECT – Dave Dec 2 '16 at 12:36
  • 1
    Sorry. Added an example hope it is more clear now. – Benedikt Buchert Dec 2 '16 at 19:03
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.

enter image description here

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!

  • Great! How Would I have to modify the code to be able to access Tables in different other WorkSheets? – Benedikt Buchert Dec 2 '16 at 15:02
  • @BenediktDichgans You would place a similar macro in each worksheet, (adapting it to the details of that worksheet) – Gary's Student Dec 2 '16 at 15:10
  • Hm I have hoped for a solution without coding. Thanks you for your help though. – Benedikt Buchert Dec 2 '16 at 15:33
1

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.

      screenshot of spreadsheet

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.

  • I like your solution the only problem is the sortability. – Benedikt Buchert Dec 7 '16 at 8:46
  • Well, gosh, (1) the example in your question isn't sorted, and (2) Gary's Student's answer doesn't provide sorting, either. If sorting is an issue, why didn't you say so? – Scott Dec 7 '16 at 19:02
  • I'm sorry! I didn't know this was relevant for the solution... – Benedikt Buchert Dec 7 '16 at 19:07
  • A kid walks into a candy shop and asks for chocolate.  The shopkeeper shows him a box of chocolates, and the kid says, “That looks great.”  Then the shopkeeper says, “Too bad strawberries are out of season, so I don’t have any chocolate-covered strawberries.”  The kid (gesturing toward the box of chocolates) says, “Well, then, these are no good; I really wanted chocolate-covered strawberries.” … … … … … … …  Do you get the point?  You started wanting a feature only after you were told that it would be hard to obtain. – Scott Dec 9 '16 at 19:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.