combining two list

PearTrader

New Member
Joined
Jan 5, 2004
Messages
5
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!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

PearTrader

New Member
Joined
Jan 5, 2004
Messages
5
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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
PearTrader said:
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.

Rich (BB code):
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
ABCDE
1558
2ListAListBI-List0F-List
3DogsHorsesDogs1Dogs
4CatsMonkeysCats2Cats
5CowsHippopotamusCows3Cows
6HorsesDogsHorses4Horses
7PigsKangaroosPigs5Pigs
8Horses Monkeys
9Monkeys6Hippopotamus
10Hippopotamus7Kangaroos
11Dogs  
12Kangaroos8 
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)),"")
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,872
Messages
5,766,864
Members
425,382
Latest member
IronM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top