Create one new column of unique data from two columns

[Lenshaw]

Hi,

I am attempting to set up an automated workbook that will (using formulae) do comparisons between two reports. As a starting point I need to identify a list of unique ID numbers as these vary from report to report.

I have two columns of ID numbers, one from each report, each ID is made up of letters and numbers. I want to compare these two columns of data and create from them one column of data on a new workbook that contains only the unique values.

For example:

ColumnA
2
5
4
9
8
7
1

ColumnB
2
5
9
3
6
8
7
11

Result should be:
2
5
4
9
8
3
7
1
6
11

Further details:
Each column contains approx 550 IDs but this will vary from report to report
The two columns are on seperate tabs of the same workbook
Each column will contain a handful of ID numbers that are not in the other column, the remainder will be the same

I am able to identify unique records using filters for each column individually but I want to do this across the two columns and have it pull out into a new column with no manual processing/checking.

Any advice/pointers will be much appreciated!

[Blake 7]

create a named range for your first list called "List1" do the same for your second list and call it "List2"

then whereever you want your distinct list of id's to be use the formula below and enter with CSE

=IFERROR(IFERROR(INDEX(list1, MATCH(0, COUNTIF($F$1:F2, list1), 0)), INDEX(list2, MATCH(0, COUNTIF($F$1:F2, list2), 0))), "")

[Lenshaw]

Hi Blake 7,

Thanks for your reply.

Can you explain what CSE means and what should be in cells F1:F2? The formula fails for me as these cells are blank in my workbook.

[Blake 7]

I put the formula in F2. Thats why I put $F$1:F2, if you put the formula in G2 the you would change the formula to $G$1:G2.

CSE is control, shift + enter. because you press CTRL+SHIFT+ENTER to enter them into your workbooks.

[Lenshaw]

This doesn't seem to work, the result is a column of zeros. Can you give me more detail on how this formula works so I can understand it?

[Blake 7]

This doesn't seem to work, the result is a column of zeros. Can you give me more detail on how this formula works so I can understand it?

Please see the attached which should help. But even better, I would use a udf...

Function Uniques(ByVal inputRange As Range) 
    Dim inputArray As Variant 
    Dim myColl As New Collection 
    Dim xVal As Variant 
    Dim outArray() As Variant 
     
    On Error Resume Next 
    With inputRange 
        inputArray = Application.Intersect(.Cells, .Parent.UsedRange).Value 
    End With 
    On Error Goto 0 
     
    On Error Resume Next 
    For Each xVal In inputArray 
        myColl.Add Item:=xVal, key:=(CStr(xVal) & TypeName(xVal)) 
    Next xVal 
    myColl.Remove "String" 
    On Error Goto 0 
     
     
    Redim outArray(1 To Application.Max(myColl.Count, Application.Caller.Cells.Count)) 
    For xVal = 1 To UBound(outArray) 
        outArray(xVal) = vbNullString 
    Next xVal 
     
    For xVal = 1 To Application.Min(myColl.Count, Application.Caller.Cells.Count) 
        outArray(xVal) = myColl(xVal) 
    Next xVal 
     
    If Application.Caller.Columns.Count = 1 Then 
        Uniques = Application.Transpose(outArray) 
    Else 
        Uniques = outArray 
    End If 
     
End Function

Taken from

http://www.ozgrid.com/forum/showthread.php?t=86452

[Fotis1991]

..And of course, we can do it using Array formula.

In F2 and copy down.

=IFERROR(IF(ROWS(F1:$F$1)<=SUM(1/COUNTIF(List1;List1));INDEX(List1;MATCH(0;COUNTIF(F1:$F$1;List1);0));INDEX(List2;MATCH(0;COUNTIF(F1:$F$1;List2);0)));"")

Replace ALL semi colons in my formula to comma.