combining two list

[PearTrader]

I need to combine two list into one.

The spreadsheet would appear something like the following

A (input)
Dogs
Cats
Cows
Horses
Pigs

B (input)
Horses
Monkeys
Hippopotamus
Dogs
Kangaroos

C (output)
Dogs
Cats
Cows
Horses
Pigs
Monkeys
Kangaroos

While Dogs and Horses appear on both list, they only appear once in the final (output) list. I'm sure its some combination of IF, MATCH and LOOKUP, I'm just having trouble figuring it out. Thanks!

 

[Brian from Maui]

See,

http://www.mrexcel.com/board2/viewtopic.php?t=8659

 

[PearTrader]

Thanks, but I don't think this will work, as I'm trying to make this list completely dymanic, and as userfriendly as possible...some users may not understand the 'autofilter' command. I was hoping to find some combination of IF, MATCH and IS functions that would generate a combined list (with no duplicates and no spaces).

Can anyone offer any additional suggestions?
Thanks!

 

[Brian from Maui]

Thanks, but I don't think this will work, as I'm trying to make this list completely dymanic, and as userfriendly as possible...some users may not understand the 'autofilter' command. I was hoping to find some combination of IF, MATCH and IS functions that would generate a combined list (with no duplicates and no spaces).

Can anyone offer any additional suggestions?
Thanks!

The link provides several solutions other than auto filter.

 

[Aladin Akyurek]

Close all open books, except the target book.
Activate Tools|Macro|Visual Basic Editor.
Activate Insert|Module.
Copy the VBA code below and paste in the pane, entitled ...(code).
Activate File|Close and return to Microsoft Excel.

Option Explicit

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
    ' Code: Juan Pablo González
    ' Spec: Aladin Akyurek
    ' May 4, 2003
    ' Ref: http://makeashorterlink.com/?P20022174
    ' Mod: Nov 3, 2003, to reduce number of ReDim Preserve calls.
    Dim TempUnion() As Variant
    Dim i As Long, Itm As Variant, Ctr As Long
    ReDim TempUnion(1 To UBound(Arg) - LBound(Arg) + 1) As Variant
    For i = LBound(Arg) To UBound(Arg)
        Arg(i) = Arg(i)
        If IsArray(Arg(i)) Then
            For Each Itm In Arg(i)
                Ctr = Ctr + 1
                If Ctr > UBound(TempUnion) Then
                    ReDim Preserve TempUnion(1 To UBound(TempUnion) * 2) As Variant
                End If
                'ReDim Preserve TempUnion(1 To Ctr) As Variant
                TempUnion(Ctr) = Itm
            Next Itm
        Else
            Ctr = Ctr + 1
            If Ctr > UBound(TempUnion) Then
                ReDim Preserve TempUnion(1 To UBound(TempUnion) * 2) As Variant
            End If
            'ReDim Preserve TempUnion(1 To Ctr) As Variant
            TempUnion(Ctr) = Arg(i)
        End If
    Next i
    If Ctr< UBound(TempUnion) Then
        ReDim Preserve TempUnion(1 To Ctr) As Variant
    End If
    ArrayUnion = TempUnion
End Function

The exhibit below shows the formulas to compose a uniquified list from 2 input lists...

aaCombineListsIntoUniquifiedList PearTrader.xls
	A	B	C	D	E
1	5	5			8
2	ListA	ListB	I-List	0	F-List
3	Dogs	Horses	Dogs	1	Dogs
4	Cats	Monkeys	Cats	2	Cats
5	Cows	Hippopotamus	Cows	3	Cows
6	Horses	Dogs	Horses	4	Horses
7	Pigs	Kangaroos	Pigs	5	Pigs
8			Horses		Monkeys
9			Monkeys	6	Hippopotamus
10			Hippopotamus	7	Kangaroos
11			Dogs
12			Kangaroos	8
13
14
15
Sheet1

A1:

=MATCH(REPT("z",255),A:A)-CELL("Row",A3)+1

B1:

=MATCH(REPT("z",255),B:B)-CELL("Row",B3)+1

C3:

=IF(ROW()-ROW($C$3)+1<=$A$1+$B$1,INDEX(arrayunion($A$3:$A$7,$B$3:$B$7),ROW()-ROW($C$3)+1),"")

D2 must house a 0.

D3:

=IF((C3<>"")*ISNA(MATCH(C3,$C$2:C2,0)),LOOKUP(9.99999999999999E+307,$D$2:D2)+1,"")

E1:

=LOOKUP(9.99999999999999E+307,D:D)

E3:

=IF(ROW()-ROW($E$3)+1<=$E$1,INDEX(C:C,MATCH(ROW()-ROW($E$3)+1,D:D)),"")