Last Name- First Name problem

dickpierce

Board Regular
Joined
Nov 26, 2004
Messages
148
Office Version
  1. 2007
Platform
  1. Windows
I have four columns of data. Each column has about 660 names in them.

a: Last Name
b: First Name
c: Last Name
d: First Name

I'm using the following formula in column e:=IF(COUNTIF(c:c,A2)>0,"",A2)

It works great unless there are matching first names in b and d. What is the formula I could use that would work that would weed out same first names?
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
What are you actually trying to do?
 

dickpierce

Board Regular
Joined
Nov 26, 2004
Messages
148
Office Version
  1. 2007
Platform
  1. Windows
Here's what I'm trying to do: I want to match the last names of people in column a with the last names in column c. If there is no match, the last name of a person from column a should appear in column e. Using the formula I quoted in my previous post works well, however if a person with a first name that is the same in both column b and d but with a different last name, his last name will not appear in column e. I want the name to appear in e. Does this help?
 

dickpierce

Board Regular
Joined
Nov 26, 2004
Messages
148
Office Version
  1. 2007
Platform
  1. Windows
Here's an addendum to the problem I'm having. These are the results I'm getting using the formula:=IF(COUNTIF(c:c,a44)>0,"",a44)
However, notice the name phil bradley in columns a-b. The result is blank, but should not be.

a b c d e

Bonura Zeke Berger Wally Bonura
Boone Ray Berra Yogi
Boyle Jack Biggio Craig Boyle
Bradley Phil Bishop Max
Brandt Jackie Blair Paul Brandt
Bransfield Kitty BlasingameDon Bransfield
Braun Steve Blauser Jeff Braun
Bresnahan Roger Blue Lu
Bressler Rube Bluege Ossie Bressler
Bressoud Eddie Boggs Wade Bressoud
Bridwell Al Bolling Frank Bridwell
Briggs Johnny Bonds Barry Briggs
Brookens Tom Bonds Bobby Brookens
Brosius Scott Bonilla Bobby Brosius
Brown Ollie Boone Bob Brown
Browne George Boone Bret Browne
Browning Pete Boone Ray Browning
Buechele Steve Bottomley Jim Buechele
Buford Don Boudreau Lou
Buhner Jay Bowa Larry Buhner
Bumbry Al Boyer Clete Bumbry
Burdock Jack Boyer Ken Burdock
Burgess Smoky Bradley Bill
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

dickpierce said:
Here's an addendum to the problem I'm having. These are the results I'm getting using the formula:=IF(COUNTIF(c:c,a44)>0,"",a44)
However, notice the name phil bradley in columns a-b. The result is blank, but should not be.

a b c d e

Bonura Zeke Berger Wally Bonura
Boone Ray Berra Yogi
Boyle Jack Biggio Craig Boyle
Bradley Phil Bishop Max
Brandt Jackie Blair Paul Brandt
Bransfield Kitty BlasingameDon Bransfield
Braun Steve Blauser Jeff Braun
Bresnahan Roger Blue Lu
Bressler Rube Bluege Ossie Bressler
Bressoud Eddie Boggs Wade Bressoud
Bridwell Al Bolling Frank Bridwell
Briggs Johnny Bonds Barry Briggs
Brookens Tom Bonds Bobby Brookens
Brosius Scott Bonilla Bobby Brosius
Brown Ollie Boone Bob Brown
Browne George Boone Bret Browne
Browning Pete Boone Ray Browning
Buechele Steve Bottomley Jim Buechele
Buford Don Boudreau Lou
Buhner Jay Bowa Larry Buhner
Bumbry Al Boyer Clete Bumbry
Burdock Jack Boyer Ken Burdock
Burgess Smoky Bradley Bill

If A:B is sorted on A and Cand D sorted on C, try:

E1:

=IF(LOOKUP(A1,$C$1:$C$23)=A1,IF(INDEX($D$1:$D$23,MATCH(A1,$C$1:$C$23,1))=B1,"",A1),A1)
 

dickpierce

Board Regular
Joined
Nov 26, 2004
Messages
148
Office Version
  1. 2007
Platform
  1. Windows
:pray:

You are a God!

Works like a charm. Thanks! But out of curiosity, is there another formula? Like my original one?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

dickpierce said:
...

[O]ut of curiosity, is there another formula? Like my original one?

I don't think so.
 

dickpierce

Board Regular
Joined
Nov 26, 2004
Messages
148
Office Version
  1. 2007
Platform
  1. Windows
If you say so then, it must not exist. Thanks again. :p
 

dickpierce

Board Regular
Joined
Nov 26, 2004
Messages
148
Office Version
  1. 2007
Platform
  1. Windows
Oh oh! I found a mistake using the given formula.

Hayes Frankie Greenberg Hank Hayes
Hayes Jackie Grich Bobby Hayes
Heath Jeff Griffey Jr. Ken
Heath Mike Griffey Sr. Ken Heath
Heathcote Cliff Grissom Marquis Heathcote
Hegan Jim Groat ****
Held Woodie Groh Heinie
Helms Tommy Grote Jerry Helms
Helton Todd Guerrero Pedro Helton
Hemphill Charlie Guerrero Vladimir Hemphill
Hemsley Rollie Guillen Ozzie
Henderson Dave Gwynn Tony Henderson
Henderson Ken Hack Stan Henderson
Henrich Tommy Hafey Chick
Hernandez Jose Haller Tom Hernandez
Herndon Larry Hamilton Billy Herndon
Hickman Charlie Hamner Granny
Hickman Jim Hansen Ron Hickman
Higginson Bobby Hargrove Mike Higginson
High Andy Harper Tommy High
Hill Glenallen Harrah Toby Hill
Hinton Chuck Harris Bucky Hinton
Hisle Larry Hart Jim Ray
Hoak Don Hartnett Gabby
Hoblitzel **** Hartsel Topsy Hoblitzel
Hofman Solly Hayes Frankie Hofman

Notice the name Frankie Hayes. It should be a blank. If I change your formula to:=IF(LOOKUP(A1,$C$1:$C$23)=A1,IF(INDEX($D$1:$D$23,MATCH(A1,$C$1:$C$23,0))=B1,"",A1),A1). I put a zero at the end of Match, it solves this problem but then other errors occur. See below.

Avila Bobby Bailey Bob
Baerga Carlos Bailey Ed Baerga
Bailey Ed Baines Harold Bailey

Bailey should not appear. Help!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
dickpierce said:
Oh oh! I found a mistake using the given formula.

Hayes Frankie Greenberg Hank Hayes
Hayes Jackie Grich Bobby Hayes
Heath Jeff Griffey Jr. Ken
Heath Mike Griffey Sr. Ken Heath
Heathcote Cliff Grissom Marquis Heathcote
Hegan Jim Groat ****
Held Woodie Groh Heinie
Helms Tommy Grote Jerry Helms
Helton Todd Guerrero Pedro Helton
Hemphill Charlie Guerrero Vladimir Hemphill
Hemsley Rollie Guillen Ozzie
Henderson Dave Gwynn Tony Henderson
Henderson Ken Hack Stan Henderson
Henrich Tommy Hafey Chick
Hernandez Jose Haller Tom Hernandez
Herndon Larry Hamilton Billy Herndon
Hickman Charlie Hamner Granny
Hickman Jim Hansen Ron Hickman
Higginson Bobby Hargrove Mike Higginson
High Andy Harper Tommy High
Hill Glenallen Harrah Toby Hill
Hinton Chuck Harris Bucky Hinton
Hisle Larry Hart Jim Ray
Hoak Don Hartnett Gabby
Hoblitzel **** Hartsel Topsy Hoblitzel
Hofman Solly Hayes Frankie Hofman

Notice the name Frankie Hayes. It should be a blank. If I change your formula to:=IF(LOOKUP(A1,$C$1:$C$23)=A1,IF(INDEX($D$1:$D$23,MATCH(A1,$C$1:$C$23,0))=B1,"",A1),A1). I put a zero at the end of Match, it solves this problem but then other errors occur. See below.

Avila Bobby Bailey Bob
Baerga Carlos Bailey Ed Baerga
Bailey Ed Baines Harold Bailey

Bailey should not appear. Help!

You need to adapt/adjust the ranges: $C$1:$C$23 ---> $C$1:$C$26. And you do not need to set the match-type to 0 in MATCH. A:B must be sorted in ascending order, first on A then B. The same sort routine for C:D.
 

Forum statistics

Threads
1,148,216
Messages
5,745,431
Members
423,951
Latest member
peggrif

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