I have 2 columns with names
I want to have a 3rd column with only the names that match
Is there a function for doing this
--
Carla Heimbigner

Is one list longer than the other?

--
Biff
Microsoft Excel MVP


"Cheimbig" <(E-Mail Removed)> wrote in message
news:6F9F7BB5-FB9A-4F4D-9C5F-(E-Mail Removed)...
>I have 2 columns with names
> I want to have a 3rd column with only the names that match
> Is there a function for doing this
> --
> Carla Heimbigner

Yes - one column 328 and the other 162
--
Carla Heimbigner


"T. Valko" wrote:

> Is one list longer than the other?
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Cheimbig" <(E-Mail Removed)> wrote in message
> news:6F9F7BB5-FB9A-4F4D-9C5F-(E-Mail Removed)...
> >I have 2 columns with names
> > I want to have a 3rd column with only the names that match
> > Is there a function for doing this
> > --
> > Carla Heimbigner
>
>
>

Ok, try this...

Assume:

Long list in the range A2:A329
Short list in the range B2:B163

Create these named ranges...
Goto the menu Insert>Name Define
Name: Llist
Refers to: =$A$2:$A$329

Name: Slist
Refers to: =$B$2:$B$163

Enter this formula in cell D1. This will return the number of matches.

=SUMPRODUCT(--(ISNUMBER(MATCH(Slist,Llist,0))))

Enter this array formula** in D2:

=IF(ROWS(D$22)<=D$1,INDEX(Slist,SMALL(IF(ISNUMBER(MATCH(Slist,Llist,0)),ROW(Slist)),ROWS(D$22))-MIN(ROW(Slist))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy down until you get blanks meaning all matches have been extracted.

--
Biff
Microsoft Excel MVP


"Cheimbig" <(E-Mail Removed)> wrote in message
news:7D893A55-2CB9-42DB-AC75-(E-Mail Removed)...
> Yes - one column 328 and the other 162
> --
> Carla Heimbigner
>
>
> "T. Valko" wrote:
>
>> Is one list longer than the other?
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Cheimbig" <(E-Mail Removed)> wrote in message
>> news:6F9F7BB5-FB9A-4F4D-9C5F-(E-Mail Removed)...
>> >I have 2 columns with names
>> > I want to have a 3rd column with only the names that match
>> > Is there a function for doing this
>> > --
>> > Carla Heimbigner
>>
>>
>>

Hi,

You can use advanced filters as well.

1. Assume you have 2 lists as follows. List 1 (including heading) is in
range C4:C13 and List 2 (including heading) is in range D49

List 1 List 2
A A
S T
D Y
f H
g G
H
J
L
M

2. Type condition in C16;
3. Type =COUNTIF($C$5:$C$13,D5)=1 in C17
4. Type List 2 in cell C21
5. Now go to Data > Advanced Filter and click "Copy to another location"
6. In the list range, select C413;
7. In the criteria range, select C16:C17
8. In the Copy to box, please refer cell C21
9. Now click on OK

Please not that this in not a dynamic solution. If any entry in List 1 or
List 2 changes, you will have to rerun the advanced filter.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Cheimbig" <(E-Mail Removed)> wrote in message
news:6F9F7BB5-FB9A-4F4D-9C5F-(E-Mail Removed)...
> I have 2 columns with names
> I want to have a 3rd column with only the names that match
> Is there a function for doing this
> --
> Carla Heimbigner

Can I just send one of you the file --- neither of these worked for me - so I
am clearly doing something wrong.
--
Carla Heimbigner


"Ashish Mathur" wrote:

> Hi,
>
> You can use advanced filters as well.
>
> 1. Assume you have 2 lists as follows. List 1 (including heading) is in
> range C4:C13 and List 2 (including heading) is in range D49
>
> List 1 List 2
> A A
> S T
> D Y
> f H
> g G
> H
> J
> L
> M
>
> 2. Type condition in C16;
> 3. Type =COUNTIF($C$5:$C$13,D5)=1 in C17
> 4. Type List 2 in cell C21
> 5. Now go to Data > Advanced Filter and click "Copy to another location"
> 6. In the list range, select C413;
> 7. In the criteria range, select C16:C17
> 8. In the Copy to box, please refer cell C21
> 9. Now click on OK
>
> Please not that this in not a dynamic solution. If any entry in List 1 or
> List 2 changes, you will have to rerun the advanced filter.
>
> --
> Regards,
>
> Ashish Mathur
> Microsoft Excel MVP
> www.ashishmathur.com
>
> "Cheimbig" <(E-Mail Removed)> wrote in message
> news:6F9F7BB5-FB9A-4F4D-9C5F-(E-Mail Removed)...
> > I have 2 columns with names
> > I want to have a 3rd column with only the names that match
> > Is there a function for doing this
> > --
> > Carla Heimbigner
>

Send it to me. I'm at:

xl can help at comcast period net

Remove "can" and change the obvious. If the file is big, zip it.

--
Biff
Microsoft Excel MVP


"Cheimbig" <(E-Mail Removed)> wrote in message
news:94E0F310-3FCB-41F8-8B2A-(E-Mail Removed)...
> Can I just send one of you the file --- neither of these worked for me -
> so I
> am clearly doing something wrong.
> --
> Carla Heimbigner
>
>
> "Ashish Mathur" wrote:
>
>> Hi,
>>
>> You can use advanced filters as well.
>>
>> 1. Assume you have 2 lists as follows. List 1 (including heading) is in
>> range C4:C13 and List 2 (including heading) is in range D49
>>
>> List 1 List 2
>> A A
>> S T
>> D Y
>> f H
>> g G
>> H
>> J
>> L
>> M
>>
>> 2. Type condition in C16;
>> 3. Type =COUNTIF($C$5:$C$13,D5)=1 in C17
>> 4. Type List 2 in cell C21
>> 5. Now go to Data > Advanced Filter and click "Copy to another location"
>> 6. In the list range, select C413;
>> 7. In the criteria range, select C16:C17
>> 8. In the Copy to box, please refer cell C21
>> 9. Now click on OK
>>
>> Please not that this in not a dynamic solution. If any entry in List 1
>> or
>> List 2 changes, you will have to rerun the advanced filter.
>>
>> --
>> Regards,
>>
>> Ashish Mathur
>> Microsoft Excel MVP
>> www.ashishmathur.com
>>
>> "Cheimbig" <(E-Mail Removed)> wrote in message
>> news:6F9F7BB5-FB9A-4F4D-9C5F-(E-Mail Removed)...
>> > I have 2 columns with names
>> > I want to have a 3rd column with only the names that match
>> > Is there a function for doing this
>> > --
>> > Carla Heimbigner
>>

Hi,

You can send me the file at (E-Mail Removed).

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Cheimbig" <(E-Mail Removed)> wrote in message
news:94E0F310-3FCB-41F8-8B2A-(E-Mail Removed)...
> Can I just send one of you the file --- neither of these worked for me -
> so I
> am clearly doing something wrong.
> --
> Carla Heimbigner
>
>
> "Ashish Mathur" wrote:
>
>> Hi,
>>
>> You can use advanced filters as well.
>>
>> 1. Assume you have 2 lists as follows. List 1 (including heading) is in
>> range C4:C13 and List 2 (including heading) is in range D49
>>
>> List 1 List 2
>> A A
>> S T
>> D Y
>> f H
>> g G
>> H
>> J
>> L
>> M
>>
>> 2. Type condition in C16;
>> 3. Type =COUNTIF($C$5:$C$13,D5)=1 in C17
>> 4. Type List 2 in cell C21
>> 5. Now go to Data > Advanced Filter and click "Copy to another location"
>> 6. In the list range, select C413;
>> 7. In the criteria range, select C16:C17
>> 8. In the Copy to box, please refer cell C21
>> 9. Now click on OK
>>
>> Please not that this in not a dynamic solution. If any entry in List 1
>> or
>> List 2 changes, you will have to rerun the advanced filter.
>>
>> --
>> Regards,
>>
>> Ashish Mathur
>> Microsoft Excel MVP
>> www.ashishmathur.com
>>
>> "Cheimbig" <(E-Mail Removed)> wrote in message
>> news:6F9F7BB5-FB9A-4F4D-9C5F-(E-Mail Removed)...
>> > I have 2 columns with names
>> > I want to have a 3rd column with only the names that match
>> > Is there a function for doing this
>> > --
>> > Carla Heimbigner
>>