Consolidating column values based on another column value

I'm looking to do the following in Excel:

I have a table and want to write a function or formula to look at the values in one column and where they are identical, take the value in another column and concatenate it to the column value for the first record it found (in a new column).

For example:

ADDRESS                         PAGE         NEW COLUMN
1 810 ANDERSON TERR 1817-2     1817-2
10 1940 HILLSIDE DR 1315-42    1315-42
101 TRANQUILLE RD     开发者_StackOverflow         1518-53     1518-53 1517-30
101 TRANQUILLE RD              1517-30     
1020 HILLSIDE DR               1417-111    1417-111 1417-112
1020 HILLSIDE DR               1417-112 
10285 DALLAS DR                1330-10     1330-10 1330-3
10285 DALLAS DR                1330-3   
1051 PINE SPRINGS RD           2218-46     2218-46 2218-18 2218-31 2217-6 2218-32
1051 PINE SPRINGS RD           2217-18  
1051 PINE SPRINGS RD           2218-31  
1051 PINE SPRINGS RD           2217-6   
1051 PINE SPRINGS RD           2218-32  

Any insight would be greatly appreciated!!

Regards

I would sort the table on the column in question, then you get all rows with the same column value after each other. On the sorted table, you can then use a function like

=IF('column value' = 'previous column value', 'concatenate values', '')

If you always need to concatenate the FIRST value found, you can make another column that always contains just the first found value for a set of rows that belong together and concatenate with that.

More precise:

1) Sort table 2) Assuming Address in column A and Page in column B, add this formula in column C (and copy down):

=IF(A2=A1,C1& " " & B2, B2)

This gives exactly the values in your example:

ADDRESS PAGE NEW

1 ANDERSON 1817-2 1817-2

10 HILLSIDE 1315-42 1315-42

101 TRANQUILLE 1518-53 1518-53

101 TRANQUILLE 1517-30 1518-53 1517-30

1020 HILLSIDE 1417-111 1417-111

1020 HILLSIDE 1417-112 1417-111 1417-112

10285 DALLAS 1330-10 1330-10

10285 DALLAS 1330-3 1330-10 1330-3

1051 PINE 2218-46 2218-46

1051 PINE 2217-18 2218-46 2217-18

1051 PINE 2218-31 2218-46 2217-18 2218-31

1051 PINE 2217-6 2218-46 2217-18 2218-31 2217-6

1051 PINE 2218-32 2218-46 2217-18 2218-31 2217-6 2218-32

If you do not want the intermediate concatenations you have to hide column C and make another one which copies the value of column C when the value in column A changes.

And as a function:

Sub Combine()

    Dim rwNumber As Integer
    Dim value As String
    Dim oldValue As String
    Dim concatenatedString As String

    'Sort the table
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A2:C14")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    For rwNumber = 2 To 100
       'gets value of first cell
       value = ActiveWorkbook.Worksheets("Sheet1").Cells(rwNumber, 1).value
       If value = oldValue Then
        concatenatedString = concatenatedString + " " + ActiveWorkbook.Worksheets("Sheet1").Cells(rwNumber, 2).value
       Else
            ActiveWorkbook.Worksheets("Sheet1").Cells(rwNumber - 1, 3).value = concatenatedString
            concatenatedString = ActiveWorkbook.Worksheets("Sheet1").Cells(rwNumber, 2).value
       End If
       oldValue = value
    Next rwNumber
End Sub

Or something like that :-)