How to merge multiple columns in Excel so that unique values are retained in the new column?

Question

Say the data are in Col1 and Col2 below. How can we create Col3 by merging Col1 and Col2?

Col1  Col2   Col3
------------------
  a      b      a
  b      c      b
  c      d      c
  g      e      d
         f      e
                f
                g

Answer 1

Do this in a scratch copy of your file, just in case something blows up or melts down, because “Undo” doesn’t always work.  Copy the data from the two columns, sequentially, into another scratch column; let’s call it V.  If your existing data have headers, copy the heading from Column A; otherwise, put some dummy value in cell V1.

Click somewhere in Column V.  Now go to the “Data” tab, “Sort & Filter” group, and click on “Advanced”:

         

This will bring up the “Advanced Filter” dialog box:

        

Verify that “List range” shows your data in Column V.  Select “Copy to another location” and “Unique records only”.  Type “W1” in the “Copy to” field — or click in the field, and then click in W1 (there are several techniques that will get the same result).  Click on “OK”. You should get something like this:

           

Now copy the data from Column W to Column C.  If desired, sort.  Then clear Columns V and W.

^{Disclosure: the above answer was partially copied from my answer to Grouping data columns by shared values.}

Answer 2

This VBA should do it. I'm sure there's a formula way, but I find VBA a lot easier to read in these situations

Sub FindUniques()
'Define and set variables
Dim rangeIn As Range, rangeOut As Range
Set rangeIn = Application.Selection
Set rangeIn = Application.InputBox("Input Range", "Input Range", rangeIn.Address, Type:=8)
Set rangeOut = Application.InputBox("Select the first cell to output to", "Output Range Start", Type:=8)

Dim colLoop, rowLoop

Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")

Dim curVal


'Loop through range columns
For colLoop = 1 To rangeIn.Columns.Count
    'Loop through range rows
    For rowLoop = 1 To rangeIn.Rows.Count
        'Get the value of cell in current column, current row
        curVal = rangeIn.Cells(rowLoop, colLoop).Value
        'Check if we've already added this to the dictionary. If we have, it's not unique, skip it
        If Not dic.Exists(curVal) And curVal <> "" Then
            'Write the value in place of our output
            rangeOut.Value = curVal
            'Add the value to our dictionary so it'll be skipped if it comes up again
            dic(curVal) = ""
            'Go to the next cell down ready to write the next one
            Set rangeOut = rangeOut.Offset(1, 0)
        End If
    Next
Next
End Sub

Answer 3

Here is a way using hidden columns that does not require VBA. This does not sort the final column, however.

After entering these formulas, you can hide the three columns in the middle by right-clicking their headings and selecting Hide:

And that will leave you with this:

The formulas in the four columns after Col1 and Col2 will have to be copied down to at least twice the maximum length of Col1 and Col2. I suggest picking an arbitrary large number that you feel certain your Col1 and Col2 number of rows will never exceed and then just forgetting it.

This assumes that your Col1 and Col2 are in columns A and B and that the header row is included and the header row is in row 1. You'll have to modify these slightly if no header row is included.

The Combined column's formula:

=OFFSET($A$2, FLOOR((ROW() - ROW($2:$2)) / 2, 1), MOD(ROW() - ROW($2:$2), 2))

The Filtered column's formula:

=IF(C2 = 0, "", IF(ISERROR(MATCH(C2, C$1:C1, 0)), ROW(), ""))

The Sorted column's formula:

=SMALL(D:D, ROW() - ROW($1:$1))

The Col3 column's formula:

=IFERROR(INDEX(C:C, E2), "")

The way this works is to first combine Col1 and Col2 starting at the first row and proceeding downward (Cell A2, Cell B2, Cell A3, Cell B3, etc.). It does this by using a combination of the OFFSET function combined with the FLOOR, MOD, and ROW functions.

Then we filter out duplicates by testing whether the value exists in the current row through all previous rows. We use the MATCH function for this in combination with some clever use of absolute and relative references. The row number is returned instead of the value so that it can be used in the next two columns.

Then we bring all the listed row numbers to the top by a combination of the SMALL and ROW functions.

And finally, we just need to return the INDEX of the Combined column corresponding to the row of the Sorted column.

Answer 4

VBA solution

Sub MergeColumnsUnique()    
  Dim MyLookupCols As Variant: MyLookupCols = Array(1, 2)
  Dim MyOutputCol As Integer: MyOutputCol = 3
  Dim MySheet As Variant: Set MySheet = ThisWorkbook.Sheets(1)

  For Each col In MyLookupCols
    curCol = MySheet.UsedRange.Columns(col)

    For Each cell In curCol
      If Not IsEmpty(cell) And MySheet.Columns(MyOutputCol).Find(cell) Is Nothing Then
        Count = Count + 1
        MySheet.Cells(Count, MyOutputCol) = cell
      End If
    Next cell

  Next col        
End Sub

Answer 5

Google Sheets Only

This is only available if you're using google sheets, but they have much more sophisticated handling of arrays

You can do it in a simple one-liner:

=SORT(UNIQUE({A1:A4;B1:B5}))

Breakdown

To combine multiple ranges into an array, use {} and delimit multiple ranges with semicolons ;

={A1:A4;B1:B5}

This will produce a single vertical column with both ranges combined

Then use Unique and Sort which will both take in array values to produce a final columnular result